CROSS JOIN

A CROSS JOIN in SQL is a type of join that performs a cartesian product of rows from the tables in the join. In other words, it returns every combination of rows from the two tables. If the first table has ‘n’ rows and the second table has ‘m’ rows, the result is a table with n*m rows.

The query syntax for a CROSS JOIN is as follows:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Here is a diagram representation of CROSS JOIN:

Let’s consider two small tables for simplicity, Colors and Sizes:

Colors

ColorID  | ColorName
-------- | ---------
1        | Red
2        | Blue
3        | Green

Sizes

SizeID | SizeName
------ | --------
1      | Small
2      | Medium
3      | Large

Example Test Question

Prepare a list of all possible combinations of colors and sizes.

Answer

To solve this, we need to do a CROSS JOIN on the Colors and Sizes tables.

Here is the SQL query to achieve this:

SELECT Colors.ColorName, Sizes.SizeName
FROM Colors
CROSS JOIN Sizes;

This would return the following result:

ColorName  | SizeName
---------- | --------
Red        | Small
Red        | Medium
Red        | Large
Blue       | Small
Blue       | Medium
Blue       | Large
Green      | Small
Green      | Medium
Green      | Large

In the result, you can see that each color is combined with each size, producing a total of 9 combinations (3 colors * 3 sizes). This is exactly what a CROSS JOIN does – it produces a cartesian product of the two tables, generating all possible combinations.

This is how a CROSS JOIN works in SQL, providing a result set that includes all possible combinations of records from the two tables.