PostgreSQL In and Between

The IN operator

IN checks whether a column's value matches any value in a given list. It is a shorthand for writing a long chain of OR conditions, and it is both more readable and typically faster for PostgreSQL to plan.

SELECT *
FROM orders
WHERE status IN ('pending', 'shipped', 'delivered');

You can negate the check with NOT IN to exclude a list of values instead of including them.

SELECT *
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

The BETWEEN operator

BETWEEN tests whether a value falls within an inclusive range, meaning both the lower and upper bounds are included in the match. It works with numbers, dates, and even text.

SELECT name, price
FROM products
WHERE price BETWEEN 20 AND 50;

• IN(...) checks membership in a fixed list of values
• NOT IN(...) excludes rows matching any value in the list
• BETWEEN low AND high is inclusive on both ends
• NOT BETWEEN finds values outside the given range
• IN can also be used with the result of a subquery

Combining with subqueries

IN becomes especially powerful when the list comes from another query instead of a hardcoded set of literals.

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);