WHERE Clause

WHERE Clause

A WHERE clause is a per-row boolean test the engine evaluates against each candidate row as it scans the table, keeping only the rows for which the test returns exactly TRUE — rows that return FALSE or UNKNOWN are thrown away. It runs after FROM produces the rows but before SELECT picks the columns, which is why the predicate can reference columns you never put in the output and cannot reference output aliases that don't exist yet.

Worked example: trace the predicate row by row

Take a Products table and ask for in-stock electronics under $500. The engine walks every row, computes the predicate, and admits only the rows where the combined result is TRUE:

SELECT name, price
FROM Products
WHERE category = 'Electronics' AND price < 500;

Evaluating the predicate category = 'Electronics' AND price < 500 against each row:

Only row 1 survives. Note row 4: its price is unknown, so price < 500 is neither TRUE nor FALSE but UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN — so the Webcam is silently dropped even though it is an Electronics item. The engine never told you it had a row it couldn't decide on.

Why the naive NULL filter is wrong

A common attempt to also catch the unknown-price rows is to write price != 500 or price <> 500 and assume NULLs come along. They don't. Any comparison operator (=, !=, <, >) applied to NULL yields UNKNOWN, never TRUE, so the row is excluded by both price < 500 and price >= 500. To test for absence you must use the dedicated operator:

-- naive: silently misses the NULL-price row
WHERE category = 'Electronics' AND price < 500

-- explicit: include unknown prices on purpose
WHERE category = 'Electronics' AND (price < 500 OR price IS NULL)

IS NULL / IS NOT NULL are the only operators that return TRUE/FALSE against a NULL; everything else returns UNKNOWN, and WHERE keeps only TRUE.

Pitfalls

• NULLs vanish without warning. As above, any predicate that touches a NULL evaluates to UNKNOWN and the row is dropped. A filter you think is exhaustive (status = 'active' plus status != 'active') will still lose every row whose status is NULL.
• You can't reference SELECT aliases in WHERE. Because WHERE runs before projection, SELECT price * 0.9 AS sale_price ... WHERE sale_price < 100 errors with "unknown column sale_price". Repeat the expression (WHERE price * 0.9 < 100) or wrap the query in a subquery / CTE.
• WHERE vs HAVING. WHERE filters individual rows before grouping; it cannot use aggregates like COUNT(*) or SUM(x). Conditions on aggregates belong in HAVING, which runs after GROUP BY.
• Wrapping the column in a function on the left side. WHERE UPPER(name) = 'MOUSE' or WHERE DATE(created_at) = '2026-06-29' is logically correct but forces the engine to compute the function for every row. The result is right; the cost can be large on big tables. Prefer comparing the raw column to a transformed literal (e.g. a range on created_at) when you can.

Takeaways

• WHERE is a per-row boolean filter that runs after FROM and before SELECT — so it sees raw columns but not output aliases.
• SQL uses three-valued logic: a row is kept only when the predicate is TRUE; FALSE and UNKNOWN are both rejected.
• Comparisons against NULL produce UNKNOWN, so use IS NULL / IS NOT NULL to test for missing values on purpose.
• Aggregate conditions go in HAVING, not WHERE; and wrapping a column in a function still works but can be expensive.

Re-authored and deepened for this guide. Synthesized from the SQL standard's three-valued logic (ISO/IEC 9075), the PostgreSQL documentation on the SELECT statement and comparison operators, and the MySQL Reference Manual chapters on WHERE clause optimization and working with NULL values. The original page (single category='Electronics' example, mislabeled java code header, and figures with alt='Image') was replaced with a row-by-row predicate trace, a pipeline diagram, and the NULL/alias/HAVING failure modes a working engineer hits.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — WHERE Clause

Why this exists / the decision it encodes

WHERE exists to admit only rows whose predicate is exactly TRUE under three-valued logic. It runs after FROM and before SELECT/GROUP — so it sees base columns, not SELECT aliases, and cannot use aggregates (those belong in HAVING). The decision is which rows enter the pipeline at all.

Worked example with numbers or traced SQL/FD

Products: (1 Mouse Elec 25)(2 Monitor Elec 700)(3 Desk Furn 300)(4 Webcam Elec NULL)
WHERE category='Electronics' AND price < 500
Row1: T AND T = T → keep
Row2: T AND F = F → drop
Row3: F AND T = F → drop
Row4: T AND UNKNOWN = UNKNOWN → drop (NULL price)
Fix if unknowns wanted: ... AND (price < 500 OR price IS NULL)
Alias trap: WHERE sale_price < 100 fails if sale_price is SELECT alias — repeat expr or CTE.

When NOT / named alternative

Do not use WHERE for aggregate filters — use HAVING. Do not filter outer-join preserved columns in WHERE if you need NULL-padded orphans (use ON). Prefer sargable predicates: bare column vs constant range, not function(column).

Failure mode / ops fingerprint / interview trap

Trap: status='active' OR status!='active' still loses NULL statuses. Ops: reports silently undercount rows with NULL measures because UNKNOWN drops them. Interview: "NULL = NULL is false" — actually comparison yields UNKNOWN; only IS NULL tests absence.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: every WHERE shape is also an access-path decision (sargability). Three-valued logic is correctness, not style.

Hostile-panel drills (with model answers)

Q1. What three outcomes can a WHERE predicate have, and which keep the row?
Model answer: TRUE, FALSE, UNKNOWN. Only TRUE keeps the row; FALSE and UNKNOWN both discard.

Q2. Why can't you reference SELECT aliases in WHERE?
Model answer: Logical processing order: FROM → WHERE → GROUP → HAVING → SELECT → ORDER. Aliases are created at SELECT, after WHERE has already run.

Q3. Rewrite DATE(created_at)='2026-06-29' to a sargable range.
Model answer: WHERE created_at >= '2026-06-29' AND created_at < '2026-06-30' so the index on created_at can seek a range of raw values.