Table Joins

A join pairs rows from two tables#

You store customers and orders separately, so reports need to stitch them back together. A join does that by matching a condition, usually foreign key to primary key.

customers          orders
id  name           id  customer_id
1   Asha     ◀──── 1   1
1   Asha     ◀──── 2   1
2   Ravi     ◀──── 3   2
3   Meera    ◀──── 4   3
4   Dan            (no orders)

INNER JOIN#

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

Only rows with a match on both sides survive. Dan has no orders, so he vanishes. Use table aliases (c, o) and prefix every column. It prevents ambiguity errors and makes the query self-documenting.

LEFT JOIN#

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Keeps every left row. Where there's no match, right-side columns are NULL. Dan appears with 0 orders. Note COUNT(o.id), not COUNT(*), which would count Dan's NULL-padded row as 1.

The anti-join pattern finds rows with no match:

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

The ON versus WHERE trap with LEFT JOIN#

-- Looks like "all customers, their paid orders"
SELECT c.name, o.id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';     -- turns it into an inner join

Rows with no order have o.status NULL, which fails the WHERE, so Dan is gone again. Put the filter in ON to keep the outer behavior:

LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'

RIGHT, CROSS, and self-joins#

RIGHT JOIN is a LEFT JOIN with the tables swapped. Most teams write only LEFT JOIN to keep reading order consistent.

CROSS JOIN pairs every row with every row: 5 products times 4 customers gives 20 rows. It's useful for generating combinations, and a disaster when you accidentally write a join with no condition.

A self-join joins a table to itself, handy for hierarchies:

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

Joining three tables#

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

Row multiplication#

Joining a one-to-many relationship repeats the "one" side. If you SUM a customer-level value after joining order items, it gets counted once per item. Aggregate in a subquery or CTE first, then join.

Common mistakes#

• Filtering the right table of a LEFT JOIN in WHERE.
• COUNT(*) after a left join.
• Forgetting the ON clause and creating a cartesian product.
• Not indexing the join columns. Foreign keys are indexed automatically in InnoDB, but a join on other columns isn't.

Practice#

List every customer with their total spent, including customers who never ordered (show 0). Then list products that have never been sold. Finally, write a query listing each product with the names of customers who bought it.