Joins that hold up

A join matches rows from two tables and returns the combinations. That is all it does — and almost every join bug comes from forgetting the second half of that sentence: combinations. If a row on the left matches three rows on the right, you get three output rows.

INNER JOIN

SELECT c.full_name, o.id, o.status
FROM customer c
JOIN "order" o ON o.customer_id = c.id;

JOIN means INNER JOIN. Rows survive only when the condition is true on both sides — a customer with no orders disappears, and an order with a dangling customer_id disappears too. In our schema the foreign key makes the second case impossible, which is the point of having it.

LEFT JOIN, and the trap

LEFT JOIN keeps every row from the left table, filling the right side with NULL where nothing matched.

SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id;

Now here is the mistake, and it is the most common join bug there is:

-- WRONG: this is an INNER JOIN wearing a LEFT JOIN costume
SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE o.status = 'paid';

The LEFT JOIN dutifully produces NULL rows for customers with no paid order — and then WHERE o.status = 'paid' evaluates to NULL for those rows and throws them away. You asked for all customers and got only the ones with paid orders.

The fix is to decide what the condition is for:

-- A filter on the right table belongs in ON: it decides what counts as a match
SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id AND o.status = 'paid';

-- A filter on the left table belongs in WHERE: it decides which rows to consider
SELECT c.full_name, o.id
FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE c.country = 'ID';

For an INNER JOIN the distinction does not change the result, which is exactly why people learn the habit of putting everything in WHERE and then get bitten the first time they change the join type.

RIGHT JOIN

RIGHT JOIN is LEFT JOIN with the tables swapped. It exists, both engines support it, and it is rare in practice for one reason: a reader has to hold two tables in mind in the opposite order to how they are written. Write LEFT JOIN and reorder the tables.

FULL OUTER JOIN

Keeps unmatched rows from both sides.

In MySQL you build it from two outer joins and a UNION:

-- PostgreSQL
SELECT c.id, o.id
FROM customer c
FULL OUTER JOIN "order" o ON o.customer_id = c.id;

-- MySQL: the same result
SELECT c.id AS customer_id, o.id AS order_id
FROM customer c LEFT JOIN `order` o ON o.customer_id = c.id
UNION
SELECT c.id, o.id
FROM customer c RIGHT JOIN `order` o ON o.customer_id = c.id;

Use UNION, not UNION ALL — the matched rows appear in both halves and UNION deduplicates them. That deduplication is also why this is slower than the real thing.

CROSS JOIN, deliberate and accidental

A cross join returns every combination: 1,000 customers by 500 products is 500,000 rows.

-- Deliberate, and legitimate: a grid to left-join real data onto
SELECT c.id, p.id FROM customer c CROSS JOIN product p;

The accidental version comes from the old comma syntax with a forgotten condition:

-- Two tables, no join condition. This is a cross join.
SELECT * FROM customer c, "order" o;

Nothing warns you. On small tables it looks like a slow query; on large ones it fills a disk. This is the strongest argument for always writing explicit JOIN ... ON: a missing ON is a syntax error, while a missing WHERE is a Tuesday.

Semi-joins and anti-joins

Often you do not want the columns from the other table — only to know whether a match exists. Joining for that reason duplicates rows and then needs DISTINCT to undo the damage.

-- Semi-join: customers who have at least one order. No duplicates, no DISTINCT.
SELECT * FROM customer c
WHERE EXISTS (SELECT 1 FROM "order" o WHERE o.customer_id = c.id);

-- Anti-join: customers with none
SELECT * FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM "order" o WHERE o.customer_id = c.id);

-- Anti-join, the other spelling: LEFT JOIN and keep the misses
SELECT c.* FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE o.id IS NULL;

All three are fine. EXISTS says what it means, and it never has the NOT IN NULL problem from lesson 2. Both planners usually execute EXISTS and the LEFT JOIN form identically.

Self joins

A table joined to itself needs aliases, because otherwise no column reference is unambiguous.

-- Pairs of orders placed by the same customer within an hour of each other
SELECT a.id, b.id, a.customer_id
FROM "order" a
JOIN "order" b
  ON b.customer_id = a.customer_id
 AND b.id > a.id                                  -- each pair once, not twice
 AND b.placed_at < a.placed_at + interval '1 hour';

b.id > a.id is doing real work: without it every pair appears twice, once in each direction, plus every row joined to itself. In MySQL the interval is a.placed_at + INTERVAL 1 HOUR.

USING, and why NATURAL JOIN is a trap

-- ON, spelled out
JOIN order_item i ON i.order_id = o.id

-- USING: only when the columns have the same name in both tables
SELECT * FROM "order" o JOIN order_item i USING (order_id);   -- if o had order_id

USING also merges the column so it appears once in SELECT *, which is occasionally what you want.

NATURAL JOIN joins on every column that shares a name, without you listing them. Both engines support it, and it should be avoided: add a created_at column to both tables and the join silently starts matching on it too, changing the result of a query nobody edited.

Join order, and what the planner does with it

The order you write joins in does not determine the order they execute in. Both engines reorder joins based on statistics about table sizes and value distributions.

EXPLAIN
SELECT c.full_name, i.quantity
FROM customer c
JOIN "order" o      ON o.customer_id = c.id
JOIN order_item i   ON i.order_id = o.id
WHERE c.country = 'ID';

Both may start from customer (if country = 'ID' is selective) or from order_item (if it is not). Lesson 8 is about reading that decision.

Occasionally the planner gets it wrong and you need to override:

Both are last resorts. A hint is a statistic frozen at the time you wrote it, and it stops being true as the data grows.

Many joins, and fan-out again

Every one-to-many join multiplies rows, and multiple such joins multiply with each other:

Imagine a shipment table alongside our four — an order can have several, just as it has several items:

-- If an order has 3 items and 2 shipments, this returns 6 rows per order
SELECT o.id, i.product_id, s.tracking_code
FROM "order" o
JOIN order_item i ON i.order_id = o.id
JOIN shipment   s ON s.order_id = o.id;

Nothing here is wrong syntactically, and any aggregate over it is nonsense. When you need two independent one-to-many relationships in one result, aggregate each separately first — as in lesson 3 — or use two queries.

What to take away

• A filter on the outer-joined table goes in ON. In WHERE it cancels the outer join.
• MySQL has no FULL OUTER JOIN; build it with LEFT + RIGHT + UNION.
• Explicit JOIN ... ON, never comma joins — a missing ON fails loudly.
• Use EXISTS / NOT EXISTS when you only need existence.
• Avoid NATURAL JOIN; a new column silently changes the join.
• Two one-to-many joins in one query multiply. Aggregate before joining.