How SQL WHERE works: true, false, unknown and NULL

The idea

WHERE is a bouncer. For every row of the table it asks a yes-or-no question, built from your conditions, and only the rows that get a definite yes come through. The row is not changed, not moved, not compared to its neighbours. It is tested alone and it is either kept or left behind.

That sounds like two outcomes, but SQL has three. A test can be true, false or unknown, and unknown shows up as soon as a NULL is involved. WHERE keeps only true. Almost every filtering bug you will meet comes from forgetting the third answer or from mixing up the order in which AND and OR combine.

Figure 1

WHERE tests each row on its own, and AND needs both tests to pass

orders
order_id	channel	status
1003	marketplace	completed
1004	marketplace	completed
1005	web	completed
1006	marketplace	cancelled
1007	marketplace	completed
1008	web	completed

Result
order_id	channel	status
1003	marketplace	completed
1004	marketplace	completed
1007	marketplace	completed

Step 1 of 4 Six rows of orders. The question: WHERE channel = 'marketplace' AND status = 'completed'. The database asks it of each row separately, as if the others did not exist.

Six rows of orders. The question: WHERE channel = 'marketplace' AND status = 'completed'. The database asks it of each row separately, as if the others did not exist.
Test one: channel = 'marketplace'. Rows 1005 and 1008 are on the web, so they fail. Four rows pass.
Test two: status = 'completed'. Row 1006 passed the first test but was cancelled. AND needs both answers to be yes, so it fails.
Three rows are left. Kept rows are copied unchanged into the result: WHERE only decides who stays, it never edits a value.

Why it exists

Harbor & Pine has about 12,000 orders and nobody reads all of them. They need refunds from last month, business customers in Texas, products missing a cost. WHERE is how you cut the table down to the rows the question is about, before you count, sum or sort anything. Filtering early also matters for speed: every later step has fewer rows to work on.

Without it you would export everything and filter by eye in a spreadsheet, and the answer would change the moment the data did. A WHERE clause is the question written down, so you can run it again next week and get the same kind of answer.

Think of a WHERE clause as a sentence you could read aloud to a colleague: "orders from the marketplace that were completed". If you can't say it in plain words, the condition is probably not what you meant. That habit catches most mistakes before you run anything.

How it works under the hood

Each row goes through the condition independently:

The database takes a row and evaluates each comparison with that row's values.
It combines the results with AND and OR into one answer: true, false or unknown.
Only true keeps the row. False and unknown both discard it.

That last rule is the whole NULL story. A NULL means "no value known", so unit_cost = 503.50 is unknown when the cost is NULL, and so is unit_cost <> 503.50. The row fails both tests.

Figure 2

A comparison with NULL is unknown, and WHERE keeps only true

products
product_id	name	unit_cost
101	Vale Velvet Loveseat	NULL
102	Alder Oak Coffee Table	503.50
103	Orchard Rattan Dining Chair	429.87

WHERE unit_cost IS NULL
product_id	name
101	Vale Velvet Loveseat

Step 1 of 4 Which products have no recorded cost? Product 101 does: its unit_cost is NULL, meaning unknown.

Which products have no recorded cost? Product 101 does: its unit_cost is NULL, meaning unknown.
WHERE unit_cost = NULL asks: is an unknown equal to unknown? Nobody can say. The answer is not true and not false but unknown, and WHERE keeps only true.
It is unknown for 101, and for 102 and 103 it is false. Not one row is true, so the query returns nothing, without any error.
IS NULL is a different test. It answers a plain yes or no: is this value missing? Row 101 is a yes, and it is kept.

The fix is a dedicated test: IS NULL and IS NOT NULL ask "is the value missing?" and always answer yes or no. The same logic hides a second trap. WHERE referral_source <> 'search' quietly drops every customer with no referral source, since for them the comparison is unknown. If you want those customers too, say so: OR referral_source IS NULL.

How unknown combines

Three-valued logic has a few rules worth knowing. unknown AND false is false, because one false is enough to fail. unknown AND true is unknown. unknown OR true is true, because one true is enough to pass. unknown OR false is unknown. And NOT unknown stays unknown, which is why NOT (unit_cost = 503.50) still loses the NULL rows. You rarely compute these by hand. You just need to remember that a NULL can only be rescued by something that is true.

AND binds tighter than OR

Like multiplication before addition, AND is evaluated before OR. This filter

WHERE channel = 'marketplace' OR status = 'completed' AND channel = 'web'

is read as channel = 'marketplace' OR (status = 'completed' AND channel = 'web'). When the meaning you wanted was different, the result is wrong, and no error warns you. Use parentheses whenever a condition mixes the two. IN ('web', 'app') is a tidy way to write several ORs on the same column.

Dates on a timestamp

order_date is a timestamp, not a plain date. WHERE order_date <= '2024-03-31' means "up to midnight at the start of March 31", so every order placed that day is lost. Write a half-open range instead: order_date >= '2024-03-01' AND order_date < '2024-04-01'. It includes all of March and works for any month, whatever its length.

Patterns

LIKE '%@gmail.com' matches text by pattern: % stands for any run of characters and _ for exactly one. Plain LIKE is case-sensitive in most databases, so lowercase the column first or use ILIKE where it exists. A pattern with a leading % has to read every value, so keep it for questions that need it.

Where WHERE sits

WHERE runs right after FROM, on the raw rows. That has two practical effects. It cannot see the aliases you define in SELECT, because those do not exist yet. And it cannot filter on a total such as COUNT(*) > 10, because no totals exist yet either. Conditions on totals belong in HAVING, which you meet in chapter 4.

Common misconceptions

"WHERE column = NULL finds the missing values"

It returns nothing, with no error, which is why it hurts. The comparison is unknown for every row. Use IS NULL. If a filter that should match something returns zero rows, check whether NULL is involved before you check anything else.

"NOT equal keeps everything that isn't equal"

It keeps everything that is known to be different. A row with a NULL is neither equal nor not equal, so it disappears from both = 'x' and <> 'x'. The two results do not add up to the whole table. Count the rows before and after, and look for the gap.

"A filter can't change the values in my rows"

True, and a handy rule: WHERE keeps or drops whole rows, it never edits them. If a value looks wrong in the output, the problem is in SELECT or in the data, not in the filter. A filter can, however, change which rows an aggregate sees, so a total can change when you add a condition.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

Orders table: 1006 is a cancelled marketplace order, 1008 is a completed web order. You run WHERE channel = 'marketplace' OR status = 'completed' AND channel = 'web'. Which of the two rows is in the result? Neither, because the two halves of the condition contradict each other Only 1008, because OR keeps just one side Both, because OR applies to the whole list of conditions at once Both: 1006 passes the first test, and 1008 passes status = 'completed' AND channel = 'web'