WHERE in depth

Lesson 01 used WHERE col = value and stopped there. The real power of WHERE is the predicate vocabulary on the right-hand side. This lesson is a tour of the operators you'll reach for every day.

The seed loaded a users table similar to before, plus a nullable country column and a few extra people — sixteen rows in total.

Comparisons beyond =

The usual suspects work how you'd expect: <, <=, >, >=, <> (or !=).

SELECT full_name, signed_up_at
FROM users
WHERE signed_up_at >= '2024-06-01';

<> and != are interchangeable in Postgres, but the SQL standard spells it <>. Pick one and stay consistent.

Combine predicates with AND / OR

AND binds tighter than OR, just like * binds tighter than + in arithmetic. When in doubt, parenthesize.

SELECT full_name, country, is_active
FROM users
WHERE is_active = true
AND (country = 'US' OR country = 'UK');

Without those parentheses, the query reads as is_active = true AND country = 'US' or country = 'UK' — which would happily include inactive UK users. Parentheses are documentation as much as logic.

IN is a friendlier OR

When you'd be OR-ing the same column against a list of values, IN is cleaner.

SELECT full_name, country
FROM users
WHERE country IN ('US', 'UK', 'NL');

NOT IN is the inverse — but be careful with NULLs in the list, you'll see why in a moment.

BETWEEN for ranges

x BETWEEN a AND b is shorthand for x >= a AND x <= b — both endpoints are inclusive.

SELECT full_name, signed_up_at
FROM users
WHERE signed_up_at BETWEEN '2024-04-01' AND '2024-05-31';

For dates and timestamps this is usually what you want. For floats, be aware the upper bound is inclusive — sometimes you want >= a AND < b instead, especially if b represents "the next bucket".

LIKE for pattern matching

% matches zero or more characters; _ matches exactly one. Patterns are case-sensitive — use ILIKE for case-insensitive.

SELECT full_name, email
FROM users
WHERE full_name LIKE '%Hopper%'
OR email ILIKE 'a%@example.com';

Patterns starting with % can't use a regular B-tree index — if you need that and the table is big, look at trigram (pg_trgm) indexes later.

IS NULL is not = NULL

This is the single biggest gotcha for SQL beginners. NULL represents unknown, and unknown = unknown is itself unknown — not true. So country = NULL matches nothing, ever. You have to use IS NULL and IS NOT NULL.

SELECT full_name, country
FROM users
WHERE country IS NULL;

Same trap with NOT IN: if the list contains a NULL, the whole predicate becomes unknown and you get zero rows back. Sanitize your lists or prefer NOT EXISTS for that pattern.

Putting it together

A realistic predicate is usually a small mix of these.

SELECT full_name, country, signed_up_at
FROM users
WHERE is_active = true
AND country IS NOT NULL
AND signed_up_at >= '2024-04-01'
AND email NOT LIKE '%@spam.example.com'
ORDER BY signed_up_at;

What you learned

• Comparisons (<, >=, <>) and how they read in SQL.
• AND/OR precedence — parenthesize when mixing.
• IN as readable shorthand for chained ORs.
• BETWEEN is inclusive on both ends.
• LIKE (case-sensitive) vs ILIKE; % and _ wildcards.
• IS NULL / IS NOT NULL — never = NULL.

Up next: sorting results predictably and paging through them without falling into the OFFSET trap.