SQL FULL OUTER JOIN: Syntax and Interview Qs
FULL OUTER JOIN is the reconciliation primitive every production data pipeline eventually needs. It lives at the boundary where two sources of truth meet: ledger and warehouse, stream and batch, vendor export and internal catalog. When the diff matters, this is the join that computes it in a single scan.
FULL OUTER JOIN Syntax
FULL JOIN and FULL OUTER JOIN parse identically. Architecturally they compile to the same plan node, usually a hash join with a two-sided spill strategy. Most style guides prefer the full spelling because reviewers on pipeline teams expect explicit intent when the query reconciles sources.
FULL OUTER JOIN Syntax Reference
-- These two are identical
SELECT * FROM a FULL JOIN b ON a.id = b.a_id;
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;
-- Full syntax
SELECT
COALESCE(left_table.key, right_table.key) AS unified_key,
left_table.column1,
right_table.column2
FROM left_table
FULL OUTER JOIN right_table
ON left_table.key = right_table.key;Interview note: Unlike LEFT JOIN, table order does not matter for FULL OUTER JOIN. Swapping the tables produces the same rows (columns just appear in different order). This is because FULL OUTER JOIN preserves unmatched rows from both sides.
How It Combines LEFT and RIGHT
Think of FULL OUTER JOIN as running a LEFT JOIN and a RIGHT JOIN at the same time, then merging the results. The result has three categories of rows.
Category 1: Matched rows
Both tables have a row that satisfies the ON condition. All columns from both tables are populated. This is the same as what INNER JOIN returns.
Category 2: Left-only rows
The left table has a row with no matching row in the right table. Left columns are populated. Right columns are NULL. This is the same as the extra rows LEFT JOIN produces beyond INNER JOIN.
Category 3: Right-only rows
The right table has a row with no matching row in the left table. Right columns are populated. Left columns are NULL. This is the part that LEFT JOIN cannot capture. Only FULL OUTER JOIN or RIGHT JOIN preserves these rows.
Full Outer Join Row Categories Example
-- customers: Alice (1), Bob (2), Carol (3)
-- orders: order for Alice (customer_id=1), order for Dave (customer_id=99)
SELECT c.name, o.order_id, o.customer_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
-- Result:
-- Alice | 201 | 1 (matched)
-- Bob | NULL | NULL (left-only: no order)
-- Carol | NULL | NULL (left-only: no order)
-- NULL | 202 | 99 (right-only: orphan order)FULL OUTER JOIN vs LEFT JOIN vs INNER JOIN
This comparison shows how each join type handles unmatched rows. FULL OUTER JOIN is the most permissive: it never drops a row from either table.
| Behavior | FULL OUTER | LEFT | INNER |
|---|---|---|---|
| Unmatched left rows | Kept, right columns filled with NULL | Kept, right columns filled with NULL | Dropped |
| Unmatched right rows | Kept, left columns filled with NULL | Dropped | Dropped |
| Result row count | Sum of matched + unmatched left + unmatched right | At least as many rows as the left table | Only matching rows |
| NULL generation | Both sides can produce NULLs | Only right-side columns produce NULLs | No NULLs generated by the join |
| Typical use case | Reconciliation, comparing two data sources | Preserve all entities from one table | Only entities with matching data on both sides |
5 FULL OUTER JOIN Patterns
From basic syntax to data reconciliation, difference detection, and the LEFT + RIGHT UNION workaround for databases that lack native support.
Basic FULL OUTER JOIN
FULL OUTER JOIN returns every row from both tables. Where the ON condition matches, columns from both tables are populated. Where a left-table row has no match, right columns are NULL. Where a right-table row has no match, left columns are NULL.
Data Reconciliation Between Two Sources
FULL OUTER JOIN is the standard pattern for comparing two datasets. When migrating data, auditing pipelines, or validating ETL output, you join the source and target on a shared key. Rows that appear in both are matches. Rows with NULLs on one side are missing from that source.
Finding Differences Between Two Tables
By filtering the FULL OUTER JOIN result to only rows where one side is NULL, you find records that exist in one table but not the other. FULL OUTER JOIN with IS NULL filters on both sides finds rows missing from either side in a single query.
Combining LEFT and RIGHT Results
A FULL OUTER JOIN is logically equivalent to a LEFT JOIN unioned with the unmatched right-side rows. Some databases that do not support FULL OUTER JOIN (like older MySQL versions before 8.0) require this workaround.
FULL OUTER JOIN with COALESCE for Merged Keys
When performing a FULL OUTER JOIN, the join key can be NULL on either side. COALESCE picks the non-NULL value, giving you a single unified key column. This is critical for downstream processing: without COALESCE, you would have two key columns, each NULL for different rows, making grouping and sorting unreliable.
Data Reconciliation Pattern
-- Compare source system vs data warehouse
SELECT
COALESCE(src.account_id, dwh.account_id) AS account_id,
src.balance AS source_balance,
dwh.balance AS warehouse_balance,
CASE
WHEN src.account_id IS NULL THEN 'Missing from source'
WHEN dwh.account_id IS NULL THEN 'Missing from warehouse'
WHEN src.balance != dwh.balance THEN 'Balance mismatch'
ELSE 'Match'
END AS reconciliation_status
FROM source_accounts src
FULL OUTER JOIN warehouse_accounts dwh
ON src.account_id = dwh.account_id;MySQL UNION Workaround
-- FULL OUTER JOIN equivalent using UNION ALL
-- (for databases that lack FULL OUTER JOIN support)
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION ALL
SELECT c.customer_id, c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;NULL Handling in FULL OUTER JOIN
FULL OUTER JOIN produces NULLs on both sides of the result. This is different from LEFT JOIN, which only produces NULLs on the right side.
COALESCE for unified keys
The join key is NULL on the unmatched side. Use COALESCE(left.key, right.key) to produce a single non-NULL key column. Without this, GROUP BY and ORDER BY on the key column will not work correctly because NULL values are grouped separately.
NULL = NULL is not TRUE
If either table has NULL values in the join key, those rows will never match. NULL = NULL evaluates to UNKNOWN in SQL, not TRUE. This means rows with NULL keys always appear as unmatched, even if both tables have NULL key rows.
Arithmetic with NULLs from both sides
Any arithmetic with NULL produces NULL. When computing differences like source.amount - target.amount, either value could be NULL. Wrap both in COALESCE: COALESCE(src.amount, 0) - COALESCE(tgt.amount, 0). Test your query with unmatched rows from both directions.
4 FULL OUTER JOIN Interview Questions
These questions test join type selection, reconciliation skills, bidirectional anti-join patterns, and data quality debugging.
What is the difference between FULL OUTER JOIN, LEFT JOIN, and INNER JOIN?
INNER JOIN keeps only matches. LEFT JOIN keeps all left rows plus matches. FULL OUTER JOIN keeps all rows from both tables. Example: 100 customers, 80 have orders, 5 orders reference deleted customers. INNER: 80 rows. LEFT: 100 rows. FULL OUTER: 105 rows.
How would you use SQL to reconcile data between a source system and a data warehouse?
FULL OUTER JOIN source and target on the business key. Use CASE WHEN to classify each row: source IS NULL means missing from source, target IS NULL means missing from target, values differ means mismatch, everything else is a match. Mention COALESCE for the unified key.
Write a query to find all customers who have no orders AND all orders that have no valid customer.
SELECT COALESCE(c.id, o.customer_id) AS id, c.name, o.order_id FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id WHERE c.id IS NULL OR o.customer_id IS NULL. c.id IS NULL catches orphan orders; o.customer_id IS NULL catches customers without orders.
A FULL OUTER JOIN returns unexpected NULLs on both sides for what should be matching rows. What went wrong?
Check for data type mismatches: one side stores the key as VARCHAR, the other as INTEGER. Check for trailing whitespace: TRIM both sides. Check for case differences: LOWER both sides. Check for NULL keys: NULL = NULL is not TRUE in SQL. Run a diagnostic: SELECT src.key, dwh.key, LENGTH(src.key), LENGTH(dwh.key) to spot invisible differences.
FULL OUTER JOIN FAQ
What is a FULL OUTER JOIN in SQL?+
When should I use FULL OUTER JOIN instead of LEFT JOIN?+
Does MySQL support FULL OUTER JOIN?+
How do NULLs work in a FULL OUTER JOIN?+
The Reconciliation Query Your Pipeline Already Needs
- 01
Active recall beats re-reading by 50%
Cognitive-science meta-reviews (Dunlosky et al., 2013) rank practice testing as a top-tier study technique, while re-reading and highlighting rank near the bottom
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
Five problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition