Phase 3 of 8 · 7 lessons
Joins: combining tables
Put rows from two or more tables side by side, keep or drop the rows that have no match on purpose, and spot the joins that quietly double your totals.
Data is split across tables so each fact is stored once. Joins put it back together. Nearly every real query and most interview questions use at least one join — and most wrong answers come from choosing the wrong kind.
Lessons, in order
-
3.1
Joins, and INNER JOIN
A JOIN combines rows from two tables wherever a condition (usually matching keys) is true, and INNER JOIN keeps only the rows that found a match on both sides.
-
3.2
LEFT JOIN: keep every row from one side
A LEFT JOIN keeps every row from the left (first) table, joins matching rows from the right table, and fills the right-hand columns with NULL where there is no match.
-
3.3
Finding what is missing: anti-joins
An anti-join returns rows from one table that have no match in another — written as
LEFT JOIN … WHERE right.key IS NULL, or asWHERE NOT EXISTS (…). -
3.4
Self joins: a table joined to itself
A self join joins a table to itself under two different aliases, so a row can be compared with another row of the same table — such as an employee with their manager.
-
3.5
Joining three or more tables
You join more than two tables by adding one JOIN … ON per table, walking along the keys — orders to customers, orders to order_items, order_items to products — so each row can hold columns from all of them.
-
3.6
FULL OUTER JOIN and CROSS JOIN
A FULL OUTER JOIN keeps every row from both tables, with NULLs wherever either side has no match; a CROSS JOIN pairs every row of one table with every row of the other.
-
3.7
Join traps: double counting, and ON versus WHERE
The two classic join bugs are fan-out — joining a "many" table repeats the "one" side, so its numbers get summed several times — and putting a right-table filter in WHERE, which silently turns a LEFT JOIN into an INNER JOIN.