SQL - Cross Join

In SQL Server, a CROSS JOIN (also known as a Cartesian join) is a type of join that combines each row from the first table with every row from the second table, resulting in a Cartesian product. It does not require a specific join condition and returns all possible combinations of rows.

The basic syntax for performing a CROSS JOIN in SQL Server is as follows:

SELECT column_list
FROM table1
CROSS JOIN table2;

In this syntax, column_list represents the columns you want to select from the cross-joined tables. table1 and table2 are the two tables you want to combine using a cross join.

Here's an example to illustrate the usage of a CROSS JOIN:

Consider two tables, "Colors" and "Sizes", with the following structures:

To retrieve all possible combinations of colors and sizes, you can use a CROSS JOIN as follows:

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

In the above query, we select the color from the "Colors" table and the size from the "Sizes" table. We perform a cross join between the two tables, combining each row from the "Colors" table with every row from the "Sizes" table.

The output of the query would be:

As you can see, the result set includes all possible combinations of colors and sizes, as there is no specific join condition. It returns a row for each combination of a color and a size, resulting in a Cartesian product.

It's important to note that a cross join can result in a large number of rows if the tables involved have many records. Therefore, it should be used with caution and only when it's intended to generate all possible combinations.

In summary, a CROSS JOIN in SQL Server combines each row from the first table with every row from the second table, resulting in a Cartesian product. It does not require a join condition and returns all possible combinations of rows.