Real databases spread information across many tables (employees in one, departments in another) linked by a shared key. A JOIN stitches them back together so you can use columns from both in one query. This is the last fundamental, and the one with the most famous beginner trap.
A JOIN matches rows from two tables on a shared key. INNER JOIN keeps only rows that match in both; LEFT JOIN keeps every row from the left table even when the right side has no match.
The setup: two related tables
Here employees.dept_id points at departments.dept_id. The join key is dept_id.
- name
- dept_id (FK)
- salary
- dept_id (PK)
- dept_name
- location
INNER JOIN — only matching rows
See the match first: each employee's dept_id is looked up in departments. Rows whose key matches share a colour; rows with no partner (Di's dept_id 99, and Marketing, which has no employees) stay grey and never reach the result:
employees
| name | dept_id | salary |
|---|---|---|
| Ada | 1 | 62000 |
| Bo | 2 | 48000 |
| Cy | 1 | 71000 |
| Di | 99 | 52000 |
departments
| dept_id | dept_name | location |
|---|---|---|
| 1 | Engineering | Antwerp |
| 2 | Sales | Ghent |
| 3 | Marketing | Bruges |
INNER JOIN ON employees.dept_id = departments.dept_idresult
| name | dept_id | salary | dept_name | location |
|---|---|---|---|---|
| Ada | 1 | 62000 | Engineering | Antwerp |
| Bo | 2 | 48000 | Sales | Ghent |
| Cy | 1 | 71000 | Engineering | Antwerp |
Try it now: write the INNER JOIN the visual showed, each employee's name and dept_name (only the matched ones).
Your turn · Exercise
INNER JOIN employees to departments on dept_id. Return name and dept_name (matched rows only).
The data
employees
4 rows returned
| name | dept_id |
|---|---|
| Ada | 1 |
| Bo | 2 |
| Cy | 1 |
| Di | 99 |
departments
3 rows returned
| dept_id | dept_name |
|---|---|
| 1 | Engineering |
| 2 | Sales |
| 3 | Marketing |
INNER JOIN returns a row only where the key exists in both tables. Run it: each employee gets their department's name and location.
Two things to notice: we gave the tables aliases (e, d) so we can write e.name and d.dept_name, and the ON clause states which keys must match.
Watch out
Look closely at the result: Di is missing. Di's dept_id is 99, which doesn't exist in departments, so INNER JOIN drops the row entirely, silently. This is the trap: an inner join quietly discards rows that don't match, and your headcount comes up short without any error.
LEFT JOIN — keep every row from the left
A LEFT JOIN keeps all rows from the left table; where the right table has no match, its columns come back NULL. Watch Di survive this time, kept in the result, with the department columns filled by NULL:
employees
| name | dept_id | salary |
|---|---|---|
| Ada | 1 | 62000 |
| Bo | 2 | 48000 |
| Cy | 1 | 71000 |
| Di | 99 | 52000 |
departments
| dept_id | dept_name | location |
|---|---|---|
| 1 | Engineering | Antwerp |
| 2 | Sales | Ghent |
| 3 | Marketing | Bruges |
LEFT JOIN ON employees.dept_id = departments.dept_idresult
| name | dept_id | salary | dept_name | location |
|---|---|---|---|---|
| Ada | 1 | 62000 | Engineering | Antwerp |
| Bo | 2 | 48000 | Sales | Ghent |
| Cy | 1 | 71000 | Engineering | Antwerp |
| Di | 99 | 52000 | NULL | NULL |
Run it: now Di stays, with empty department fields.
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| Keeps | Only rows matching in both tables | Every left-table row, matched or not |
| Unmatched left rows | Dropped silently | Kept, right columns = NULL |
| Use when | You only want matched records | You must not lose any left-table rows |
Which one do I use?
Ask: "do I need every row from my main table, even the ones without a match?" If yes, use LEFT JOIN. That covers headcounts, "customers and their orders (including those who never ordered)", and completeness checks. If you genuinely only want matched pairs, INNER JOIN.
From practice
When a joined query returns fewer rows than the table you started from, an INNER JOIN silently dropping unmatched rows is the first thing to suspect. Switch to LEFT JOIN and look for the NULLs; they tell you exactly which keys didn't match.
Your turn
Keep every employee. List each employee with their department name, including Di, whose department is unknown. (Which join keeps the unmatched row?)
Your turn · Exercise
List every employee with their department name, keeping employees even when no department matches. Return name and dept_name.
The data
employees
4 rows returned
| name | dept_id | salary |
|---|---|---|
| Ada | 1 | 62,000 |
| Bo | 2 | 48,000 |
| Cy | 1 | 71,000 |
| Di | 99 | 52,000 |
departments
3 rows returned
| dept_id | dept_name | location |
|---|---|---|
| 1 | Engineering | Antwerp |
| 2 | Sales | Ghent |
| 3 | Marketing | Bruges |
That's the foundation: selecting, filtering, sorting, aggregating, grouping, and joining. You're now ready for SQL for Data Analysts, which picks up where this leaves off (window functions, CTEs, and the patterns real reporting runs on), and for the business cases, where you'll use all of it on deliberately messy data.