Learn SQL: IN
A list with IN
"Members from Leuven, Gent or Hasselt get a flyer," says Vic. Typing `OR` three times works. But it can be shorter, and easier to read.
IN is a short OR
With IN you test whether a value is in a list. town IN ('Leuven', 'Gent', 'Hasselt') means the same as three conditions joined with OR, but it's shorter.
Each value in the list gets its own quotes, separated by commas.
SELECT name, town FROM members WHERE town IN ('Leuven', 'Gent', 'Hasselt')| name | town |
|---|---|
| Ine Claes | Leuven |
| Marc Dierckx | Hasselt |
| Leila Amrani | Gent |
| Hanne De Smet | Leuven |
| Lucas Goossens | Gent |
| Eva Michiels | Hasselt |
| Nora Willems | Leuven |
NOT IN and numbers
NOT IN does the opposite: everything except the values in the list. Numbers go in the list without quotes.
This query shows the rentals of every film except films 1, 22 and 23.
SELECT * FROM rentals WHERE film_id NOT IN (1, 22, 23)| id | film_id | member_id | rented_on | returned_on | fine |
|---|---|---|---|---|---|
| 2 | 6 | 7 | 2024-10-01 | 2024-10-02 | 0 |
| 3 | 19 | 1 | 2024-10-02 | 2024-10-08 | 4.5 |
| 5 | 7 | 9 | 2024-10-03 | 2024-10-05 | 0 |
| 6 | 12 | 12 | 2024-10-04 | 2024-10-12 | 6 |
| 7 | 10 | 6 | 2024-10-05 | 2024-10-06 | 0 |
| 8 | 21 | 15 | 2024-10-05 | 2024-10-07 | 0 |
| 9 | 3 | 5 | 2024-10-06 | 2024-10-07 | 0 |
| 11 | 17 | 10 | 2024-10-08 | 2024-10-09 | 0 |
Try it yourself
Vic is filling a shop window with films from 1994, 2001 and 2023. Show title and year.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson