Learn SQL: BETWEEN
From … to with BETWEEN
Vic is planning a nineties night. "From 1990 up to and including 1999. The ones from 1990 too, mind!" Borders matter, and `BETWEEN` includes them.
BETWEEN includes the borders
year BETWEEN 1990 AND 1999 means year >= 1990 AND year <= 1999. Both borders count.
Look at the two highlighted rows: The Matrix (1999) and Home Alone (1990) sit exactly on the border, so they belong in the list.
SELECT title, year FROM films WHERE year BETWEEN 1990 AND 1999| title | year |
|---|---|
| The Matrix | 1999 |
| Alles moet weg | 1996 |
| Jurassic Park | 1993 |
| Titanic | 1997 |
| Toy Story | 1995 |
| Pulp Fiction | 1994 |
| The Lion King | 1994 |
| Home Alone | 1990 |
Dates too, and NOT BETWEEN
BETWEEN also works for dates stored as text, and with NOT BETWEEN you find everything outside the range. Always put the smallest value first.
This query shows the rentals from the first week of October.
SELECT * FROM rentals WHERE rented_on BETWEEN '2024-10-01' AND '2024-10-07'| id | film_id | member_id | rented_on | returned_on | fine |
|---|---|---|---|---|---|
| 1 | 1 | 3 | 2024-10-01 | 2024-10-03 | 0 |
| 2 | 6 | 7 | 2024-10-01 | 2024-10-02 | 0 |
| 3 | 19 | 1 | 2024-10-02 | 2024-10-08 | 4.5 |
| 4 | 22 | 4 | 2024-10-03 | 2024-10-04 | 0 |
| 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 |
Try it yourself
Which members were born between 1980 and 1995, borders included? Show name and birth 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