~/learn/SQL · Joins
Joins
Combine rows from two tables that share a key.
why this matters
Data is split across tables — customers in one, orders in another. Joins stitch them back together so you can ask "what did each customer buy?".
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
Mental model
orders.customer_id == customers.id
orders (12 rows) customers (8 rows)
\ /
JOIN ON the key -> one wide row per matching pair
INNER JOIN keeps matches only. LEFT JOIN keeps every left row and pads the rest with NULL.Normalised databases split data across tables. A JOIN stitches them back together by matching a key — usually a foreign key pointing at another table's primary key.
Try it: Keep only the completed orders.
An INNER JOIN (the default) keeps only rows that match on both sides. A LEFT JOIN keeps every row of the left table and fills the right-hand columns with NULL where nothing matched — that is how you find customers with no orders.
Note
Joins and aggregation combine naturally: join first to widen the rows, then GROUP BY to summarise them.
Common gotcha
Putting a left table's condition in WHERE after a LEFT JOIN silently turns it back into an inner join, because NULL rows fail the test. Keep those conditions in the ON clause.
Pro tip
Always write table aliases (customers c, orders o) and prefix columns (c.id, o.customer_id). It avoids 'ambiguous column' errors.
Row explosion
Joining on the wrong column multiplies rows. If your row count suddenly explodes, check the ON condition.
key takeaways
- INNER JOIN keeps only matching rows.
- LEFT JOIN keeps every left row, NULLs where no match.
- ON says which columns link the tables.
check your understanding
You want every customer, including those with no orders. Which join?
free practice
Build confidence with a question you can solve without Pro.