What is a cross join?

A SQL CROSS JOIN is a type of join that returns the Cartesian product of the rows from the tables in the join. This means that it combines each row from the first table with every row from the second table, resulting in a dataset where the number of rows is the product of the number of rows in both tables.

What is a CROSS JOIN?

The primary characteristic of a CROSS JOIN is that it does not require a join condition (an ON clause). Instead, it implicitly pairs every row from the first table with every row from the second table. If TableA has 'm' rows and TableB has 'n' rows, a CROSS JOIN will produce m * n rows.

Due to its nature of generating all possible combinations, a CROSS JOIN is less commonly used for typical data retrieval compared to INNER, LEFT, or RIGHT JOINs. Its applications are generally specific, such as generating permutations, creating test data, or in some cases, simulating a Cartesian product needed for advanced statistical or reporting queries.

Syntax

SELECT column_list
FROM TableA
CROSS JOIN TableB;

Alternatively, you can achieve the same result by listing multiple tables in the FROM clause separated by commas, without a WHERE clause.

SELECT column_list
FROM TableA, TableB;

Example

Let's consider two simple tables: Products and Colors.

Table: Products

Table: Colors

CROSS JOIN Query

SELECT
    p.ProductName,
    c.ColorName
FROM
    Products p
CROSS JOIN
    Colors c;

Result of the CROSS JOIN

As you can see, the Products table has 2 rows and the Colors table has 3 rows. The CROSS JOIN produced 2 * 3 = 6 rows, combining each product with every available color.

Key Characteristics

• Cartesian Product: Returns all possible combinations of rows from the joined tables.
• No Join Condition: Explicitly, a CROSS JOIN does not use an ON clause. If you specify an ON clause, it often behaves like an INNER JOIN (depending on the DBMS), but its core definition is without a condition.
• Row Count: The number of rows in the result set is (rows in TableA) * (rows in TableB).
• Use Cases: Primarily for generating combinations, creating test data, or specific statistical analyses rather than standard data merging.
• Implicit CROSS JOIN: Achieved by listing multiple tables in the FROM clause without a WHERE clause (e.g., FROM TableA, TableB).