WHERE: Filtering Rows

~12 min · queries, filter

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The search box of SQL

WHERE filters rows before any grouping or aggregation. Every condition you put in WHERE eliminates rows that the rest of the query never sees. Cheaper queries, smaller results, fewer surprises.

The operators

Equality / comparison: =, <>, <, <=, >, >=.
Boolean combination: AND, OR, NOT — use parentheses for non-trivial logic.
Set membership: IN (...), NOT IN (...).
Range: BETWEEN x AND y (inclusive on both ends).
Pattern: LIKE (case-sensitive), ILIKE (case-insensitive, PostgreSQL).
NULL: IS NULL, IS NOT NULL, IS DISTINCT FROM.

Filter early, filter cheaply

Push every filter you can into WHERE — it shrinks the data the rest of the query has to process. Filters that can use an index ("indexable" predicates) are dramatically cheaper than ones that require a full scan.

Code

Common WHERE shapes·sql

SELECT * FROM users WHERE role = 'admin' AND active = TRUE;

SELECT * FROM products
WHERE category IN ('electronics', 'books')
  AND price BETWEEN 10 AND 100;

SELECT * FROM articles
WHERE title ILIKE '%postgres%'
  AND published_at >= now() - INTERVAL '30 days';

NULL-aware filters·sql

-- WRONG: never matches
SELECT * FROM users WHERE deleted_at = NULL;

-- RIGHT
SELECT * FROM users WHERE deleted_at IS NULL;

-- NULL-safe: IS DISTINCT FROM treats NULL like any other value
SELECT * FROM users WHERE role IS DISTINCT FROM 'admin';

External links

PostgreSQL WHERE / Operators

Exercise

Write WHERE clauses for each: (a) users created in the last 7 days, (b) products priced between $50 and $100 in either 'A' or 'B' category, (c) emails ending in @gmail.com, (d) rows where discount is NULL or 0.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousSELECT: Asking the Database a Question Next →ORDER BY, LIMIT, and Pagination

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.