JOINs: Combining Tables on a Key

Well-designed schemas store each fact once, so an order holds a customer_id, not a name. To see names you JOIN. INNER JOIN returns one row for every pair of rows where the ON condition is true; a row with no partner on the other side is dropped.

SELECT o.id, c.name, o.amount
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
WHERE o.amount > 500;

Aliases (orders AS o) shorten table names and become required when the same column name exists in both tables: id alone is ambiguous, o.id versus c.id is not. Qualify every column with its alias as soon as more than one table is involved.

Filter and sort after joining as usual: the join builds the combined rows first, then WHERE and ORDER BY work on them. ON is for the matching rule; put your real filters in WHERE. For an INNER JOIN the two behave the same, but the habit matters once LEFT JOINs arrive.

Self-join. A table can join to itself when it references itself - an employee row has manager_id pointing at another employee. Give the table two aliases so each plays a different role:

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

Chaining joins. Add one JOIN per extra table and read it as a path: each ON links the table you just added to one already in the query.

SELECT o.id, c.name, ct.country
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN cities    AS ct ON ct.id = c.city_id;

Duplicates are a join symptom. If one customer has five orders, that customer appears five times in the joined result. When a count looks too big after a join, you are counting joined rows, not customers: use COUNT(DISTINCT c.id) or aggregate before you join.

A JOIN with a missing or wrong ON condition is effectively a cross join: every row paired with every row, so 1,000 x 1,000 rows becomes 1,000,000 results. If a query suddenly returns far too many rows, check the ON condition first.