4 WHERE

WHERE is how you filter rows. The clause sits between FROM and ORDER BY, and PostgreSQL keeps only rows for which the condition evaluates to true. Anything that evaluates to false or NULL is dropped — this three-valued logic is one of SQL’s quirks and the cause of many beginner bugs. Get comfortable with it early.

What you’ll learn

• Filtering with comparison operators (=, <>, <, >, <=, >=)
• Pattern matching with LIKE (and ILIKE for case-insensitive)
• Combining conditions with AND, OR, NOT
• Membership and ranges: IN, BETWEEN
• Why = NULL does not work and what to use instead

Example

-- Equality and ordering
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 3;

-- Pattern matching: % matches any sequence, _ matches one character
SELECT tablename
FROM pg_tables
WHERE tablename LIKE 'pg_a%'
ORDER BY tablename
LIMIT 5;

-- Case-insensitive: ILIKE (PostgreSQL extension)
SELECT tablename FROM pg_tables WHERE tablename ILIKE 'PG_Class' LIMIT 1;

-- Multiple conditions
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
  AND tablename LIKE 'pg_%'
  AND tablename NOT LIKE 'pg_stat_%'
ORDER BY tablename
LIMIT 5;

-- IN: membership test
SELECT tablename
FROM pg_tables
WHERE tablename IN ('pg_class', 'pg_database', 'pg_namespace')
ORDER BY tablename;

-- BETWEEN: inclusive range
SELECT n FROM generate_series(1, 10) AS n WHERE n BETWEEN 3 AND 7;

LIKE is the SQL standard pattern operator: % matches any string of characters (including empty), _ matches exactly one character. ILIKE is a PostgreSQL extension for case-insensitive matching. Index use depends on the pattern, collation, and operator class. Alphabetic prefixes generally need a suitable expression or trigram index; a B-tree can support some patterns beginning with characters unaffected by case. For more powerful matching, PostgreSQL also supports ~ (regex) and ~* (case-insensitive regex), as well as full-text search (covered later).

AND binds tighter than OR, so A AND B OR C means (A AND B) OR C. When in doubt, add parentheses — your future self reading the query will thank you. IN (...) is shorthand for = ANY (ARRAY[...]) and is equivalent to a chain of ORs.

Run the example

To run:

$ psql -f source/where.sql postgres
 schemaname |  tablename
------------+--------------
 pg_catalog | pg_aggregate
 pg_catalog | pg_am
 pg_catalog | pg_amop
(3 rows)
 ...

Common pitfalls

• = NULL and <> NULL are never true (they are NULL). Use IS NULL and IS NOT NULL. This trips up engineers from every other language.
• NOT IN (subquery) becomes mysterious if the subquery contains a NULL — nonmatching values yield NULL while matching values yield false, so the filter keeps no rows. Prefer NOT EXISTS for nullable subqueries.
• LIKE 'abc' does not match 'ABC'. Use ILIKE or apply LOWER() to both sides — but LOWER(col) = 'abc' defeats a plain index on col (add an expression index on LOWER(col) if you need it often).
• Be careful with IN ('a', 'b', NULL) — the NULL in the list contributes nothing useful and silently makes NOT IN behave unexpectedly.

Downloads

SQL file: where.sql · All lesson files · Setup guide