Unit 3: 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.
The big picture
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 this unit
- 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.
- 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.
- 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 as `WHERE NOT EXISTS (…)`.
- Self joins: a table joined to itself: A self join joins a table to itself, under two different aliases. That lets a row be compared with another row of the same table, such as an employee with their manager.
- Joining three or more tables: You join more than two tables by adding one JOIN … ON for each table, following the keys: orders to customers, orders to order_items, order_items to products. Each result row can then hold columns from all of them.
- 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.
- Join traps: double counting, and ON versus WHERE: There are two classic join bugs. Fan-out: joining a "many" table repeats the "one" side, so its numbers get summed several times. And a right-table filter in WHERE, which silently turns a LEFT JOIN into an INNER JOIN.