SQL CROSS JOIN
A CROSS JOIN pairs every row of one table with every row of another. It builds the full grid of combinations, which a LEFT JOIN can then fill in.
Every join so far matched rows on a condition. A
CROSS JOIN
has no condition: it pairs every row of the first table with every row of
the second. Garden Shop has 6 suppliers and 8 categories, so crossing them
gives 6 × 8 = 48 rows, one for every possible supplier and category:
select s.supplier_name, c.category_name
from suppliers as s
cross join categories as c
order by s.supplier_name, c.category_name
There's no
ON, because there's nothing to match. Most of those pairs never happen: no
seed company sells tools. Listing them anyway is the point, and it solves a
problem a normal join can't.
Zeros don't come from data
How many products does each supplier sell in each category?
select s.supplier_name, c.category_name, count(*) as products
from suppliers as s
join products as p on p.supplier_id = s.supplier_id
join categories as c on c.category_id = p.category_id
group by s.supplier_name, c.category_name
order by s.supplier_name, c.category_nameThat returns 11 rows, and none of them is a 0. Every row of the result comes from a product, so a pair with no products produces no row at all. For a report that says "Ironwood Tools sells nothing in Houseplants", the zeros are the answer, and the data can't supply them.
The fix comes in two steps.
CROSS JOIN
builds every pair the report should show. Then a
LEFT JOIN
to
products
attaches whatever matches each pair and keeps the pairs that match nothing.
The
ON
clause needs both keys,
p.supplier_id = s.supplier_id and p.category_id = c.category_id, because a product belongs to the pair only when it matches both. Count
p.product_id, as in
Lesson 5.3, and an empty pair counts 0. The exercise below does this for two
suppliers.
The same grid-then-fill idea shows up in Lesson 21.2 · Date spines, where one side of the grid is a generated list of days, so a day with no orders still gets a row.
The CROSS JOIN you didn't mean
A cross join's row count is the two table sizes multiplied, and it grows fast. The old comma style of joining makes one easy to write by accident:
select count(*) as row_count
from customers, orders
20 customers times 24 orders is 480 rows, every customer paired with every
order, including orders that belong to someone else. With a
WHERE
condition linking the two, the comma form works like an inner join; without
one, it's a cross join that nobody asked for. DuckDB refuses a plain
JOIN
with no
ON
as a syntax error, which is one reason to write joins with the keyword.
Writing
CROSS JOIN
when you mean it tells the next reader the missing condition is deliberate.
The
missing join predicate
mistake page runs the broken query beside the fixed one.
Schema · Garden Shop Tables · suppliers, categories, products 3 tables
One row per supplier. Some suppliers have no contact email.
Lookup table of product categories.
One row per product, with price, cost, and inventory levels.
For Ironwood Tools and Terra Pots Co, show
how many products each sells in every category, including
the categories where the count is 0. Return supplier_name,
category_name, and products, sorted by supplier,
then category. The starter only finds the pairs that have products.
Run your query to inspect the result.
Expected answer
- Columns: supplier_name, category_name, products.
- 16 rows: all eight categories for each of the two suppliers. Ten of them are 0.
- Ironwood Tools sells Tools, Watering, and Outdoor Decor; Terra Pots Co sells Pots & Planters, Outdoor Decor, and Watering.
That's the last lesson in “Joins.”
The next chapter builds on what this one covered.
Start Chapter 6: CASE Logic and Derived Columns → Begins with 6.1 Calculated columnsRelated
INNER, LEFT, RIGHT, FULL, and CROSS joins.
Join types and common join patterns.
A missing join condition pairs every row with every row.
Keep unmatched rows from both tables, and when RIGHT JOIN fits.
Choose whether unmatched rows stay or drop.
Generate every day, left join the facts, report 0.