SQL - Join - Cross

A cross join, also known as a Cartesian join, is a type of join operation in SQL. It combines every row from the first table with every row from the second table, resulting in a Cartesian product. Unlike other join types, the cross join does not require a matching condition based on a related column between the tables.

A cross join is used when you want to combine all rows from one table with all rows from another table. It is commonly used to generate all possible combinations of records between two tables.

Syntax:

SELECT column_list
FROM table1
CROSS JOIN table2;

In this syntax, table1 and table2 are the tables to be joined. The CROSS JOIN keyword is used to perform the cross join operation.

Example:

Let's consider two tables: Colors and Sizes. The Colors table contains different colors, while the Sizes table contains different sizes.

To generate all possible combinations of colors and sizes, we can use a cross join:

SELECT Colors.Color, Sizes.Size
FROM Colors
CROSS JOIN Sizes;

In this example, the cross join will return all possible combinations of colors and sizes, resulting in the Cartesian product. The result set will contain every color paired with every size:

Note that the number of rows in the result set is the product of the number of rows in each table, as every row from the first table is combined with every row from the second table.

It's important to note that a cross join can result in a large number of rows if the tables involved have a significant number of rows. Therefore, it should be used with caution and only when necessary.