SQL joins

Why it matters

Normalization spreads facts across tables; joins bring them back together. Almost every real query has at least one, and the classic mistakes (an outer join quietly turned into an inner join, duplicated rows from joining on a non-unique column, missing rows that had no match) produce wrong numbers rather than errors. Joins are also the most common SQL interview topic, usually as "write a query that...".

The idea

A join combines rows from two tables where a condition holds. Using Employee(emp_id, name, dept_id) and Department(dept_id, dname), with one employee having no department and one department having no employees:

The ON versus WHERE trap

For inner joins, a condition in ON and in WHERE behaves the same. For outer joins it does not. The ON condition decides which rows match; unmatched left rows survive with NULLs. A WHERE condition runs after the join and filters the result, and any condition on the right table's columns is UNKNOWN for NULL-padded rows, so those rows disappear.

-- every department, with its employees hired in 2026 (departments with none still appear)
SELECT d.dname, e.name
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id AND e.hired_year = 2026;

-- wrong: the WHERE filter removes departments with no 2026 hires, so this is an inner join in disguise
SELECT d.dname, e.name
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
WHERE e.hired_year = 2026;

Rule: conditions on the optional (outer) side belong in ON; conditions that should remove rows from the result belong in WHERE.

Anti joins and semi joins

"Departments with no employees" is an anti join. Three common forms: LEFT JOIN ... WHERE e.emp_id IS NULL, NOT EXISTS (SELECT 1 FROM employee e WHERE e.dept_id = d.dept_id), and NOT IN (...), which breaks if the subquery can return NULL, as the relational model lesson showed. Prefer NOT EXISTS. "Departments that have at least one employee" is a semi join: use EXISTS, which returns each department once, rather than an inner join that repeats a department per employee and then needs DISTINCT.

Self joins

A table joined with itself answers hierarchical and comparative questions: each employee with their manager (employee e JOIN employee m ON e.manager_id = m.emp_id), pairs of employees in the same department, or consecutive events for the same user. Aliases are mandatory.

Row multiplication

A join produces one row per matching pair. Joining on a column that is not unique on either side multiplies rows: orders joined to order lines and to payments on order_id gives lines × payments rows per order, and summing amounts then double counts. Aggregate each child table separately (in subqueries or CTEs) before joining, or join only on keys that are unique on one side.

How joins are executed

The optimizer chooses among nested-loop, hash and sort-merge joins based on table sizes and indexes; the storage module covers them. For writing SQL, the practical points are to join on indexed keys, filter early, and select only the columns you need.

Trade-offs

Inner joins are simplest and drop unmatched data silently; outer joins preserve it at the cost of NULL handling downstream. Anti joins via NOT EXISTS are safe and usually well optimised; NOT IN is concise and fragile with NULLs. Joining many tables in one query is fine when keys are indexed; when it multiplies rows, aggregate before joining.

In an interview

Write it and narrate the join type: "Every customer with their order count including zero: customers LEFT JOIN orders, COUNT(o.order_id) not COUNT(), because COUNT() would count the NULL-padded row as 1." That single detail (COUNT of the right-side key) is what distinguishes a correct answer.