Joins Updated November 2026

FULL OUTER JOIN

FULL OUTER JOIN returns every row from both tables: matched pairs where the ON condition holds, plus unmatched rows from either side padded with NULL. It is useful for reconciliations, such as comparing two lists to see what is missing on each side. PostgreSQL supports it; SQLite supports it from version 3.39 (2022).

Syntax

SELECT columns FROM a FULL [OUTER] JOIN b ON a.key = b.key;

Examples

SELECT a.sku, a.qty AS warehouse, b.qty AS shop
FROM warehouse_stock a
FULL JOIN shop_stock b ON b.sku = a.sku;

Result: sku | warehouse | shop X1 | 10 | 4 X2 | 5 | NULL X3 | NULL | 2

X2 exists only in the warehouse, X3 only in the shop.

SELECT COALESCE(a.sku, b.sku) AS sku
FROM warehouse_stock a
FULL JOIN shop_stock b ON b.sku = a.sku
WHERE a.sku IS NULL OR b.sku IS NULL;

Result: sku X2 X3

Rows present on only one side. COALESCE picks whichever key is not NULL.

SELECT a.sku, a.qty, b.qty FROM warehouse_stock a LEFT JOIN shop_stock b ON b.sku = a.sku
UNION ALL
SELECT b.sku, NULL, b.qty FROM shop_stock b LEFT JOIN warehouse_stock a ON a.sku = b.sku WHERE a.sku IS NULL;

Result: Same rows as the FULL JOIN

Workaround for databases without FULL JOIN (MySQL, older SQLite).

Common mistakes

Selecting a.sku alone shows NULL for rows that only exist in b; use COALESCE(a.sku, b.sku) for the key column.
Writing a WHERE condition on one side (WHERE a.qty > 0) removes that side's NULL rows and quietly turns the query into a one-sided join.
Getting a syntax error in SQLite older than 3.39; upgrade or use the LEFT JOIN plus UNION ALL pattern.

Practice it

The drills on Querydrill check your answer on live data, free, no account needed.

Start practicing

Questions

What is the difference between FULL JOIN and UNION?

FULL JOIN matches rows side by side into wider rows; UNION stacks the result rows of two queries on top of each other. They solve different problems.

Does MySQL support FULL OUTER JOIN?

No. Emulate it with a LEFT JOIN, UNION ALL, and a RIGHT JOIN (or LEFT JOIN with the tables swapped) filtered to the unmatched rows.

LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.
INNER JOININNER JOIN returns rows that have a match in both tables. Syntax with ON, joining three tables, aliases, columns from both sides, and the common pitfalls.
UNION and UNION ALLUNION combines the rows of two SELECTs and removes duplicates; UNION ALL keeps them and is faster. Column rules, ordering the result, INTERSECT and EXCEPT.
COALESCECOALESCE returns the first non-NULL argument. Show a default instead of NULL, treat missing numbers as zero in sums, fall back across columns, IFNULL, NULLIF.