SQL / JOINS
FULL OUTER JOIN and the gaps it reveals
Reconcile two tables with FULL OUTER JOIN, tell which side each unmatched row came from, and list only the gaps without accidentally shrinking the result.
What you will learn
- Keep unmatched rows from both sides in one result with FULL OUTER JOIN
- Tell which table a row is missing from by testing each side's key with IS NULL
- Report a single key with COALESCE(a.key, b.key), or let USING (key) merge it for you
- Emulate a full join in MySQL: LEFT JOIN, UNION ALL, then a RIGHT JOIN anti-join
Understanding FULL OUTER JOIN and the gaps it reveals
FULL OUTER JOIN evaluates the ON condition the same way an inner join does, then patches the result twice: each left row that matched nothing is emitted with NULLs in every right-hand column, and each right row that matched nothing is emitted with NULLs in every left-hand column. Every input row therefore appears at least once, which is what makes this join the tool for finding what one table has and the other does not. The row count is not left plus right, though, because matched rows are shared between the two sides, and repeated keys still multiply inside a key group exactly as an inner join would.
Because NULL padding can now appear on either side, a NULL no longer means "missing on the right". The dependable signal is each side's own join key: assuming the key is never NULL in the source data, s.order_id IS NULL can only mean the row was manufactured for an invoiced row with no shipment, and i.order_id IS NULL means the opposite. Those two tests cut the result into three groups, matched, left-only and right-only, and each group answers a different question, which is why reconciliation queries select both keys rather than just one.
Selecting the key from a single side quietly breaks everything built on top of the join, since half the rows hold NULL there: ORDER BY and GROUP BY collapse all the gaps into one NULL bucket, and a further join on that column matches nothing. COALESCE(s.order_id, i.order_id) gives back a usable key, and USING (order_id) performs that same coalesce as part of the join. Filters need the mirror-image care: a predicate such as i.amount > 100 in WHERE is unknown for NULL-padded rows, so it deletes precisely the gaps the full join was there to expose.
placeholder
WITH shipped(order_id, warehouse) AS (
VALUES (1001, 'east'),
(1002, 'east'),
(1004, 'west')
),
invoiced(order_id, amount) AS (
VALUES (1002, 89.50),
(1003, 42.00),
(1004, 15.75)
)
SELECT s.order_id AS shipped_id,
i.order_id AS invoiced_id,
s.warehouse,
i.amount
FROM shipped s
FULL OUTER JOIN invoiced i ON i.order_id = s.order_id
ORDER BY COALESCE(s.order_id, i.order_id);A FULL OUTER JOIN is an inner join plus the unmatched rows of both tables padded with NULLs, which turns each side's key column into the test for which table a row is missing from.
Worked examples
Only the gaps
Keeps just the rows that failed to pair up and labels each one with the table it is missing from.
WITH shipped(order_id) AS (
VALUES (1001), (1002), (1004)
),
invoiced(order_id) AS (
VALUES (1002), (1003), (1004)
)
SELECT COALESCE(s.order_id, i.order_id) AS order_id,
CASE WHEN i.order_id IS NULL
THEN 'shipped, never invoiced'
ELSE 'invoiced, never shipped'
END AS problem
FROM shipped s
FULL OUTER JOIN invoiced i ON i.order_id = s.order_id
WHERE s.order_id IS NULL OR i.order_id IS NULL
ORDER BY COALESCE(s.order_id, i.order_id);Example explained
Line 1The ON clause pairs 1002 and 1004, leaving 1001 padded on the invoiced side and 1003 padded on the shipped side.
Line 2WHERE s.order_id IS NULL OR i.order_id IS NULL keeps only those padded rows; OR is required because no row ever has both keys NULL at once.
Line 3COALESCE reads whichever key survived, so the report has one id column instead of two half-empty ones.
Line 4The CASE tests one side only: after that WHERE, i.order_id IS NULL can mean nothing except "shipped but never billed".
USING merges the key column
Shows that USING replaces the two key columns with a single coalesced one, which also hides which side matched.
WITH shipped(order_id, warehouse) AS (
VALUES (1001, 'east'), (1002, 'east')
),
invoiced(order_id, amount) AS (
VALUES (1002, 89.50), (1003, 42.00)
)
SELECT order_id, warehouse, amount
FROM shipped
FULL OUTER JOIN invoiced USING (order_id)
ORDER BY order_id;Example explained
Line 1USING (order_id) exposes one merged column defined as COALESCE(shipped.order_id, invoiced.order_id), which is why 1003 shows an id although shipped has no such row.
Line 2That merged column is unqualified, so ORDER BY order_id needs neither a table prefix nor a COALESCE call.
Line 3The merge costs you provenance: 1001 and 1003 both carry an id, and only the empty warehouse or empty amount reveals which table lacked the row.
Emulating it where FULL JOIN is missing
Reproduces the same four rows using LEFT JOIN plus a right anti-join, for engines such as MySQL that reject FULL OUTER JOIN.
WITH shipped(order_id) AS (
SELECT 1001 UNION ALL SELECT 1002 UNION ALL SELECT 1004
),
invoiced(order_id) AS (
SELECT 1002 UNION ALL SELECT 1003 UNION ALL SELECT 1004
)
SELECT *
FROM (
SELECT s.order_id AS shipped_id, i.order_id AS invoiced_id
FROM shipped s
LEFT JOIN invoiced i ON i.order_id = s.order_id
UNION ALL
SELECT s.order_id, i.order_id
FROM shipped s
RIGHT JOIN invoiced i ON i.order_id = s.order_id
WHERE s.order_id IS NULL
) recon
ORDER BY COALESCE(shipped_id, invoiced_id);Example explained
Line 1The LEFT JOIN half already yields every shipped row, matched or unmatched.
Line 2The second half is a right anti-join: RIGHT JOIN plus WHERE s.order_id IS NULL emits only invoiced rows with no shipment, so matched pairs are never counted twice.
Line 3UNION ALL rather than UNION, because UNION would also fold together genuine duplicate rows produced by repeated keys.
Line 4The derived table exists so the final ORDER BY can use an expression over the combined columns, which PostgreSQL forbids directly on a UNION.
Important notes
MySQL through 8.x has no FULL OUTER JOIN and rejects the keyword outright; SQLite supports it only from 3.39. The word OUTER is optional everywhere, so FULL JOIN and FULL OUTER JOIN are the same thing.
A source row whose join key is NULL matches nothing, because NULL = NULL is unknown, so it always surfaces as a gap on its own side. That is the join behaving correctly, not a lost partner.
Common mistakes
Writing WHERE s.order_id IS NULL AND i.order_id IS NULL to hunt for gaps: the join never emits a row with both keys NULL, so the query returns zero rows and the reconciliation looks clean when it is not.
Selecting one side's key only, then grouping or ordering by it: every invoiced-only row falls into a single NULL bucket and its real id never reaches the report.
Adding an ordinary filter such as WHERE i.amount > 100 after the join: NULL > 100 is unknown, so all shipped-only rows are discarded and the full join silently degrades into a right join.
Try it yourself
Change, predict, then run
In a browser editor, define departments(dept_id, dept_name) holding ids 1 and 2, and employees(emp_id, dept_id) holding dept_ids 1, 1 and 4. Write one FULL OUTER JOIN that returns exactly the two orphans, department 2 with no employees and the employee pointing at nonexistent department 4, each labelled with the table it is missing from.
Open the SQL workspaceCheck your understanding
shipped holds 6 rows with distinct order_id values, invoiced holds 5 rows with distinct order_id values, and a FULL OUTER JOIN of the two on order_id returns 8 rows. How many order_ids exist in shipped but not in invoiced?
- 1 order_id
- 2 order_ids
- 3 order_ids
- 6 order_ids
Show answer
With distinct keys on both sides the result holds exactly one row per key in the union of the two key sets, so 6 + 5 - matched = 8, meaning 3 keys matched and 6 - 3 = 3 order_ids are shipped-only. Answering 2 counts the invoiced-only keys (5 - 3), which is also what you get from the wrong assumption that the join merely appended 8 - 6 rows to the shipped side.