LEARN BY DOING
SQL joins & NULL explorer
Follow real rows through INNER and LEFT joins. Move a predicate between ON and WHERE and see what changes.
Customers
| row_id | id | name |
|---|---|---|
| 1 | 1 | Ada |
| 2 | 2 | Ben |
| 3 | 3 | Cy |
| 4 | NULL | No ID |
Orders
| id | customer_id | total |
|---|---|---|
| A | 1 | 20 |
| B | 1 | 80 |
| C | NULL | 100 |
| D | 4 | 40 |
Follow the query
SELECT c.name, o.id, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
ORDER BY c.row_id, o.id;5 result rows
| name | order id | total |
|---|---|---|
| Ada | A | 20 |
| Ada | B | 80 |
| Ben | NULL | NULL |
| Cy | NULL | NULL |
| No ID | NULL | NULL |
NULL equality does not produce a match, including NULL against NULL. Ada has two matching orders. A LEFT join supplies NULLs for unmatched customers; a WHERE comparison then filters those NULL-extended rows out. An ON predicate changes matching before unmatched rows are supplied.