Practical SQL patterns for inner, outer, and reconciliation-style joins, including full outer join emulation.
Examples that show how output changes by join type.
Only users who have a matching subscription row appear.
SELECT u.user_id, u.email, s.plan_code
FROM users u
JOIN subscriptions s ON s.user_id = u.user_id
WHERE s.status = 'active';`JOIN` without a modifier means `INNER JOIN` in most SQL dialects.
Find unmatched left-side rows by checking for `NULL` on the right.
SELECT u.user_id, u.email
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.user_id
WHERE s.user_id IS NULL;This is a classic anti-join pattern for missing relationships.
See items present in one side, the other side, or both.
SELECT a.sku AS a_sku, b.sku AS b_sku
FROM inventory_snapshot_a a
FULL OUTER JOIN inventory_snapshot_b b
ON b.sku = a.sku;Great for reconciliation and migration verification.
Use a `LEFT JOIN` + unmatched rows from the other side via `UNION ALL`.
SELECT a.id, b.id
FROM a
LEFT JOIN b ON b.id = a.id
UNION ALL
SELECT a.id, b.id
FROM b
LEFT JOIN a ON a.id = b.id
WHERE a.id IS NULL;Useful in engines without native `FULL OUTER JOIN`, such as some MySQL versions and SQLite.