INNER, LEFT, RIGHT, and FULL OUTER JOIN Patterns

Practical SQL patterns for inner, outer, and reconciliation-style joins, including full outer join emulation.

View

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Common join patterns

Inner join active users with subscriptions

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';

# Only users who have a matching subscription row appear.

Left join to find users without subscriptions

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;

# Find unmatched left-side rows by checking for `NULL` on the right.

Full outer join to compare two datasets

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;

# See items present in one side, the other side, or both.

Emulate FULL OUTER JOIN where unsupported

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;

# Use a `LEFT JOIN` + unmatched rows from the other side via `UNION ALL`.

More in SQL Joins

No recommendations yet.