CROSS JOIN
A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second.
Overview
CROSS JOIN combines every row from one table with every row from another, with no join condition -- the result has N x M rows, where N and M are the row counts of the two inputs. It's the mathematical Cartesian product, and it's the "base case" that other joins (INNER, LEFT, etc.) are conceptually built from by adding a filter condition.
Copy code
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;
-- every color paired with every size
An equivalent, older syntax simply lists both tables in the FROM clause separated by a comma:
Copy code
SELECT c.color, s.size FROM colors c, sizes s;
Practical use cases
- Generating combinations: producing every (product, region) or (color, size) pairing for a product catalog.
- Expanding a calendar or date spine: cross-joining a table of dates with a table of entities to ensure every entity has a row for every date, even if no underlying data exists (useful before a
LEFT JOINback to actual event data). - Cross joining with a table function, such as
CROSS JOIN generate_series(...)in DuckDB, to fan a row out into multiple derived rows.
Copy code
SELECT d.day, r.region
FROM generate_series(DATE '2026-01-01', DATE '2026-01-31', INTERVAL 1 DAY) AS d(day)
CROSS JOIN regions r;
Accidental CROSS JOINs
The most common real-world encounter with CROSS JOIN is unintentional: forgetting a join condition (ON clause) on what was meant to be an INNER JOIN, or listing two tables with a comma in the FROM clause and forgetting to add the matching WHERE filter. This silently produces a Cartesian product and can look like a correct-but-massively-inflated result set, since row counts multiply rather than error out.
Performance
Because output size grows multiplicatively, CROSS JOINs on large tables can be extremely expensive; DuckDB (and most engines) will execute them as a nested loop or similar, and there's no join key to build a hash table on, so cost scales with the full product of both input sizes.
Related terms
How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
USING clause →The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.
SELF JOIN →A self join joins a table to itself, typically using table aliases to compare rows within the same table to each other.
ASOF JOIN →An ASOF JOIN matches each row in one table to the closest preceding (or following) row in another table based on an ordering column, commonly a timestamp -- a core building block for time-series analysis.
Cost-based optimizer →A cost-based optimizer chooses among logically equivalent query plans by estimating the execution cost of each one, using statistics about table sizes and data distributions, and picking the cheapest estimate.
FAQS
CROSS JOIN has no join condition and returns every possible pairing of rows (the full Cartesian product); INNER JOIN filters that product down to only the row pairs matching an ON condition.
Yes -- omitting the ON clause on what was meant to be an INNER JOIN, or listing multiple tables in FROM with commas and forgetting a WHERE filter, both silently produce a Cartesian product.
Generating all combinations of two small dimension tables (like colors and sizes), or building a date spine by cross joining a calendar with a list of entities so every entity has a row for every date.
