JOINs

Relational databases split data across tables to avoid repetition: customers in one table, their orders in another. A JOIN puts them back together for a query.

The examples use customers(id, name, city), orders(id, customer_id, total, created_at) and order_items(order_id, product_id, qty, unit_price).

The basic INNER JOIN

SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01';

The ON clause states how rows match, almost always a foreign key equal to a primary key. Table aliases (o, c) keep the query short, and qualifying every column (c.name) prevents ambiguity when both tables have a name column.

Join	Returns
INNER JOIN	Only matching rows in both tables
LEFT JOIN	All rows from left + matches (NULL if none)
RIGHT JOIN	All rows from right + matches
CROSS JOIN	Every combination

MySQL has no FULL OUTER JOIN; simulate it with a LEFT JOIN ... UNION ... RIGHT JOIN.

LEFT JOIN and the anti-join

Find customers with no orders:

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

That pattern — LEFT JOIN plus IS NULL on the right-hand table — is the classic "which rows are missing?" query.

Be careful where a condition on the right-hand table goes:

-- Every customer, with their 2026 order count (0 if none)
SELECT c.name, COUNT(o.id) AS orders_2026
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.id
      AND o.created_at >= '2026-01-01'    -- in ON: keeps customers with no 2026 orders
GROUP BY c.id, c.name;

Moving that date test into WHERE would drop rows whose right side is NULL, silently turning the LEFT JOIN back into an INNER JOIN.

Joining more than two tables

SELECT c.city,
       COUNT(DISTINCT o.id)        AS orders,
       SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.city
ORDER BY revenue DESC;

Note COUNT(DISTINCT o.id): once each order joins to several items, a plain COUNT(*) counts items, not orders.

Self joins

A table can join to itself, which is how you walk a hierarchy:

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

Common mistakes

• Omitting the ON clause, producing a cartesian product of every row against every row.
• Filtering a LEFT JOINed table's columns in WHERE and losing the unmatched rows.
• Double counting totals when one order joins to many items; aggregate the child table first, or use DISTINCT.
• Joining columns of different types (INT to VARCHAR), which forces a cast and blocks index use.
• Forgetting an index on the foreign key column — joins on unindexed columns are slow.