The CROSS JOIN keyword in SQL is used to combine all records from both tables (table1 and table2) in a Cartesian product. This means it returns every possible combination of rows from the two tables, regardless of whether or not there is a match between them.
CROSS JOIN Syntax
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
- table1 and table2: The two tables you are joining.
- column_name(s): The columns you want to select from the tables.
How CROSS JOIN Works
- CROSS JOIN creates a result set where every row from table1 is combined with every row from table2.
- If table1 has 3 rows and table2 has 4 rows, the result will have 3 x 4 = 12 rows in total.
- The resulting output does not depend on any condition or relationship between the tables.
- This type of join is sometimes referred to as a Cartesian join.
Example:
Consider two tables, Customers and Orders, with the following data:
Customers Table:
| CustomerID | CustomerName |
|---|---|
| 1 | John |
| 2 | Alice |
Orders Table:
| OrderID | Product |
|---|---|
| 101 | Laptop |
| 102 | Tablet |
Using a CROSS JOIN:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
CROSS JOIN Orders;
Result:
| CustomerName | Product |
|---|---|
| John | Laptop |
| John | Tablet |
| Alice | Laptop |
| Alice | Tablet |
Note:
- The CROSS JOIN does not check for matching values between the tables. Every row from table1 is combined with every row from table2.
- CROSS JOIN is useful in situations where you want all combinations, such as when generating test data or creating combinations for things like menus or options.
CROSS JOIN vs INNER JOIN
- If you add a WHERE clause to a CROSS JOIN, it can return the same result as an INNER JOIN, but CROSS JOIN without any condition always returns every combination of rows from both tables.
- INNER JOIN will only return rows where there is a match between the tables based on the specified condition.