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

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Amit Sonar	85	83	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	5
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202227	Priya Wagh	80	83	85	80	82	85	87	4
202228	Saurabh Sangale	85	83	80	90	84	84	89	5
202229	Manthan Koli	85	75	84	78	82	80	86	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Table Name 2- Department

Department_Id	Department_Name
1	Computer Engineering
2	Information Technology
3	Mechanical Engineering
4	Automobile Engineering
5	Civil Engineering
6	Electrical Engineering

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:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_Name
202227	Priya Wagh	80	83	85	80	82	85	87	Computer Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Information Technology
202227	Priya Wagh	80	83	85	80	82	85	87	Mechanical Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Automobile Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Civil Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Electrical Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Computer Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Information Technology
202229	Manthan Koli	85	75	84	78	82	80	86	Mechanical Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Automobile Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Civil Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Electrical Engineering

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:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_Name
202115	Amit Sonar	85	83	82	86	92	84	85	Computer Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Information Technology
202115	Amit Sonar	85	83	82	86	92	84	85	Mechanical Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Automobile Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Civil Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Electrical Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Computer Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Information Technology
202221	Anuja Wanare	85	88	86	82	84	85	85	Mechanical Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Automobile Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Civil Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Electrical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Computer Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Information Technology
202223	Anushka Sen	70	75	71	74	80	78	80	Mechanical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Automobile Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Civil Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Electrical Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Computer Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Information Technology
202224	Aakash Jain	80	75	72	74	85	80	83	Mechanical Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Automobile Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Civil Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Electrical Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Computer Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Information Technology
202225	Akshay Agarwal	85	83	78	88	90	82	89	Mechanical Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Automobile Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Civil Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Electrical Engineering

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:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_Name
202113	Yash Dhull	90	88	94	87	85	90	89	Computer Engineering
202113	Yash Dhull	90	88	94	87	85	90	89	Information Technology
202113	Yash Dhull	90	88	94	87	85	90	89	Mechanical Engineering
202113	Yash Dhull	90	88	94	87	85	90	89	Automobile Engineering
202113	Yash Dhull	90	88	94	87	85	90	89	Civil Engineering
202113	Yash Dhull	90	88	94	87	85	90	89	Electrical Engineering
202114	Sonali Patole	95	90	92	88	92	90	91	Computer Engineering
202114	Sonali Patole	95	90	92	88	92	90	91	Information Technology
202114	Sonali Patole	95	90	92	88	92	90	91	Mechanical Engineering
202114	Sonali Patole	95	90	92	88	92	90	91	Automobile Engineering
202114	Sonali Patole	95	90	92	88	92	90	91	Civil Engineering
202114	Sonali Patole	95	90	92	88	92	90	91	Electrical Engineering
202230	Mayur Jain	80	88	87	90	92	90	88	Computer Engineering
202230	Mayur Jain	80	88	87	90	92	90	88	Information Technology
202230	Mayur Jain	80	88	87	90	92	90	88	Mechanical Engineering
202230	Mayur Jain	80	88	87	90	92	90	88	Automobile Engineering
202230	Mayur Jain	80	88	87	90	92	90	88	Civil Engineering
202230	Mayur Jain	80	88	87	90	92	90	88	Electrical Engineering

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:

Student_Id	Student_Name	First_Semester	Second_Semester	Third_Semester	Fourth_Semester	Fifth_Semester	Sixth_Semester	Total	Department_Name
202223	Anushka Sen	70	75	71	74	80	78	80	Computer Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Information Technology
202223	Anushka Sen	70	75	71	74	80	78	80	Mechanical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Automobile Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Civil Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Electrical Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Computer Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Information Technology
202224	Aakash Jain	80	75	72	74	85	80	83	Mechanical Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Automobile Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Civil Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Electrical Engineering

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:

Student_Id	Student_Name	First_Semester	Second_Semester	Third_Semester	Fourth_Semester	Fifth_Semester	Sixth_Semester	Total	Department_Name
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	Computer Engineering
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	Information Technology
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	Mechanical Engineering
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	Automobile Engineering
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	Civil Engineering
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	Electrical Engineering
202118	Sakshi Patil	80	78	74	78	80	77	83	Computer Engineering
202118	Sakshi Patil	80	78	74	78	80	77	83	Information Technology
202118	Sakshi Patil	80	78	74	78	80	77	83	Mechanical Engineering
202118	Sakshi Patil	80	78	74	78	80	77	83	Automobile Engineering
202118	Sakshi Patil	80	78	74	78	80	77	83	Civil Engineering
202118	Sakshi Patil	80	78	74	78	80	77	83	Electrical Engineering
202119	Sopan Bhore	70	68	75	75	80	80	80	Computer Engineering
202119	Sopan Bhore	70	68	75	75	80	80	80	Information Technology
202119	Sopan Bhore	70	68	75	75	80	80	80	Mechanical Engineering
202119	Sopan Bhore	70	68	75	75	80	80	80	Automobile Engineering
202119	Sopan Bhore	70	68	75	75	80	80	80	Civil Engineering
202119	Sopan Bhore	70	68	75	75	80	80	80	Electrical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Computer Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Information Technology
202223	Anushka Sen	70	75	71	74	80	78	80	Mechanical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Automobile Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Civil Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Electrical Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Computer Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Information Technology
202227	Priya Wagh	80	83	85	80	82	85	87	Mechanical Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Automobile Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Civil Engineering
202227	Priya Wagh	80	83	85	80	82	85	87	Electrical Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Computer Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Information Technology
202229	Manthan Koli	85	75	84	78	82	80	86	Mechanical Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Automobile Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Civil Engineering
202229	Manthan Koli	85	75	84	78	82	80	86	Electrical Engineering