Chapter 09 · 8 min · Basic
Bringing in the other table
Everything so far has been one table at a time. That was deliberate: almost every mistake in this track happens inside a single table, and a join on top of an unexamined table just hides them.
Now the second table, because sooner or later you need a name beside an ID. This is the only chapter here that uses a JOIN, and it is the last new piece of SQL in the track.
The framing that matters: a join is not "adding a column". It is a rule for matching rows, and matching can both lose rows and create rows. You will see both happen, in the same query, in a way that leaves the row count looking completely normal.
The dataset
Eight orders and five customer records. Two things in here are completely ordinary and both will bite: customer 101 has two rows in customers (she signed up twice, exactly like the duplicates chapter), and orders 6 and 7 belong to customers 105 and 106, who are not in the customer table at all — perhaps the export was partial, perhaps those accounts were deleted. Nobody has told you either fact.
Schema
| customer_id | int |
| text | |
| full_name | text |
| order_id | int |
| customer_id | int |
| amount_myr | numeric(7,2) |
| ordered_on | date |
Example data
Write down the truth before you join
This is the habit that protects you, and it is one query you run before touching the second table.
Eight orders, RM 655.50. That is the truth about orders, established while only one table is involved, and nothing a join does can change how much money was taken.
Write those two numbers down. After the join, you check them again. If either has moved, the join changed your data — and unless you deliberately intended a filter, a changed total is a bug, not a result.
Most people skip this step because it feels like it proves nothing. It is the single cheapest defence against the two failures in the rest of this chapter, and it costs four seconds.
Eight orders. Add up the amounts in the dataset above — what should the total be?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to actually do with a dataset once somebody hands you one, from the first look to a number you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The join everyone writes
- 03Check the total, not the row count
- 04LEFT JOIN keeps your rows
- 05Go and look at what did not match
- 06Fix the duplication where it lives
- 07The version you would send, with its caveats