Two short forms replace long chains of OR and AND.
IN: one of a list
sql
SELECT name, country
FROM customers
WHERE country IN ('IT', 'FR', 'DE');IN keeps a row when the value is equal to one of the values in the list. The list goes in parentheses, with commas between the values.
customers
| id | name | country |
|---|---|---|
| 3 | Sofia Rossi | IT |
| 4 | Noah Kim | US |
| 6 | Lucas Silva | BR |
| 7 | Emma Dubois | FR |
| 10 | Hannah Weber | DE |
NOT IN keeps the other rows: the value is different from every value in the list.
| Short form | Long form |
|---|---|
country IN ('CA', 'US') | country = 'CA' OR country = 'US' |
country NOT IN ('CA', 'US') | country <> 'CA' AND country <> 'US' |
BETWEEN: a range
sql
SELECT name, price
FROM products
WHERE price BETWEEN 22 AND 30;BETWEEN a AND b keeps the values from a to b. Both ends are included: the Container Crab Tee at 22.00 stays.
| Short form | Long form |
|---|---|
price BETWEEN 22 AND 30 | price >= 22 AND price <= 30 |
price NOT BETWEEN 22 AND 30 | price < 22 OR price > 30 |
Write the smaller value first. BETWEEN works on numbers, text and dates:
sql
SELECT id, order_date
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';