Chapter 18 · intermediate · 10 min
JOINs: combining tables
Real databases spread information across many tables: customers in one, their orders in another. A JOIN stitches them back together on a shared key, so you can ask questions that span both.
This is the concept interviewers lean on hardest, and the one where the difference between INNER and LEFT trips people up. We'll use a customers table and an orders table.
The dataset
Two tables: `customers` (id, name, city) and `orders` (id, customer_id, amount). One customer (Devi) has no orders, and one order (105) points at a customer_id that doesn't exist, an orphan row, useful for seeing what RIGHT and FULL OUTER JOIN do differently.
The join key
The two tables connect through a shared value: orders.customer_id points at customers.id. A JOIN matches rows where those line up. You almost always qualify columns with their table name (or an alias) so it's clear which id you mean.
Match customers to their orders
INNER JOIN keeps only matches
The plain JOIN above is an INNER JOIN, the default. It keeps a row only when there's a match on both sides:
| name | amount |
|---|---|
| Aisyah | 120.00 |
| Aisyah | 80.00 |
| Bala | 260.00 |
| Chong | 45.00 |
Look closely at who's missing: Devi. She's a customer with no orders, so the inner join drops her entirely.
Devi has no orders: she vanishes
LEFT JOIN keeps every left-hand row
A LEFT JOIN keeps every row from the left (first) table, matched or not. Where there's no match, the right-hand columns come back as NULL:
| name | amount |
|---|---|
| Aisyah | 120.00 |
| Aisyah | 80.00 |
| Bala | 260.00 |
| Chong | 45.00 |
| Devi | NULL |
Now Devi appears, with a NULL amount. The join preserved her. This is the single most important JOIN distinction in interviews: "customers who have never ordered" is a LEFT JOIN question.
Devi returns, with NULL
RIGHT JOIN: the mirror image
RIGHT JOIN flips which side is protected: it keeps every row from the right (second) table, matched or not. Order 105 belongs to a customer_id (99) that doesn't exist in customers, so a plain JOIN would drop it, but RIGHT JOIN keeps it, with NULL customer columns.
In practice, most people never write RIGHT JOIN; they reorder the tables and use LEFT JOIN instead, since it reads more naturally. But you'll see RIGHT JOIN in other people's code, so it's worth recognizing.
Order 105 survives, with NULL customer
FULL OUTER JOIN: keep everything, both sides
FULL OUTER JOIN keeps every row from both tables, matched or not, the combination of what LEFT and RIGHT JOIN each protect. This is the one query that shows Devi (a customer with no order) and order 105 (an order with no customer) at the same time, each with NULLs on the side that didn't match.
Use this when you need a complete audit of mismatches in either direction: "show me everything that doesn't line up, from both tables."
Both mismatches at once: Devi and order 105
The four JOIN types, side by side
| JOIN type | Keeps unmatched left rows? | Keeps unmatched right rows? | Typical use |
|---|---|---|---|
INNER JOIN | no | no | only rows that exist on both sides |
LEFT JOIN | yes | no | "all X, whether or not they have a matching Y" |
RIGHT JOIN | no | yes | rarely written directly, usually rewritten as LEFT JOIN |
FULL OUTER JOIN | yes | yes | audit every mismatch, both directions |
Finding the non-matches
Combine a LEFT JOIN with WHERE ... IS NULL and you get exactly the rows that did not match: customers with zero orders:
| name |
|---|
| Devi |
This "left join then filter for NULL" is a classic pattern worth memorising.
Customers who never ordered
Join, then aggregate
JOINs and GROUP BY combine naturally: join the tables, then summarise. Here we total each customer's spend, using a LEFT JOIN plus COALESCE (turn NULL into 0) so Devi shows up with a spend of 0 rather than disappearing:
| name | total_spend |
|---|---|
| Bala | 260.00 |
| Aisyah | 200.00 |
| Chong | 45.00 |
| Devi | 0 |
Total spend per customer
If you've used Excel or Google Sheets
INNER JOIN is the same job as VLOOKUP/XLOOKUP when every lookup value is guaranteed to exist: pulling matching data from a second table by a shared key. LEFT JOIN is what XLOOKUP does when you give it an if_not_found fallback instead of erroring on a miss; it keeps going even without a match. Spreadsheets have no native equivalent of RIGHT JOIN or FULL OUTER JOIN; they're SQL-specific tools for handling mismatches in either direction at once.
Ready to practice? Shopee's customers with no orders question below is the exact LEFT JOIN + IS NULL pattern from above.
Get comfortable narrating a join in words, like "for each customer, find matching orders; keep everyone even without a match", and the SQL writes itself. Next: what happens when a table needs to join to itself, and when one query needs three or more tables.