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

Three-Valued Logic: TRUE, FALSE, and UNKNOWN

Standard SQL's boolean logic isn't two-valued like most programming languages — it's three-valued: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition is TRUE — UNKNOWN rows are filtered out exactly like FALSE ones, silently.

SELECT * FROM Employees WHERE salary > 40000;
-- Dave is excluded — NULL > 40000 is UNKNOWN, not TRUE, so WHERE drops the row

SELECT * FROM Employees WHERE NOT (salary > 40000);
-- Dave is STILL excluded — NOT UNKNOWN is also UNKNOWN, not TRUE

That second query trips people up the most: it feels like NOT (condition) should be the exact complement of condition, catching everyone the first query missed. It isn't, whenever NULL is involved — UNKNOWN negated is still UNKNOWN, so Dave falls through both queries. If you need "everyone above the threshold, or with an unknown salary," you have to ask for it explicitly:

SELECT * FROM Employees WHERE salary > 40000 OR salary IS NULL;

AND/OR combine with UNKNOWN in ways worth internalizing, since they're not always intuitive:

The pattern: FALSE "wins" an AND (because no matter what the unknown value turns out to be, the whole thing can't be true), and TRUE "wins" an OR for the same reason in reverse. Only when neither side can force the outcome does the result stay UNKNOWN.