FULL OUTER JOIN and CROSS JOIN
A FULL OUTER JOIN keeps every row from both tables, with NULLs wherever either side has no match. A CROSS JOIN pairs every row of one table with every row of the other.
Why it matters
Comparing two lists — "which employees have no department, and which departments have no employees?" — needs the unmatched rows from both sides at once. Building a report grid — every customer × every month, zeros included — needs every combination. These two joins do those jobs.
The idea
FULL OUTER JOIN = a LEFT JOIN and a RIGHT JOIN together: matches as usual, plus the unmatched left rows (right columns NULL) and the unmatched right rows (left columns NULL). It is mainly for reconciling two sources.
CROSS JOIN has no ON at all: 6 customers × 3 months = 18 rows. On its own that is rarely what you want. But it is the standard way to build a complete grid. You then LEFT JOIN the real data onto the grid, so gaps show as zero.
FULL OUTER JOIN is comparing two class registers after a merger: you list everyone who is on both, plus the names that are only on one register. CROSS JOIN is a timetable grid: every class against every period, whether or not anything is scheduled there.
A timetable grid is small. A cross join of two big tables is enormous: a million rows × a million rows is a trillion. So it is only safe when at least one side is tiny.
Syntax
SELECT ...
FROM a FULL OUTER JOIN b ON b.key = a.key;
SELECT ...
FROM a CROSS JOIN b;How it works
- FULL OUTER JOIN employees and departments: the 7 matched pairs, plus Gus (no department), plus HR (no employees).
- Gus's row has NULL department columns; HR's row has NULL employee columns.
- CROSS JOIN customers and months: each customer row is paired with each month row, with no condition.
- LEFT JOIN the orders onto that grid on customer and month: cells with no orders get NULL.
COALESCE(SUM(amount), 0)turns those NULLs into zeros, so the report has no gaps.
Key points
- FULL OUTER JOIN = matches + unmatched left rows + unmatched right rows.
- CROSS JOIN has no ON and returns rows(a) × rows(b).
- Grid pattern: CROSS JOIN the dimensions, LEFT JOIN the facts, COALESCE to 0.
- A join with a missing or always-true ON condition is an accidental cross join.
Common mistakes
- Running a CROSS JOIN between two large tables.
- Forgetting an ON condition and getting a cross join by accident — the row count explodes.
- Using WHERE on one side after a FULL OUTER JOIN, which removes the other side's unmatched rows.
Not true: A CROSS JOIN between two tables of 1,000 rows each returns 2,000 rows.
In fact: It pairs every row with every row: 1,000 × 1,000 = 1,000,000 rows. That is why a cross join between large tables is dangerous.
In the interview
A FULL OUTER JOIN returns matched rows plus the unmatched rows from both sides, with NULLs filling the gaps — useful for reconciling two sources. A CROSS JOIN is the Cartesian product: every row paired with every row. I mostly use it to build a complete grid, such as every customer by every month. Then I LEFT JOIN the facts onto the grid, so missing combinations show as zero instead of disappearing.
Part of Unit 3: Joins: combining tables in the free SQL course.