The WHERE clause decides which rows survive. Write a real predicate — comparisons, AND/OR, IN, BETWEEN, LIKE, IS NULL — and this lab parses and evaluates it against every row, showing you exactly which ones matched and why.
Overview
Quick Context
The WHERE clause is a condition tested against each row independently. If it evaluates to TRUE, the row is kept; anything else and it is dropped. That row-at-a-time model is the key mental picture — the database is not filtering "the table", it is asking one question of every row.
The predicate column shows what your condition evaluated to for that row. Only TRUE rows are returned — UNKNOWN is not the same as FALSE, but it is discarded just the same.
The WHERE Clause: Keeping Only What You Asked For
A filter applied to every row, one at a time. Simple — until NULL gets involved.
The Operators You Actually Need
Comparison — = <> < > <= >=. Note SQL uses = for equality, not ==.
Logical — AND, OR, NOT. AND binds tighter than OR, so use parentheses when mixing them.
IN (a, b, c) — shorthand for a chain of ORs.
BETWEEN x AND y — inclusive of both endpoints, which surprises people regularly.
LIKE — pattern matching where % is any run of characters and _ is exactly one.
IS NULL / IS NOT NULL — the only correct way to test for missing values.
The NULL Trap: SQL Has Three Truth Values
NULL does not mean zero or empty string — it means unknown. Comparing anything to an unknown gives an unknown result, so manager = 'Ada Lovelace' is neither TRUE nor FALSE for a row where manager is NULL. It is UNKNOWN, and WHERE only keeps TRUE.
Run the two manager examples in this lab and watch the UNKNOWN counter. The consequence catches everyone eventually: WHERE manager = 'X' and WHERE manager <> 'X' together do not return every row. The NULL rows fall through both. To include them you must say OR manager IS NULL explicitly.
WHERE and Performance
WHERE is also where indexes earn their keep. A condition like salary > 65000 can use an index on salary to skip most of the table. But wrapping the column in a function — WHERE UPPER(name) = 'ADA' — usually defeats the index, because the stored values are no longer what is being compared. Similarly, LIKE 'A%' can use an index while LIKE '%a' cannot, since the leading wildcard gives the engine no prefix to seek on.
Filtering is the cheapest optimisation there is
WHERE decides which rows survive. Every row it removes is a row that never has to be joined, grouped, sorted, sent over the network or rendered in a browser — which is why the fastest queries are usually the ones that filter early and filter hard.
The clause runs after FROM and before GROUP BY, so it sees individual rows before any aggregation exists. That single fact explains its two limitations: you cannot filter on an aggregate here (use HAVING), and you usually cannot refer to a SELECT alias here (it has not been computed yet).
SELECT name, salary
FROM employees
WHERE department = 'Engineering'
AND salary > 50000
AND start_date >= '2024-01-01';
Three conditions, all on individual rows, all resolvable before anything else happens. This query can be answered from an index without reading most of the table — the ideal case.
A worked example on one small table
Six employees, and the results of five different conditions:
Name
Dept
Salary
Manager ID
Alice
Eng
75,000
NULL
Bob
Eng
55,000
1
Carol
Sales
48,000
1
Dan
Sales
62,000
3
Eve
Marketing
NULL
1
Frank
Eng
90,000
3
Condition
Rows returned
salary > 60000
Alice, Dan, Frank
dept = 'Eng' AND salary < 80000
Bob only — Alice and Frank are too well paid
dept IN ('Sales','Marketing')
Carol, Dan, Eve
salary IS NULL
Eve
manager_id IS NULL
Alice
salary > 60000 OR dept = 'Sales'
Alice, Carol, Dan, Frank
Two of those rows are the ones worth staring at. salary > 60000 does not return Eve, whose salary is NULL — and neither does salary <= 60000. A NULL salary satisfies no comparison at all, in either direction, which is the single most surprising thing about SQL filtering.
Three-valued logic, in practice
SQL conditions evaluate to TRUE, FALSE or UNKNOWN, and only rows evaluating to TRUE are returned.
Any comparison involving NULL yields UNKNOWN, because NULL means "no value recorded" rather than "empty" or "zero". Asking whether an unknown salary exceeds 60,000 has no answer, and SQL says so rather than guessing.
Expression
Result
NULL = NULL
UNKNOWN
NULL <> 5
UNKNOWN
NULL IS NULL
TRUE
TRUE AND UNKNOWN
UNKNOWN
TRUE OR UNKNOWN
TRUE
NOT UNKNOWN
UNKNOWN
So IS NULL and IS NOT NULL are not stylistic alternatives to = and <> — they are the only operators that can test for absence.
The trap that follows is NOT IN with a NULL in the list:
SELECT * FROM employees
WHERE manager_id NOT IN (SELECT id FROM managers); -- returns nothing if
-- any id is NULL
If the subquery returns a single NULL, every row evaluates to UNKNOWN and the query returns zero rows — silently, with no error. Use NOT EXISTS instead, which handles NULLs correctly, or filter them out of the subquery explicitly.
Writing conditions an index can use
An index on a column only helps if the column appears in the condition in its raw form. Wrap it in a function and the index is bypassed, because the database has no index on the function's output.
Slow — index unusable
Fast — index usable
WHERE YEAR(order_date) = 2026
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'
WHERE UPPER(email) = 'A@B.COM'
WHERE email = 'a@b.com' with a case-insensitive collation
WHERE price * 1.2 > 100
WHERE price > 83.33
WHERE name LIKE '%smith'
WHERE name LIKE 'smith%'
The last row deserves its own note: a leading wildcard forces a full scan, because a B-tree index is ordered by the start of the string and there is nothing to seek to. Trailing wildcards are fine.
Two other habits that pay. Put the most selective condition first when readability allows — the planner usually reorders anyway, but it costs nothing. And keep the column's data type consistent with the value you compare it to; comparing a text column to a number can force an implicit conversion that disables the index.
If you genuinely need YEAR(order_date) or UPPER(email) in a condition, create an expression index on exactly that expression, and the planner can use it.
The operators, and the one that quietly drops rows
WHERE keeps rows whose condition evaluates to true. Most of it is unsurprising -- and the exception matters enough that it is worth meeting on real data rather than in the abstract.
query.sqlSQLite
-- Each row below is one filter and how many rows it keeps.
-- Change any condition and re-run.
SELECT 'signup >= 2024-01-01' AS filter, count(*) AS kept FROM customers WHERE signup >= '2024-01-01'
UNION ALL
SELECT 'city LIKE L%', count(*) FROM customers WHERE city LIKE 'L%'
UNION ALL
SELECT 'city IN (London,Lagos)', count(*) FROM customers WHERE city IN ('London', 'Lagos')
UNION ALL
SELECT 'city NOT IN (London)', count(*) FROM customers WHERE city NOT IN ('London')
UNION ALL
SELECT 'all customers', count(*) FROM customers
UNION ALL
SELECT 'discount > 0', count(*) FROM orders WHERE discount > 0
UNION ALL
SELECT 'discount <= 0 OR > 0', count(*) FROM orders WHERE discount <= 0 OR discount > 0
UNION ALL
SELECT 'all orders', count(*) FROM orders;
-- Look at the last three rows. Only 2 of the 6 orders carry a discount;
-- the other 4 are NULL. "discount > 0" finds 2, and so does
-- "discount <= 0 OR discount > 0" -- a condition that looks like it must
-- hold for every number. It does not hold for NULL, because NULL is not
-- a number, and WHERE keeps only what is TRUE.
--
-- Same reason "city NOT IN (London)" does not find Dara.
Result
Try it yourself
Run the default predicate. The evaluation column shows TRUE or FALSE for every row — the filter is genuinely parsed and applied, so you can type your own.
Try manager IS NULL, then manager = 'Ada Lovelace'. Watch the UNKNOWN counter appear in the second case.
Test salary BETWEEN 60000 AND 75000 and confirm that a row sitting exactly on an endpoint is included.
Break the syntax on purpose — delete a closing quote. The parser reports the error rather than silently returning nothing, exactly as a database would.
Compare NOT (dept = 'Engineering') OR salary < 65000 with the parentheses removed. Operator precedence changes the answer.
In one line
WHERE asks one yes/no question of every row — except the answer can also be "unknown", and unknown rows are silently dropped. Most WHERE-clause bugs in production are really NULL bugs.
Operators worth knowing properly
BETWEEN a AND b is inclusive at both ends. For dates this is a common source of off-by-one bugs, because BETWEEN '2026-01-01' AND '2026-01-31' excludes anything timestamped later on the 31st. Prefer >= start AND < next_start.
IN (...) is shorthand for a chain of ORs, and works with a subquery as well as a literal list.
LIKE uses % for any run of characters and _ for exactly one. Escape literal percent signs with an ESCAPE clause.
IS DISTINCT FROM compares two values treating NULLs as comparable — NULL IS DISTINCT FROM 5 is TRUE. It is the operator you want when comparing nullable columns, and it is supported by PostgreSQL and several others.
EXISTS tests whether a subquery returns any row at all, and stops at the first one. Usually the right tool for "does a related record exist".
Parentheses matter more than people expect once OR appears. WHERE a = 1 AND b = 2 OR c = 3 binds as (a = 1 AND b = 2) OR c = 3, because AND has higher precedence. If you meant the other grouping, the parentheses are not optional.
Reading a plan when a filter is slow
The reliable way to find out why a filtered query is slow is to ask the database:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';
Three things to look for in the output:
Seq Scan / Full Table Scan on a large table means no index was used. Either none exists, or the condition is written in a form that cannot use it.
A large gap between estimated and actual rows means the planner's statistics are stale. Refreshing them (ANALYZE) often fixes the plan outright.
Filter removed N rows after an index scan means the index narrowed the search but the engine still had to check each row. A composite index covering both columns may remove that step.
For the query above, an index on (customer_id, status) lets the engine seek straight to the matching rows. An index on status alone would be close to useless if most orders are open — an index on a low-selectivity column rarely earns its keep.
Questions people ask
Does the order of AND conditions matter? Not for correctness, and rarely for speed — the planner reorders based on statistics. Write them in the order that reads best.
Why does WHERE column != 'x' exclude NULL rows? Because NULL != 'x' is UNKNOWN, not TRUE. Add OR column IS NULL if you want them.
Is IN slower than OR? No — they usually produce identical plans. IN with a very long literal list can get slow; a temporary table or a VALUES join is faster past a few thousand items.
Can I filter on a window function? Not in WHERE, because window functions are computed after it. Wrap the query in a CTE or subquery and filter outside.
What about WHERE 1=1? A harmless idiom used when building queries programmatically, so every real condition can be appended as AND .... The planner discards it.
Should I use LIKE for search? For prefix matching, yes. For real text search — word matching, ranking, stemming — use the database's full-text search features or a dedicated search engine.
Recap in one screen
WHERE filters individual rows before grouping, so it cannot see aggregates or most aliases.
NULL comparisons return UNKNOWN, and only TRUE rows are returned — use IS NULL.
NOT IN with a NULL in the list returns nothing at all; prefer NOT EXISTS.
Wrapping an indexed column in a function disables the index; rewrite as a range, or index the expression.
BETWEEN is inclusive; for timestamps use >= start AND < next_start.
When it is slow, read EXPLAIN ANALYZE before changing anything.
Recall check
0 of 4
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What is meant by “BETWEEN a AND b” here?
is inclusive at both ends. For dates this is a common source of off-by-one bugs, because BETWEEN '2026-01-01' AND '2026-01-31' excludes anything timestamped later on the 31st. Prefer >= start AND < next_start .
What is meant by “IN (...)” here?
is shorthand for a chain of ORs, and works with a subquery as well as a literal list.
What is meant by “LIKE” here?
uses % for any run of characters and _ for exactly one. Escape literal percent signs with an ESCAPE clause.
What is meant by “IS DISTINCT FROM” here?
compares two values treating NULLs as comparable — NULL IS DISTINCT FROM 5 is TRUE. It is the operator you want when comparing nullable columns, and it is supported by PostgreSQL and several others.
Cheat sheet
Where Clause in SQL
The WHERE clause decides which rows survive. Write a real predicate — comparisons, AND/OR, IN, BETWEEN, LIKE, IS NULL — and this lab parses and evaluates it against every row, showing you exactly which ones matched and why.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.