Introduction
A FULL OUTER JOIN (or simply FULL JOIN) returns every row from both tables. Where rows match on the join condition, columns from both sides are populated normally; where a row from either table has no counterpart, the missing side's columns are NULL-padded. It is effectively the union of a LEFT JOIN and a RIGHT JOIN result, useful for reconciliation tasks like finding all mismatches between two related tables in either direction.
Cricket analogy: Combining the full ICC squad list with the full injury report shows every player from both sources: matched names get status filled in, while players on only one list appear with the other column blank.
Syntax
-- PostgreSQL, SQL Server, Oracle (native support)
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount
FROM customers AS c
FULL OUTER JOIN orders AS o
ON c.customer_id = o.customer_id;
-- MySQL has no native FULL OUTER JOIN; emulate with UNION
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;Explanation
Conceptually, FULL OUTER JOIN first performs the INNER JOIN, then appends the unmatched left-table rows (NULL-padded on the right, as a LEFT JOIN would produce) and the unmatched right-table rows (NULL-padded on the left, as a RIGHT JOIN would produce). PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN natively. MySQL and SQLite (pre-3.39) do not, so it must be emulated by taking a LEFT JOIN UNION a RIGHT JOIN; UNION (not UNION ALL) is used here specifically to deduplicate the rows that matched and would otherwise appear in both the LEFT and RIGHT JOIN halves.
Cricket analogy: Reconciling two scorecards means first matching identical overs from both, then appending Team A's overs missing from Team B's sheet (blank for B) and Team B's overs missing from A's sheet (blank for A); MySQL scorers without a built-in tool combine two separate lists and remove duplicates manually.
Example
-- customers
-- customer_id | customer_name
-- 1 | Alice
-- 2 | Bob
-- 3 | Carla
-- orders
-- order_id | customer_id | order_amount
-- 100 | 1 | 250
-- 102 | 2 | 400
-- 103 | 9 | 999 -- customer_id 9 does not exist in customers
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;Output
Result rows: Alice/100/250, Bob/102/400 (matched pairs), Carla/NULL/NULL (unmatched customer, right side NULL-padded), and NULL/103/999 (unmatched order, left side NULL-padded because no customer has customer_id 9). Every row from both tables appears exactly once, and mismatches are visible as NULLs on whichever side lacks a counterpart.
Cricket analogy: Result rows show Kohli/matched-century, Rohit/matched-fifty as paired batting stats, but 'Gill/NULL/NULL' appears for a net-session-only player never selected, and 'NULL/uncapped-debutant' appears for a walk-in trial player absent from the squad list.
Key Takeaways
- FULL OUTER JOIN returns all rows from both tables, NULL-padding whichever side lacks a match.
- It is logically equivalent to combining a LEFT JOIN result with a RIGHT JOIN result and removing duplicates.
- MySQL and SQLite lack native FULL OUTER JOIN; emulate it via LEFT JOIN UNION RIGHT JOIN.
- Use UNION (not UNION ALL) in the emulation to avoid duplicating the matched rows.
- Common use case: reconciling two tables to find rows present in only one side (add WHERE to isolate mismatches).
Practice what you learned
1. What does FULL OUTER JOIN return?
2. Which major database does NOT support FULL OUTER JOIN natively?
3. When emulating FULL OUTER JOIN in MySQL, why use UNION instead of UNION ALL?
4. In a FULL OUTER JOIN, what happens to an order row whose customer_id has no matching customer?
Was this page helpful?
You May Also Like
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.
LEFT and RIGHT JOIN
Preserve all rows from one table, NULL-padding columns from the other table when there is no match.
CROSS JOIN and Set Operations
Produce a Cartesian product with CROSS JOIN, and combine query results with UNION, UNION ALL, INTERSECT, and EXCEPT.