SQL CROSS Join

In this tutorial, we will help you understand the concept of the SQL CROSS Join clause with the few examples.

The SQL CROSS Join query is used to join one or more than one table. The CROSS Join query performs the Cartesian product in the set of rows. The CROSS Join displays one row from the first table and joins all the rows from the other rows of the other tables. 

After executing the CROSS Join query, the number of rows in the result will equal the product of the number of rows in table one and the number of rows in table two.

For example:

Assume there are two tables, Table 1 and Table 2.

There are five rows in table 1 and six rows in table 2.

After executing the cross join query, the final result will get thirty records.

Cross Join = M * N

M = Number of rows in the first table.

N = Number of rows in the second table. 

The syntax of the SQL CROSS Join is as follows:

SELECT table1.column_name1, table1.columnname2, table1.column_name3, table1.columnname4, table2.column_name1, table2.column_name2, table2.column_name3, table2.column_name4 FROM table1 CROSS JOIN table2;

Let’s understand the SQL CROSS Join with the help of examples.

Assume the following table, which has certain data.

Table Name 1- D_Students

Table Name 2- Department

Example 1: Write a query to perform the cross joins on the D_students and Department table where the fifth_semester percentage is 82.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM D_Students CROSS JOIN Department WHERE Fifth_Semester = 82;  

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the fifth_semester percentage is 82.

The output of the above example is as follows:

Example 2: Write a query to perform the cross joins on the D_students and Department table where the Student_Name. Start with the letter ‘A’.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Student_Name LIKE 'A%';

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the Student_Name is. Start with the letter 'A'.

The output of the above example is as follows:

Example 3: Write a query to perform the cross joins on the D_students and Department table where the second_semester > 85 and Sixth_Semester =90.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Second_Semester > 85 AND Sixth_Semester = 90;

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the second_semester > 85 and Sixth_Semester =90. The AND operator display those records where both the condition satisfy the criteria.

The output of the above example is as follows:

Example 4: Write a query to perform the cross joins on the D_students and Department table where the third_semester includes 71, 72 percentages.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Third_Semester IN (71, 72);

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the third_semester includes 71 and 72 percentages.

The output of the above example is as follows:

Example 4: Write a query to perform the cross joins on the D_students and Department table where the fifth_semester percentages are between 80 and 82.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department CROSS JOIN D_Students WHERE Fifth_Semester BETWEEN 80 AND 82;

The above SQL CROSS Join query joins the D_Students table and department, displaying the records where the fifth_semester percentages are between 80 and 82.

The output of the above example is as follows: