Ranges with BETWEEN

Ranges such as price >= 15 AND price <= 20 are so common that SQL has a shorter way to write them, BETWEEN … AND …:

SELECT title, price
FROM games
WHERE price BETWEEN 15 AND 20
ORDER BY price, title;

Both ends belong to the range: the games for $15 and for $20 are in the result. price BETWEEN 15 AND 20 means exactly price >= 15 AND price <= 20, it's just shorter and easier to read. The AND in it is part of BETWEEN; it doesn't join two conditions.

BETWEEN works with dates too, since they compare correctly as text. The orders from the summer of 2024, from June 1 to August 31:

SELECT id, ordered_at
FROM orders
WHERE ordered_at BETWEEN '2024-06-01' AND '2024-08-31'
ORDER BY ordered_at;

Order 7 from June 1 is in the result: the first day of the range is included, and so is the last one. (If the dates held a time too, the last day would be left out again, as you saw in the previous exercise: '2024-08-31 18:30' is greater than '2024-08-31'. For such dates, use >= and < with the next day.)

NOT BETWEEN finds the values outside the range. The games that cost less than $10 or more than $50:

SELECT title, price
FROM games
WHERE price NOT BETWEEN 10 AND 50
ORDER BY price;

Typical mistake: the greater value first. price BETWEEN 20 AND 15 isn't an error, but it finds nothing: no price is at least 20 and at most 15 at the same time. The smaller value always goes first, and with dates, the earlier one.