What is Cross-Join in DBMS?

In DBMS, we sometimes need to find the 'Cartesian Product' of two relations. In other words, we need to multiply the resultsets of two relations. In all such cases, the 'Cross Join' is used. So in this blog, we will first see what is a cross join, and then we will understand it with some examples.

Note: In this blog, We will take all the examples using the MySQL database only. We'll be using the below-mentioned student and course table for demonstrating the examples. The tables are as follows:

Cross Join

Cross Join is a binary operation that performs the cartesian product of two tables. In cross join, each row of one table is combined with all the rows of the other table in the resultset. If there are 'm' number of rows in one table and 'n' number of rows in the other. Then, the resultset obtained after the cross join operation has 'm*n' rows .

Below is a diagram to demonstrate how each row of one table is multiplied with all the rows of another table.

Also, for cross join there is no need that the two tables should have an equal number of columns, the number of columns can be different for joining the tables.

The syntax for the cross-join is as follows:

SELECT table1.columns, table2.columns FROM table1 CROSS JOIN table2;

The MySQL query for the cross-join can be as follows:

SELECT s.sid, s.student_name, c.course_name FROM student AS s CROSS JOIN course AS c;

The returned values for the above query is as follows:

In MySQL, if we use the 'comma' instead of 'cross join' in the query, it will also produce the same resultset as cross join.

The MySQL query for the cross-join using comma can be as follows:

SELECT s.sid, s.student_name, c.course_name FROM student AS s, course AS c;

The returned values for the above query is as follows:

Note: It is to be noted that if we use the 'WHERE' clause in the 'CROSS JOIN' query, it starts behaving as an 'INNER JOIN' with the 'ON' clause. It will produce the same resultset as an inner join.

The syntax for the cross-join using 'where' condition can be as follows:

SELECT table1.columns, table2.columns FROM table1 CROSS JOIN table2 WHERE table1.id = table2.id; 

The MySQL query for the cross-join using 'where' condition can be as follows:

SELECT s.sid, s.student_name, c.course_name FROM student AS s CROSS JOIN course AS c WHERE s.sid = c.cid;

The returned values for the above query is as follows:

This is all about the Cross Join in DBMS. Hope you learned something new today. That's it for this blog.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!