FULL OUTER and CROSS join are the two outer shapes that rarely get used and occasionally become essential. FULL OUTER keeps every row from both sides, which is how you reconcile two lists that both have gaps. CROSS matches every row with every row, which is how you build a matrix — the skeleton of a report where one dimension has combinations that do not exist in the data.
When are FULL OUTER and CROSS joins right?
Both are unusual on the read path and both are precise tools rather than blunt ones. A full outer join is the right answer when the question is about the difference between two sets. A cross join is the right answer when the question is about every possible combination of two dimensions, including the empty ones.
What does a FULL OUTER JOIN keep?
An inner join keeps what matches, a left join keeps the left side, and a full outer join keeps everything — unmatched rows from either table, with NULLs on the other side. The reconciliation question for the library is "which authors have no books, and which books have no author":
SELECT a.author_id, a.name, b.book_id, b.title
FROM authors a FULL OUTER JOIN books b ON b.author_id = a.author_id
WHERE a.author_id IS NULL OR b.book_id IS NULL
ORDER BY a.author_id, b.book_id;
author_id name book_id title
--------- ----------- ------- -----
7 Selina Voss NULL NULL
Only one row survives the "either side is empty" filter: Selina Voss has no books. Every book has an author, so no book-side orphan appears. The join itself produced fourteen rows — thirteen books plus one unmatched author — which is the arithmetic of a full outer join:
SELECT COUNT(*) AS rows FROM authors a FULL OUTER JOIN books b ON b.author_id = a.author_id;
rows
----
14
Seven authors plus thirteen books minus six authors that have books equals fourteen rows. That formula — left count plus right count minus matches — is the fastest way to sanity-check a full outer join. The dialect picture is uneven: MySQL has no FULL OUTER JOIN at all, SQL Server spells it FULL OUTER JOIN and Oracle supports it, PostgreSQL supports it, and SQLite only gained it in version 3.39 (2022). Where it is missing, the portable equivalent is a UNION ALL of a LEFT JOIN and an anti-join.
What does a CROSS JOIN produce?
A cross join has no ON clause at all — every row of the first table is paired with every row of the second. Three branches times four distinct genres gives twelve pairs:
SELECT COUNT(*) AS branch_genre_pairs
FROM branches br CROSS JOIN (SELECT DISTINCT genre FROM books) g;
branch_genre_pairs
------------------
12
On its own that is a table of 39 combinations (three branches times thirteen books) with no meaning attached. Its real use is as a frame: pair every branch with every genre, then join the real counts on top, so the report shows the zeros as well as the numbers.
SELECT br.city, b.genre, COUNT(l.loan_id) AS loans
FROM branches br
CROSS JOIN books b
LEFT JOIN loans l ON l.book_id = b.book_id AND b.branch_id = br.branch_id
GROUP BY br.city, b.genre
ORDER BY br.city, b.genre;
city genre loans
------- ---------- -----
Bristol fiction 3
Bristol historical 2
Bristol reference 1
Bristol technical 0
Cardiff fiction 0
Cardiff historical 1
Cardiff reference 0
Cardiff technical 4
Glasgow fiction 1
Glasgow historical 1
Glasgow reference 1
Glasgow technical 0
Three zeros appear that no ordinary grouping would have produced: Bristol holds no technical books, Cardiff holds no fiction or reference books, and Glasgow holds no technical books. A report that only lists what exists would leave a reader wondering whether those cells are zero or missing. A cross join plus a left join is how you make the absence explicit — and it is the same technique that produces the month-by-month grid in a reporting tool.
When is a CROSS JOIN the wrong tool?
Whenever you expected a match and got a multiplication instead. The classic accident is writing FROM books b JOIN loans l and forgetting the ON clause entirely — the result is every book paired with every loan, which is a valid query and a meaningless table. The tell is a row count equal to the product of the two table sizes, and the fix is always the same: add the condition. Two habits protect you. Write JOIN rather than a bare comma-separated FROM list, because a comma list is an implicit cross join that is easy to miss. And keep the ON clause on the row you actually intend to match, not on some other plausible pair of columns.
Quick reference
- FULL OUTER JOIN keeps unmatched rows from both sides; its row count is left plus right minus matches.
- MySQL has no FULL OUTER JOIN; SQLite and SQL Server support it only in recent versions.
- CROSS JOIN has no ON clause and multiplies the two tables — useful for building a complete grid.
- Pair a cross join with a LEFT JOIN to show zero cells that an ordinary GROUP BY hides.
- A forgotten ON clause produces a valid query with a multiplied result, never an error.
Reconciliation drill: write one query that lists members who have never borrowed a book and another that lists authors who have never published one — then explain in one sentence why both need an outer join rather than an inner one, and what would change if you wrote them as inner joins with the filter in the WHERE clause.