WHERE — Filtering Results

~12 min · sql, where, predicates

Level 0Scout

0 XP0/80 lessons0/10 achievements

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

Refining the row set

The WHERE clause is a boolean expression evaluated per row. Rows for which it evaluates true are kept; the rest are dropped.

The operators you'll use 95% of the time:

Comparison: =, != / <>, <, <=, >, >=.
Boolean combinators: AND, OR, NOT, parentheses for grouping.
Set membership: IN (a, b, c), NOT IN (...).
Range: BETWEEN a AND b (inclusive on both ends).
NULL: IS NULL / IS NOT NULL — never = NULL (always false).
Pattern: LIKE 'a%', GLOB, REGEXP (with extension).

Warning: NULL = NULL is NULL, not TRUE. NULL != anything is also NULL. Always use IS NULL / IS NOT NULL. The number of bugs caused by forgetting this is staggering.

Code

WHERE in many shapes·sql

-- Equality + AND
SELECT * FROM users WHERE role = 'admin' AND archived = 0;

-- Range + OR
SELECT * FROM messages WHERE
  created_at >= datetime('now', '-7 days')
  OR pinned = 1;

-- IN with a literal set
SELECT * FROM users WHERE role IN ('admin', 'moderator');

-- IN with a subquery
SELECT * FROM messages WHERE conversation_id IN (
  SELECT id FROM conversations WHERE archived = 0
);

-- NULL handling — IS / IS NOT, never =
SELECT * FROM users WHERE deleted_at IS NULL;

External links

Exercise

On a table with at least 100 rows, write five WHERE predicates that exercise (1) AND/OR/NOT, (2) IN with a literal list, (3) IN with a subquery, (4) BETWEEN, (5) NULL handling. For each, also write the wrong-version that uses = NULL or skips parentheses around an OR — observe what SQLite returns and why.

WHERE — Filtering Results

Refining the row set

Code

External links

Exercise

Progress

Comments 0