SQL Cross Join

A Cross Join (also called a Cartesian Join) is used to combine each row from one table with every row from another table, resulting in a Cartesian product. This join does not require a condition to match rows between the two tables.

 

Syntax of Cross Join

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

• table1 and table2 are the tables to be combined.
• column1, column2, ... are the columns you want to select from both tables.

 

Example

Consider the following tables:

Products Table:

Colors Table:

 

Cross Join Query

To get all combinations of products and colors, you can use a CROSS JOIN:

SELECT p.product_name, c.color_name
FROM products p
CROSS JOIN colors c;

Output:

 

How Cross Join Works

The Cross Join creates a Cartesian product between the two tables:

• Every row from the Products table is combined with every row from the Colors table.
• In this case, since there are 2 products and 2 colors, the result contains 2×2=42 \times 2 = 4 rows.

 

Key Points

• No Join Condition: Unlike other joins, a Cross Join does not require any matching condition (like in INNER JOIN, LEFT JOIN, etc.).
• Cartesian Product: The result is the Cartesian product of the two tables. If table1 has m rows and table2 has n rows, the result will have m * n rows.
• Rarely Used: Cross Joins are less commonly used, but they can be useful in generating combinations or permutations of data (e.g., testing, sample generation, etc.).

 

Use Case: Generate Combinations

You could use a Cross Join to generate all possible combinations of two sets of data, such as product-color combinations as shown in the example above.

 

Summary

A Cross Join produces the Cartesian product of two tables, resulting in a combination of every row from the first table with every row from the second table. It's often used for generating combinations but should be used with caution since the result can quickly grow large.