SQL CROSS JOIN
The SQL CROSS JOIN clause is used to retrieve the Cartesian product of two tables. This means that it returns all possible combinations of rows from the two tables. Each row from the first table is paired with every row from the second table.
CROSS JOIN does not require a ON condition and simply returns all combinations of the rows from both tables. It can generate a large result set, especially when working with tables that have many rows.
Tables Used
Products Table:| ProductID | ProductName |
|---|---|
| 1 | Laptop |
| 2 | Phone |
| ColorID | ColorName |
|---|---|
| 1 | Red |
| 2 | Blue |
Example
We’ll perform a CROSS JOIN to combine all products with all available colors.
Code Example
-- Perform a CROSS JOIN between Products and Colors
SELECT Products.ProductName, Colors.ColorName
FROM Products
CROSS JOIN Colors;
-- View the resulting table
Output Table
| ProductName | ColorName |
|---|---|
| Laptop | Red |
| Laptop | Blue |
| Phone | Red |
| Phone | Blue |
Explanation
- The CROSS JOIN returns a Cartesian product of the two tables.
- It combines each row from the Products table with every row from the Colors table, resulting in all possible combinations.
- In this case, each product (Laptop, Phone) is paired with both colors (Red, Blue).