SQL JOIN - CROSS JOIN

Syntax

SELECT column_name(s)
FROM table_name1
CROSS JOIN table_name2;

Example

Let's say we have two tables named employees and departments. We want to get all possible combinations of employees with departments. The following SQL query will return the result:

SELECT *
FROM employees
CROSS JOIN departments;

Output

The output of the above SQL query will be a table containing all possible combinations of employees with departments.

Explanation

CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no additional condition is introduced.

Use

CROSS JOIN is used to combine all possible combinations of rows from two tables. It is different from INNER JOIN in the sense that INNER JOIN only returns the common rows from the tables.

Important Points

• CROSS JOIN produces a Cartesian product of the two tables i.e. every row of the first table is joined with every row of the second table.
• If the tables have n and m rows respectively, then the resulting table will have n * m rows.
• The usage of CROSS JOIN can result in a large number of rows being returned. Therefore, it should be used judiciously.

Summary

CROSS JOIN is used to combine all possible combinations of rows from two tables. It produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table. It is different from INNER JOIN as it does not require any matching condition. It should be used judiciously as it can result in a large number of rows being returned.