The FULL OUTER and the CROSS join are the two outer shapes that rarely get used and that occasionally become essential. The FULL OUTER keeps every row from both of the sides, which is how you reconcile the two lists that both have the gaps. The CROSS matches every row with every row, which is how you build a matrix, the skeleton of a report where the one dimension has the combinations that do not exist in the data.
The previous lesson in this module covered "What Do LEFT and RIGHT Joins Preserve?", and the next lesson in this module is about "What Is a Self Join and When Do You Need One?".
When are the FULL OUTER and the CROSS joins right?
Both of them are unusual on the read path, and both of them are precise tools rather than blunt ones. A full outer join is the right answer when the question is about the difference between the two sets. A cross join is the right answer when the question is about every possible combination of the 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. Those are the unmatched rows from either table, with the 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 NULLOnly 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 the fourteen rows, which is the thirteen books plus the one unmatched author. That 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
----
14Seven authors plus thirteen books minus the six authors that have books equals the fourteen rows. That formula, which is the left count plus the right count minus the 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. The SQL Server spells it as the FULL OUTER JOIN and Oracle supports it, and PostgreSQL supports it. SQLite only gained it in the version 3.39 of the year 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 the four distinct genres gives the twelve pairs.
SELECT COUNT(*) AS branch_genre_pairs
FROM branches br CROSS JOIN (SELECT DISTINCT genre FROM books) g;branch_genre_pairs
------------------
12On its own that is a table of the 39 combinations, which is the three branches times the thirteen books, with no meaning attached.
Its real use is as a frame. You pair every branch with every genre, and then you join the real counts on top, so that 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 0Three of the 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?
It is the wrong tool whenever you expected a match and you got a multiplication instead. The classic accident is writing the 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 that is equal to the product of the two table sizes, and the fix is always the same. You add the condition.
There are two habits that protect you. You write the JOIN rather than a bare comma-separated FROM list, because a comma list is an implicit cross join that is easy to miss. And you keep the ON clause on the row that you actually intend to match, and not on some other plausible pair of the columns.
Quick reference
- The FULL OUTER JOIN keeps the unmatched rows from both of the sides, and its row count is the left plus the right minus the matches.
- MySQL has no FULL OUTER JOIN, and SQLite and the SQL Server support it only in the recent versions.
- The CROSS JOIN has no ON clause and it multiplies the two tables. It is useful for building a complete grid.
- You pair a cross join with a LEFT JOIN to show the zero cells that an ordinary GROUP BY hides.
- A forgotten ON clause produces a valid query with a multiplied result, and never an error.
Reconciliation drill: you write one query that lists the members who have never borrowed a book, and another that lists the authors who have never published one. Then you explain in one sentence why both of them need an outer join rather than an inner one, and what would change if you wrote them as the inner joins with the filter in the WHERE clause.