Cheat sheet

SQL joins cheat sheet

Q: What's the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where the join key matches in both tables; unmatched rows on either side are dropped. LEFT JOIN returns every row from the left table; right-side columns become NULL when no match exists. Use INNER when the right side is required, LEFT when it's optional. See INNER vs LEFT JOIN.

Q: When should I use FULL OUTER JOIN?

For reconciliation: comparing two snapshots of the same data and finding rows in either set that don't match the other. SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL surfaces both kinds of mismatch. Rare in everyday analytics; common in data quality and migration work.

Q: Can I join more than two tables in SQL?

Yes. Chain join clauses: FROM a INNER JOIN b ON ... LEFT JOIN c ON .... Each join is binary, but you can chain as many as needed. The order can affect performance. The optimizer usually reorders, but for complex queries you may need to help it via explicit join hints or by structuring CTEs.

Q: Why is my JOIN returning more rows than expected?

Fan-out: the join key is not unique on one or both sides, so each match multiplies rows. Joining users (1 row per user) to orders (many per user) gives one row per order, not one per user. Always sanity-check row counts before and after a join, especially before any SUM / COUNT aggregate. See Join debugging.

Q: Is RIGHT JOIN ever better than LEFT JOIN?

Almost never. A RIGHT JOIN B is mathematically equivalent to B LEFT JOIN A (with sides flipped). Most teams prefer LEFT JOIN everywhere for consistency: the preserved table is always written first, which makes joins easier to read. RIGHT JOIN is correct but rare in production code.

Q: What's the difference between WHERE and ON in a JOIN?

For INNER JOIN, they're equivalent; both filter the result. For LEFT JOIN (and other outer joins), the difference matters: filters in ON are evaluated before the join decides which left rows to preserve, so unmatched left rows survive with NULL. Filters in WHERE run after the join, so they can drop the NULL-padded rows, silently turning a LEFT into an INNER.

Every join type, the syntax, the row-count behavior, and the one trap that turns each one into a bug. Click any join name for the detail page.

At a glance #

Join	Keeps left rows	Keeps right rows	Use case
`INNER`	matched only	matched only	"must have a counterpart"
`LEFT`	all	matched only	"show every left, mark who has data"
`RIGHT`	matched only	all	mirror of `LEFT` (rarely used)
`FULL OUTER`	all	all	"everything from both sides, NULL where missing"
`CROSS`	every	every	cartesian product
Self-join	n/a	n/a	hierarchy / "compare row to row in same table"

INNER JOIN #

sql

SELECT u.id, o.id AS order_id, o.total
FROM   users u
INNER JOIN orders o ON o.user_id = u.id;

-- INNER is the default; the keyword INNER can be omitted.
SELECT * FROM users u JOIN orders o ON o.user_id = u.id;

Trap: silently drops left rows when the right table doesn't have a match. Always check row count before and after.

LEFT JOIN #

sql

-- Every user, with their last order if any.
SELECT u.id, u.email, o.id AS order_id, o.total
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Find users with no orders (the LEFT JOIN ... IS NULL pattern).
SELECT u.*
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE  o.id IS NULL;

Trap: putting a right-side filter in WHERE instead of ON silently demotes LEFT to INNER. NULL = 'paid' is NULL, which fails the filter. Right-side filters belong in ON:

sql

-- Wrong: drops users with no paid orders entirely.
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

-- Right: keeps every user; matches only paid orders.
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

RIGHT JOIN #

sql

SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;
-- Equivalent to:
SELECT * FROM b LEFT  JOIN a ON a.id = b.a_id;

Style note: rewrite as LEFT JOIN with sides flipped, which is easier to read. RIGHT JOIN is correct but rare in production code.

FULL OUTER JOIN #

sql

SELECT *
FROM   a
FULL OUTER JOIN b ON a.id = b.a_id;

-- Reconciliation: rows in either side that don't match the other.
SELECT *
FROM   a
FULL OUTER JOIN b ON a.id = b.a_id
WHERE  a.id IS NULL OR b.a_id IS NULL;

Use for: reconciling two snapshots of the same data ("what's in source but not target?"). Most-used context in real work.

CROSS JOIN #

sql

-- Every product × every region: useful for date-spine / matrix problems.
SELECT p.id, r.code
FROM   products p
CROSS JOIN regions r;

-- Old-style implicit cross join (avoid — easy to forget the WHERE clause).
SELECT * FROM a, b;

Trap: an unintentional cross join produces row count of a × b. On 1M-row tables, that's a trillion rows. Always confirm ON clause exists when joining.

Self-join #

sql

-- Manager hierarchy.
SELECT
  e.name      AS employee,
  m.name      AS manager
FROM   employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- "Find consecutive rows" (compare row to its prior).
SELECT
  o1.id,
  o1.created_at,
  o2.created_at AS next_order
FROM   orders o1
LEFT JOIN orders o2 ON o2.user_id = o1.user_id AND o2.created_at > o1.created_at;
-- (Window functions usually beat self-joins for this — see LAG.)

Multi-condition ON / equality on multiple columns #

sql

-- Compound key.
SELECT *
FROM   order_items oi
JOIN   product_inventory pi
  ON   pi.product_id = oi.product_id
  AND  pi.warehouse_id = oi.warehouse_id;

-- USING shortcut when column names match.
SELECT * FROM orders JOIN order_items USING (order_id);

-- Inequality / range join.
SELECT *
FROM   orders o
JOIN   pricing_tiers pt
  ON   o.total >= pt.min_amount
  AND  o.total <  pt.max_amount;

Anti-join (rows in A NOT in B) #

sql

-- Two equivalent forms; pick by readability.

-- 1. LEFT JOIN ... IS NULL.
SELECT u.*
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE  o.id IS NULL;

-- 2. NOT EXISTS (often the planner's favorite).
SELECT u.*
FROM   users u
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 3. NOT IN (avoid if subquery can produce NULL — the entire predicate becomes NULL).
SELECT u.* FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders);

Common mistakes #

WHERE filter on the right side of a LEFT JOIN: silently turns it into INNER. Put it in ON.
Joining on a column with duplicates on both sides: row count multiplies. a × b → if either side has 5 dupes for the join key, you get 5x rows. Run SELECT COUNT(*) FROM ... before and after to spot it.
Not specifying ON and getting a cross join: SELECT * FROM a, b (no WHERE) = trillions of rows. Always use explicit JOIN ... ON.
NOT IN with a NULLable subquery: returns nothing if any subquery row is NULL. Use NOT EXISTS or LEFT JOIN ... IS NULL instead.

FAQ #

What's the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where the join key matches in both tables; unmatched rows on either side are dropped. LEFT JOIN returns every row from the left table; right-side columns become NULL when no match exists. Use INNER when the right side is required, LEFT when it's optional. See INNER vs LEFT JOIN.

When should I use FULL OUTER JOIN?

For reconciliation: comparing two snapshots of the same data and finding rows in either set that don't match the other. SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id WHERE a.id IS NULL OR b.id IS NULL surfaces both kinds of mismatch. Rare in everyday analytics; common in data quality and migration work.

Can I join more than two tables in SQL?

Yes. Chain join clauses: FROM a INNER JOIN b ON ... LEFT JOIN c ON .... Each join is binary, but you can chain as many as needed. The order can affect performance. The optimizer usually reorders, but for complex queries you may need to help it via explicit join hints or by structuring CTEs.

What's a self-join?

Joining a table to itself, distinguished by aliases. The classic use is hierarchical data (employees with manager_id referencing the same employees table). For "compare row to previous row," window functions like LAG are usually cleaner than self-joins.

Why is my JOIN returning more rows than expected?

Fan-out: the join key is not unique on one or both sides, so each match multiplies rows. Joining users (1 row per user) to orders (many per user) gives one row per order, not one per user. Always sanity-check row counts before and after a join, especially before any SUM / COUNT aggregate. See Join debugging.

Is RIGHT JOIN ever better than LEFT JOIN?

Almost never. A RIGHT JOIN B is mathematically equivalent to B LEFT JOIN A (with sides flipped). Most teams prefer LEFT JOIN everywhere for consistency: the preserved table is always written first, which makes joins easier to read. RIGHT JOIN is correct but rare in production code.

What's the difference between WHERE and ON in a JOIN?

For INNER JOIN, they're equivalent; both filter the result. For LEFT JOIN (and other outer joins), the difference matters: filters in ON are evaluated before the join decides which left rows to preserve, so unmatched left rows survive with NULL. Filters in WHERE run after the join, so they can drop the NULL-padded rows, silently turning a LEFT into an INNER.

Tip

Default to LEFT JOIN when in doubt. INNER is correct when the right side is required; LEFT is correct when it's optional. If you're not sure, LEFT JOIN is the safer default: it preserves rows you might have lost. The exception: when the right side is a primary-key parent table that genuinely must exist (e.g., orders → users), INNER is the right call. Detail: Joins, Join debugging.