Filtering with WHERE

Comparison operators, BETWEEN, IN and LIKE.

What you'll be able to do

Filter rows with comparison operators
Use BETWEEN, IN and LIKE correctly
Avoid the leading-wildcard performance trap

Comparison operators

SELECT * FROM orders
WHERE total > 100;

The operators are =, >, <, >=, <=, and != (also written <>). Strings are compared with single quotes: WHERE city = 'Houston'.

String comparison is case-sensitive in PostgreSQL, usually case-insensitive in MySQL and SQL Server — a real source of confusion when moving between them. WHERE UPPER(city) = 'HOUSTON' is portable, but prevents an index seek.

BETWEEN

WHERE total BETWEEN 100 AND 500

BETWEEN is inclusive on both ends — equivalent to total >= 100 AND total <= 500.

With dates that inclusivity is a trap. BETWEEN '2024-01-01' AND '2024-01-31' misses anything timestamped later on the 31st. Use a half-open range instead:

WHERE order_date >= '2024-01-01'
  AND order_date <  '2024-02-01'

IN

WHERE city IN ('Houston', 'Dallas', 'Austin')

Shorthand for a chain of OR comparisons. NOT IN negates it — but be careful, since a NULL anywhere in the list makes NOT IN return no rows at all.

LIKE and wildcards

WHERE name LIKE 'Jo%'    -- starts with Jo
WHERE name LIKE '%son'   -- ends with son
WHERE name LIKE '%ann%'  -- contains ann
WHERE code LIKE 'A_2'    -- A, any one character, 2

% matches any number of characters, _ matches exactly one.

A leading wildcard prevents an index seek, forcing a full scan. LIKE 'Jo%' can use an index; LIKE '%son' cannot. On a large table that difference is dramatic.

Key points

BETWEEN is inclusive — use a half-open range for dates
A leading % in LIKE prevents index use
NOT IN returns nothing if the list contains a NULL

Check yourself