Joins: combining tables

The last lecture queried one table at a time. But good relational design deliberately spreads data across tables: customers in one, orders in another, products in a third. To answer real questions — which customers in Pune bought a USB-C hub? — you join them back together.

Anatomy of a join

SELECT c.name, o.id AS order_id, o.qty
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Pune';

• JOIN … ON names the second table and the matching condition — almost always a foreign key equal to a primary key.
• Short aliases (o, c) keep queries readable and make it clear which table each column comes from.
• Conceptually, the database pairs every row of one table with every matching row of the other. Each pair becomes one output row.

Four kinds of join

The difference between join types is what happens to rows without a match. Switch between them and watch which rows survive:

INNER JOIN (or just JOIN)

Only rows with a match on both sides. The most common join.

LEFT JOIN

Every row from the left table, plus matches from the right; unmatched right-side columns are NULL.

RIGHT JOIN

The mirror image — every row from the right table. Usually rewritten as a LEFT JOIN with the tables swapped.

FULL OUTER JOIN

Every row from both tables, matched where possible. Handy for reconciling two data sources.

Finding what’s missing

A LEFT JOIN plus an IS NULL check answers a surprisingly common family of questions — customers who never ordered, products never sold, employees without a manager:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Counting per customer, including zeros, works the same way: COUNT(o.id) counts only non-NULL order IDs, so customers with no orders correctly get 0.

Joins that quietly give wrong numbers

Joins never fail loudly; they just return rows. Two classic traps:

• Fan-out. Joining orders to order items produces one row per item. Summing an order-level column like orders.total then counts each order several times. Aggregate the detail rows, or aggregate before joining.
• Filtering away the outer rows. In a LEFT JOIN, a WHERE condition on the right table (WHERE o.status = 'paid') removes the NULL rows and turns it into an inner join. Put such conditions in the ON clause if you want to keep unmatched rows.

Try every one of these on real tables. The playground runs SQLite in your browser; the Wrong total and Filter trap examples reproduce both traps:

How the database actually runs a join

JOIN describes what to combine, not how. The database’s planner picks a physical algorithm based on table sizes and the indexes available — and the choice matters enormously. Step through the same join four ways and compare the work:

The planner’s choice shows up in EXPLAIN output as Nested Loop, Hash Join or Merge Join. The nested loop without an index is the one to fear: harmless on small tables, catastrophic on big ones — and it is exactly what an index on the join column prevents.

Joins make it easy to ask big questions of big tables. Next, we look at how the database answers them quickly: indexes.