TopolearnThese operators make common filters shorter and clearer than chains of OR and comparisons.
Three operators cover most filters that are not a single comparison.
IN tests membership in a list, replacing a chain of ORs:
WHERE status IN ('pending', 'shipped')NOT IN is the opposite, but treat it with care: if the list contains a NULL, NOT IN matches nothing at all. The NULL lesson explains why.
BETWEEN a AND b is an inclusive range, the same as >= a AND <= b. That inclusiveness bites with timestamps: ordered_at BETWEEN '2026-02-01' AND '2026-02-28' excludes everything after midnight at the start of the 28th. For time ranges use a half-open interval instead:
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'LIKE matches text patterns. % matches any run of characters (including none) and _ matches exactly one character:
name LIKE 'M%'starts with Memail LIKE '%@example.com'ends with that domainname LIKE '_o%'has o as its second letter
In PostgreSQL LIKE is case-sensitive; ILIKE is a PostgreSQL-only case-insensitive version (elsewhere, compare LOWER(name) LIKE 'm%'). To match a literal % or _, escape it with a backslash: LIKE '50\%%'.
Performance note: a pattern with a fixed prefix ('M%') can use an index in the right conditions, but a leading wildcard ('%book') forces the database to examine every row. PostgreSQL's trigram indexes (the pg_trgm extension) exist precisely for that case.
Example
SELECT id, status FROM orders WHERE status IN ('pending', 'cancelled');
-- 102 | pending
-- 104 | cancelled
SELECT name, price FROM products WHERE price BETWEEN 19.50 AND 49.00;
-- Keyboard | 49.00 <- both ends are included
-- Mouse | 19.50
SELECT id FROM orders
WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01';
-- 102
-- 103
SELECT name FROM products WHERE name LIKE 'M%';
-- Mouse
-- Monitor
SELECT name FROM products WHERE name ILIKE '%BOOK'; -- PostgreSQL only
-- EbookRemember
- IN replaces OR chains; NOT IN breaks if the list contains NULL
- BETWEEN includes both ends; use >= and < for time ranges
- LIKE: % = any run of characters, _ = one character; ILIKE is PostgreSQL's case-insensitive form
Watch out for
- Using BETWEEN with a date upper bound on a timestamp column and silently losing the last day's rows.