Learn SQL: IS NULL
Missing values with IS NULL
End of the day. "Which tapes haven't come back yet?" asks Vic. "And which films has nobody rated?" For that you need to learn to search for what isn't there.
NULL means "unknown"
NULL means there's no value: unknown or not filled in yet. It isn't 0 and it isn't empty text.
New films don't have a score yet, because no member has rated them. With IS NULL you find them.
SELECT title, year, score FROM films WHERE score IS NULL| title | year | score |
|---|---|---|
| Dune | 2021 | NULL |
| Barbie | 2023 | NULL |
| Het smelt | 2023 | NULL |
Never = NULL
Comparing anything with NULL always gives "unknown", even NULL = NULL. That's why WHERE score = NULL never finds a row. Use IS NULL and IS NOT NULL.
This query shows the rentals that have come back: those have a returned_on filled in.
SELECT id, film_id, rented_on, returned_on FROM rentals WHERE returned_on IS NOT NULL LIMIT 6| id | film_id | rented_on | returned_on |
|---|---|---|---|
| 1 | 1 | 2024-10-01 | 2024-10-03 |
| 2 | 6 | 2024-10-01 | 2024-10-02 |
| 3 | 19 | 2024-10-02 | 2024-10-08 |
| 4 | 22 | 2024-10-03 | 2024-10-04 |
| 5 | 7 | 2024-10-03 | 2024-10-05 |
| 6 | 12 | 2024-10-04 | 2024-10-12 |
Try it yourself
Vic wants to put the new films without a score in the spotlight, but only the priciest. Show title, year and price_per_day of the films without a score that cost more than 3 euros.
Ready for more?
The full lesson has 7 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson