Joins: Inner, Outer, Cross, Self, Semi and Anti

In one line

INNER JOIN keeps matching pairs; LEFT/RIGHT/FULL OUTER JOINs keep unmatched rows from one or both sides with NULLs; CROSS JOIN makes every combination; a SELF JOIN relates a table to itself. Semi-joins (EXISTS) and anti-joins (NOT EXISTS) answer "has any" and "has none" without duplicating rows.

0/6 · 0%

Think of it like this

Matching guests to a seating chart. Inner join: only guests with seats. Left join: every guest, with "no seat" where missing. Full join: every guest and every seat, matched where possible. Cross join: every guest in every seat, which is every possible arrangement.

Key ideas

01
A condition on the right table in WHERE turns a LEFT JOIN into an inner join (NULL rows fail the filter). Put it in the ON clause to filter the right side while keeping all left rows.
02
Joins multiply rows: joining orders to order_items then summing orders.total double-counts orders with several items. Aggregate before joining, or sum at the right grain.
03
Semi-join: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) returns each customer once, however many orders they have. Anti-join: NOT EXISTS finds customers with no orders.
04
Self join: compare rows in the same table (employee and manager, consecutive events, pairs of products bought together).
05
The planner chooses the physical algorithm (nested loop, hash, merge; Phase 7.4) independently of the SQL join type.

Code & diagrams

joins.sqlsql

-- every customer with their 2026 orders (keeps customers with none)
SELECT c.name, o.id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at >= '2026-01-01';
-- WRONG: moving the date into WHERE drops customers with no 2026 orders

-- anti-join: customers who never ordered
SELECT c.* FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- fan-out bug and fix
SELECT sum(o.total) FROM orders o JOIN order_item oi ON oi.order_id = o.id;   -- double counts
SELECT sum(o.total) FROM orders o
WHERE EXISTS (SELECT 1 FROM order_item oi WHERE oi.order_id = o.id);          -- correct

-- self join: products bought together
SELECT a.product_id, b.product_id, count(*) AS together
FROM order_item a JOIN order_item b
  ON a.order_id = b.order_id AND a.product_id < b.product_id
GROUP BY 1, 2 ORDER BY together DESC LIMIT 10;

Interview problem

The problem

Monthly report with missing months

Finance wants revenue for every month of 2026, showing 0 for months without orders. A simple GROUP BY skips empty months. Write the query.

When it breaks

Accidental cross join

What you see

A missing join condition on two 100K-row tables produces 10 billion rows; the query runs for hours and fills temp disk.

Fix & prevent

Always use explicit JOIN ... ON; set statement_timeout and temp_file_limit for application and ad-hoc roles.

Explain it without notes

Why does a WHERE filter on the right table turn a LEFT JOIN into an INNER JOIN?

Practice

Find products that were ordered in 2025 but not in 2026.

Trade-offs

↔
EXISTS expresses intent and avoids duplicates; joins are needed when you actually want columns from both sides.

Done when you can

I can choose the right join type and avoid fan-out and outer-join filter bugs.

Command Palette