PostgreSQL Filtering Operators

These operators make WHERE clauses far more expressive than plain =/>/< comparisons.

BETWEEN — Range Check

Inclusive on both ends.

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 70000;
-- equivalent to: salary >= 40000 AND salary <= 70000

SELECT * FROM employees WHERE hired_at BETWEEN '2024-01-01' AND '2024-12-31';

IN — Match Any Value in a List

SELECT * FROM employees WHERE department IN ('Sales', 'HR', 'Support');
-- equivalent to: department = 'Sales' OR department = 'HR' OR department = 'Support'

SELECT * FROM employees WHERE department NOT IN ('Sales', 'HR');

IN also accepts a subquery — covered in the Subqueries lesson.

LIKE / ILIKE — Pattern Matching

% matches any sequence of characters, _ matches exactly one character. LIKE is case-sensitive; ILIKE is case-insensitive.

SELECT * FROM employees WHERE name LIKE 'Ra%';     -- starts with "Ra"
SELECT * FROM employees WHERE name LIKE '%Gupta';  -- ends with "Gupta"
SELECT * FROM employees WHERE name LIKE '%upt%';   -- contains "upt" anywhere
SELECT * FROM employees WHERE name ILIKE '%gupta%'; -- case-insensitive

SELECT * FROM employees WHERE email LIKE '_ahul@gmail.com';
-- matches any single character followed by "ahul@gmail.com"

IS NULL / IS NOT NULL

NULL represents "no value" — you can never compare it with =, only with IS.

SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- this NEVER matches, even for rows where phone actually is NULL:
SELECT * FROM employees WHERE phone = NULL;

Common Errors & Troubleshooting

• WHERE phone = NULL never matching anything — comparisons against NULL always evaluate to unknown, never true; use IS NULL instead (see the NULL Semantics lesson for the full explanation).
• LIKE pattern not matching because of a literal % or _ in the data — if you need to match those characters literally, escape them: LIKE '50\%' ESCAPE '\'.
• BETWEEN with a date range missing the last day's records — BETWEEN '2024-01-01' AND '2024-01-31' on a TIMESTAMP column only includes up to midnight of the 31st, missing any time later that day; use >= '2024-01-01' AND < '2024-02-01' for a full-day-inclusive range.

Key Takeaways

• BETWEEN is inclusive on both bounds; IN checks membership in an explicit list.
• Use ILIKE instead of LIKE whenever the search should ignore case.
• NULL can only be tested with IS NULL / IS NOT NULL — = NULL silently matches nothing.