SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 6 of 18 · ~3 min

Three-Valued Logic: TRUE, FALSE, and UNKNOWN

Every general-purpose language you've probably written boolean logic in gives you exactly two outcomes to work with. SQL secretly gives you three: TRUE, FALSE, and a third state, UNKNOWN, that shows up the instant a comparison touches a NULL. The part that actually changes how you write queries is what WHERE does with that third state — it keeps a row only when the condition lands on TRUE, full stop. UNKNOWN doesn't get some special partial treatment. It's discarded right alongside every row that landed on outright FALSE, and the output gives you no way to tell which of the two happened to any given missing row.

Go back to the early-review filter and watch what happens with the threshold flipped around:

SELECT name FROM Employees WHERE salary < 52000;
-- Alice only — Dave is excluded. salary < 52000 is UNKNOWN for him, not TRUE, so WHERE drops the row

Fine so far — that's the same shape as the last chapter. Now try to write "everyone who does not clear the threshold," using NOT, the way you would in almost any other language:

SELECT name FROM Employees WHERE NOT (salary >= 52000);
-- STILL just Alice — Dave is still excluded, even though this query looks like the exact opposite of one that included him

This is the one that trips people up, every time, until they've been burned by it once. Everywhere else you've written code, wrapping a condition in NOT flips it — whatever the first query missed, the second one should catch, and vice versa. That intuition quietly assumes there are only two outcomes to flip between. Feed NOT an UNKNOWN, though, and there's no TRUE waiting on the other side of it — you get UNKNOWN back, unchanged. Dave doesn't pass the first filter and he doesn't pass its negation either; both queries independently decide there's nothing to say about him, and neither one is wrong. Getting "below the threshold, or we genuinely don't know yet" requires spelling out both cases yourself — no amount of rearranging <, >=, and NOT will ever synthesize it automatically:

SELECT name FROM Employees WHERE salary < 52000 OR salary IS NULL;
-- Alice and Dave — this is the query that actually means "flag for review or we don't have a number yet"

How AND and OR combine with UNKNOWN is worth internalizing properly, because it isn't always the guess you'd make:

Both rows boil down to the same underlying rule, read from opposite ends: an AND chain can't escape a single FALSE link, so once one side is FALSE, no possible value on the other side drags the result back up to TRUE — FALSE decides it outright. Flip to OR and it's TRUE that short-circuits everything the same way, for the identical reason mirrored. UNKNOWN only shows up as the final verdict in the gap between those two cases — when neither TRUE nor FALSE is sitting on either side strongly enough to settle things alone.