IN & BETWEEN

Two shortcuts make common filters shorter and clearer. IN checks a value against a list, replacing chains of OR. BETWEEN checks whether a value falls within a range, replacing a pair of comparisons.

IN: matching a list

SELECT name, country
FROM customers
WHERE country IN ('Spain', 'France', 'Germany');

This is equivalent to country = 'Spain' OR country = 'France' OR country = 'Germany', but easier to read and extend.

NOT IN

SELECT id, status
FROM orders
WHERE status NOT IN ('delivered', 'cancelled');

IN with a subquery

SELECT name
FROM products
WHERE id IN (SELECT product_id FROM order_items WHERE quantity >= 2);

The list can come from another query: here, products that were ever bought two or more at a time. Subqueries get a full treatment in lesson 29.

The NOT IN and NULL trap

SELECT name FROM customers
WHERE city NOT IN ('Lisbon', NULL);

No rows at all! city NOT IN ('Lisbon', NULL) means city <> 'Lisbon' AND city <> NULL, and any comparison with NULL is unknown — so the whole condition is never true. This bites most often when the list comes from a subquery that contains a NULL. Use NOT EXISTS (lesson 30) or filter out the NULLs.

BETWEEN: ranges

SELECT name, price
FROM products
WHERE price BETWEEN 30 AND 50
ORDER BY price;

BETWEEN a AND b is inclusive: it means >= a AND <= b. The smaller value must come first; BETWEEN 50 AND 30 matches nothing.

BETWEEN with dates

SELECT id, order_date
FROM orders
WHERE order_date BETWEEN '2025-12-01' AND '2025-12-31';

This works here because the dates have no time part. With timestamps, '2025-12-31' means midnight at the start of the 31st, so anything later that day is missed. A half-open range is safer and works for both:

SELECT id, order_date
FROM orders
WHERE order_date >= '2025-12-01'
  AND order_date < '2026-01-01';

NOT BETWEEN and text ranges

SELECT first_name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 90000
ORDER BY salary;

SELECT name
FROM customers
WHERE name BETWEEN 'A' AND 'D'
ORDER BY name;

Text ranges compare alphabetically. Note that 'David Müller' is excluded, because it sorts after plain 'D'. To include all names starting with A–D, use name < 'E'.

Try it yourself

Find employees in Sales or Marketing (departments 1 and 3) hired between 2018 and 2021 inclusive.