Joins

Why joins exist

Almost every useful question spans two tables or more (see Designing good tables). A join pairs rows wherever a condition is true, nearly always foreign key equals primary key. Dev has no order; order 13 has no customer.

The join family

Every join has the shape FROM a JOIN b ON condition; only the word before JOIN changes.

INNER JOIN: pairs only

Plain JOIN means INNER JOIN in both engines: matching pairs and nothing else. Dev and order 13 both vanish.

SELECT c.name, o.id AS order_id, o.status, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

LEFT JOIN: keep everything on the left

A LEFT JOIN (OUTER is optional) keeps every row of the first table, with NULLs where no partner was found. It answers "including those with none".

SELECT c.name, o.id AS order_id, o.status, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

RIGHT JOIN: the mirror

A RIGHT JOIN keeps every row of the second table. Most teams avoid it, since a RIGHT JOIN b is b LEFT JOIN a backwards.

SELECT c.name, o.id AS order_id, o.status, o.total
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id;

FULL OUTER JOIN: keep both sides

A FULL OUTER JOIN keeps unmatched rows from both tables, which reconciliation needs. PostgreSQL has it; MySQL has no FULL OUTER JOIN in any version (8.4, 9.7 or 26.x), so glue LEFT and RIGHT JOIN with UNION (MySQL vs PostgreSQL).

SELECT c.name, o.id AS order_id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.id;

-- No FULL OUTER JOIN in MySQL: LEFT JOIN, then RIGHT JOIN, joined by UNION
SELECT c.name, o.id AS order_id
FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id
UNION
SELECT c.name, o.id
FROM customers AS c RIGHT JOIN orders AS o ON o.customer_id = c.id;

UNION drops the shared rows. If duplicates are legitimate, use UNION ALL plus WHERE c.id IS NULL on the RIGHT JOIN half.

CROSS JOIN: every combination

A CROSS JOIN has no ON clause: every left row pairs with every right row. It builds grids, like every customer against every product.

SELECT c.name AS customer, p.name AS product
FROM customers AS c
CROSS JOIN products AS p;

Self join

Two aliases and a table joins to itself, as with employees.manager_id. Here b.id > a.id lists each pair of a customer's orders once.

SELECT a.customer_id, a.id AS first_order, b.id AS later_order
FROM orders AS a
JOIN orders AS b ON b.customer_id = a.customer_id AND b.id > a.id;

Semi-joins and anti-joins

Sometimes you filter by whether a partner exists, without returning it. An INNER JOIN lists Amira twice; EXISTS returns each customer once, and so does IN with a subquery; the optimizer calls both a semi-join.

SELECT c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

-- Same rows:
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);

Anti-join: has none

NOT EXISTS is the direct spelling. The older idiom is a LEFT JOIN plus WHERE o.id IS NULL: keep everyone, then keep the NULL rows.

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

-- The older idiom, same result:
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

The NOT IN trap

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- Returns 0 rows. No error. Dev is not listed.

The subquery returns 1, 1, 2, NULL, 3, and 4 NOT IN (1, 1, 2, NULL, 3) includes 4 = NULL, which is unknown, so WHERE keeps nothing. Any nullable foreign key does this. Use NOT EXISTS instead.

Joining four tables at once

Joins chain: each JOIN adds one table, and its ON clause connects it to a table already there.

SELECT c.name AS customer, o.id AS order_id, p.name AS product, oi.qty
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id   = o.id
JOIN products    AS p  ON p.id          = oi.product_id
ORDER BY c.name, o.id;

Amira appears once per item line. Order 13's mouse is missing because the first join is an INNER JOIN; a LEFT JOIN from orders brings it back.

ON or WHERE: where the filter goes changes the answer

With an INNER JOIN, ON and WHERE give the same rows. With an outer join they differ, which is the most common reporting bug. You want every customer with their paid orders, Dev included.

-- A: the filter in WHERE
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- B: the filter in ON
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid';

In A, WHERE tested NULL = 'paid' for Dev, which is unknown, and dropped him; Ben and Chloé went too. The LEFT JOIN became an INNER JOIN. In B the condition sits in ON, which only decides which orders may pair.

How the engine joins

The optimizer picks the method and names it in the plan (Indexes and query performance). A nested loop looks up each row's partners in the other table: cheap through an index, terrible without one. Hash and merge joins read each input once instead.

Why join columns need an index, and who creates it

Join columns are usually foreign keys, looked up on every join and every ON DELETE check. MySQL InnoDB requires an index there and creates one if missing. PostgreSQL leaves it to you, and calls an index there "often a good idea".

The slow join: what a DBA looks for

A missing index on the join column. The symptom is a full scan of a big table on the inner side of a nested loop. EXPLAIN ANALYZE shows rows examined; thousands of times the rows returned means a missing index.

Fan-out. A join to the "many" side multiplies rows, so totals from the "one" side come out too big.

SELECT c.name,
       COUNT(*)             AS joined_rows,
       COUNT(DISTINCT o.id) AS orders,
       SUM(o.total)         AS spent
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.name;

Order 10 has two item lines, so its 65.00 is added twice: 245.00 becomes 310.00. When COUNT(*) and COUNT(DISTINCT key) disagree, the join has fanned out; aggregate the many side first (Aggregation and window functions).

Further reading

Practice beats reading; see Practice sites and working with AI.

• MySQL 8.4: JOIN clause

  Every join form MySQL accepts.

• MySQL 8.4: Hash join optimization

  When the optimizer picks one.

• MySQL 8.4: FOREIGN KEY constraints

  InnoDB indexes the referencing column.

• PostgreSQL: Table expressions and joined tables

  Every join type, worked examples.

• PostgreSQL: Foreign keys

  Why it needs your index.