IN and BETWEEN

IN tests membership in a list or subquery; BETWEEN tests an inclusive range.

Syntax

expr [NOT] IN (value1, value2, ...)
expr [NOT] IN (SELECT col FROM ...)
expr [NOT] BETWEEN low AND high       -- low <= expr AND expr <= high

x IN (a, b, c) is shorthand for x = a OR x = b OR x = c.
x IN (SELECT ...) checks against the values a one-column subquery returns.
BETWEEN low AND high is inclusive at both ends. low must be the smaller value: BETWEEN 10 AND 1 matches nothing (BETWEEN SYMMETRIC accepts either order).
NULL: x IN (...) is NULL when x is NULL. x NOT IN (1, NULL) is never TRUE — it is FALSE or NULL — so a single NULL in the list (or subquery) empties a NOT IN result. Use NOT EXISTS for subqueries.
On timestamps, BETWEEN '2025-03-01' AND '2025-03-31' stops at midnight on the 31st. Use a half-open range (>= '2025-03-01' AND < '2025-04-01').

List membership starter

SELECT order_id, status
FROM orders
WHERE status IN ('shipped', 'processing')
ORDER BY order_id;

Equivalent to status = 'shipped' OR status = 'processing'.

Inclusive numeric range starter

SELECT product_name, price
FROM products
WHERE price BETWEEN 75.5 AND 145
ORDER BY price;

Both 75.50 and 145.00 are included.

IN with a subquery starter

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'cancelled')
ORDER BY customer_name;

Customers with at least one cancelled order; duplicates in the subquery do not duplicate output rows.