SQL IS NULL and IS NOT NULL: why NULL is not FALSE, with three-valued truth tables
In SQL, NULL means “unknown”, so col = NULL is never true: test for a missing value with col IS NULL and for a present one with col IS NOT NULL. NULL is not FALSE but a third truth value, and a WHERE clause keeps only the rows whose condition is TRUE.
This guide explains IS NULL and IS NOT NULL, gives SQL's three-valued truth tables for AND, OR and NOT, and goes through the places where NULL quietly changes a query's answer: NOT, NOT IN, COUNT, CHECK constraints and nullable Boolean columns. It ends with how Tangibool's SQL export guards against NULL inputs. One honest limit first: Tangibool's engine works with TRUE and FALSE only, so the three-valued tables below are SQL's rules, stated here, not engine output; the engine checks only the two-valued facts that the guide links to.
IS NULL and IS NOT NULL
IS NULL is true when a value is missing, and IS NOT NULL when it is present:
| Expression | When email is 'a@b.com' | When email is NULL |
|---|---|---|
email IS NULL | FALSE | TRUE |
email IS NOT NULL | TRUE | FALSE |
email = NULL | NULL (unknown) | NULL (unknown) |
email <> NULL | NULL (unknown) | NULL (unknown) |
So the query for customers without an email address is
SELECT * FROM customers WHERE email IS NULL;
and the query for customers with one is the same with IS NOT NULL. The two IS tests always return TRUE or FALSE, never NULL, which is what makes them safe in a WHERE clause.
Why = NULL never works
NULL stands for a value that is missing or unknown. Asking whether an unknown value equals something, even another unknown value, gets the answer “unknown”: NULL = NULL is NULL, not TRUE. A WHERE clause treats that as “not TRUE” and drops the row, so WHERE email = NULL returns nothing, whatever the table holds. Most databases accept the query without an error, which is why the mistake survives.
To compare two columns that may both be NULL and treat two NULLs as equal, use a null-safe comparison: a IS NOT DISTINCT FROM b in PostgreSQL and SQL Server 2022, or a <=> b in MySQL. To replace a NULL with a value for display, use COALESCE(email, 'no email'), which returns its first argument that is not NULL.
SQL's three truth values
A condition in SQL evaluates to TRUE, FALSE or UNKNOWN, and in a Boolean column UNKNOWN is stored as NULL. The rules for the logical operators follow one idea: if the known operands already decide the answer, that is the answer; otherwise the answer is unknown. These are SQL's rules, from the SQL standard, and they are the same in PostgreSQL, MySQL, SQL Server and Oracle.
NOT
| x | NOT x |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | NULL |
AND (FALSE wins)
| AND | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | FALSE |
| NULL | NULL | FALSE | NULL |
OR (TRUE wins)
| OR | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | NULL |
| NULL | TRUE | NULL | NULL |
FALSE AND NULL is FALSE because the AND fails whatever the unknown value is; TRUE OR NULL is TRUE for the same reason. TRUE AND NULL and FALSE OR NULL depend on the unknown value, so they are NULL.
On the cells without NULL, the tables are the ordinary Boolean ones, which the engine computes. Row 0 has both inputs false:
| # | a | b | NOT a | a AND b | a OR b |
|---|---|---|---|---|---|
| 0 | FALSE | FALSE | TRUE | FALSE | FALSE |
| 1 | FALSE | TRUE | TRUE | FALSE | TRUE |
| 2 | TRUE | FALSE | FALSE | FALSE | TRUE |
| 3 | TRUE | TRUE | FALSE | TRUE | TRUE |
Computed by the Tangibool engine
Some laws of two-valued logic survive the third value and some do not. De Morgan's laws hold in SQL too: the engine shows that NOT (a AND b) and NOT a OR NOT b agree on every TRUE and FALSE row, and working through the tables above shows they agree when a or b is NULL as well. The law of the excluded middle does not survive, as the next sections show.
WHERE keeps only TRUE; CHECK rejects only FALSE
Clauses that filter rows treat UNKNOWN differently from each other:
- WHERE, HAVING and the ON of a join keep a row only when the condition is TRUE. FALSE and UNKNOWN both drop it.
- CHECK constraints reject a row only when the condition is FALSE. UNKNOWN passes. So
CHECK (price > 0)accepts a row whose price is NULL; add NOT NULL to the column if a price is required.
The difference is deliberate: a query should return only rows known to match, and a constraint should reject only rows known to break it. It still surprises people in both directions.
Five places where NULL changes the answer
1. NOT does not find the NULLs
WHERE NOT (status = 'closed') returns the rows whose status is something else, but not the rows where status is NULL: for those, status = 'closed' is NULL, and NOT NULL is NULL. To include them, write WHERE status <> 'closed' OR status IS NULL, or WHERE status IS DISTINCT FROM 'closed' where it is available.
2. “x = 5 OR x <> 5” is not always true
In two-valued logic, “p or not p” is true on every row: the truth table generator shows x_is_5 OR NOT x_is_5 is a tautology. In SQL, when x is NULL both comparisons are NULL, NULL OR NULL is NULL, and the row disappears from WHERE x = 5 OR x <> 5. A query that splits a table into “equal to 5” and “not equal to 5” misses every row with a NULL. The worked example and the pitfall NULL is not FALSE use the same formula.
3. NOT IN with a NULL in the list returns nothing
x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, a NOT over an OR of comparisons; in two-valued logic NOT (is_1 OR is_2 OR is_3) is NOT is_1 AND NOT is_2 AND NOT is_3. The last comparison is always NULL, so the AND can never be TRUE: it is FALSE when x is 1 or 2 and NULL otherwise. The query returns no rows at all. The same happens with a subquery: WHERE id NOT IN (SELECT manager_id FROM staff) returns nothing if any manager_id is NULL. Write NOT EXISTS (SELECT 1 FROM staff s WHERE s.manager_id = t.id), or filter the NULLs out of the subquery.
IN is less dangerous: x IN (1, NULL) is TRUE when x is 1, and NULL rather than FALSE otherwise, which a WHERE clause treats the same way.
4. COUNT(*) and COUNT(column) differ
COUNT(*) counts rows; COUNT(email) counts the rows where email is not NULL. AVG, SUM, MIN and MAX also ignore NULLs: the average of 4, NULL and 6 is 5, not 10/3. SUM over no values, or over only NULLs, is NULL, not 0; wrap it in COALESCE(SUM(x), 0) when a zero is wanted. GROUP BY puts all the NULLs in one group, although NULL = NULL is not TRUE.
5. Nullable Boolean columns have three states
A column declared active BOOLEAN without NOT NULL can hold TRUE, FALSE or NULL. Then:
WHERE activereturns the TRUE rows;WHERE NOT activereturns the FALSE rows, and not the NULL ones;WHERE active IS NOT TRUEreturns the FALSE and the NULL rows;WHERE active IS UNKNOWN(oractive IS NULL) returns the NULL rows.
So the rows split three ways, not two. If “unknown” makes no sense for the column, declare it active BOOLEAN NOT NULL DEFAULT FALSE. PostgreSQL and MySQL support IS TRUE, IS NOT TRUE and IS UNKNOWN; SQL Server has no BOOLEAN type and stores flags as BIT, where the same three states are 1, 0 and NULL.
NOT NULL as a constraint
“NOT NULL” also names a column constraint, which is different from the IS NOT NULL test. email VARCHAR(255) NOT NULL in a CREATE TABLE statement makes the database refuse any row without an email. Adding the constraint to an existing column fails while any row still holds NULL, so fill those rows first with an UPDATE. A primary key column is NOT NULL automatically.
| IS NOT NULL | NOT NULL | |
|---|---|---|
| What it is | a test in a query | a rule on a column |
| Where it goes | WHERE, CASE, SELECT, CHECK | CREATE TABLE or ALTER TABLE |
| What it does | returns TRUE or FALSE for each row | stops NULLs from being stored |
Can a Boolean be NULL?
In SQL, yes, unless the column is NOT NULL, and the third state is a common source of bugs. In programming languages it depends on the type. Java's boolean cannot be null but its wrapper Boolean can; C# has bool and the nullable bool?; in Python, None is a separate value that a condition treats as false; in JavaScript, null is likewise falsy in a condition. A value read from a nullable database column needs one of the nullable types, and code should decide explicitly what NULL means rather than letting it slip through as false.
How Tangibool handles NULL
Tangibool's engine is two-valued: every input is TRUE or FALSE, and every check, truth table and verdict assumes that. It does not model NULL. When it exports a rule as SQL, from the code exporter or the app, the file targets PostgreSQL 16 or later with Boolean inputs, and it keeps the two worlds apart:
- each expression fragment states a precondition: its input columns must be non-NULL Booleans;
- a guard template gives every row a status,
'invalid_input'when any input is NULL,'out_of_scope'when the rule's assumptions fail, and'ok'otherwise, and fills the output columns only for'ok'rows; - it never wraps a fragment in
COALESCE(…, FALSE), because treating unknown as FALSE would be a choice the rule never made.
Operators are translated without changing their meaning on TRUE and FALSE: implication p → q is written ((NOT p) OR q), which is equivalent, and XOR on Booleans is <>, since NOT (a ↔ b) is a XOR b. The SQL page shows a full export with its guard.
Common mistakes
- Writing = NULL or <> NULL. Both are always unknown. Use IS NULL and IS NOT NULL.
- Expecting NOT to include the NULLs. NOT of an unknown is unknown, so those rows drop out.
- Using NOT IN on a list or subquery that can contain NULL. It returns no rows. Use NOT EXISTS.
- Counting a column to count rows. COUNT(column) skips NULLs; COUNT(*) does not.
- Treating NULL as FALSE in a nullable Boolean. Decide what unknown means, and write it out with IS TRUE, IS NOT TRUE or COALESCE, or forbid it with NOT NULL.
Frequently asked questions
What does IS NOT NULL do in SQL?
It returns TRUE when a value is present and FALSE when it is NULL. WHERE email IS NOT NULL keeps the rows that have an email address.
What is the difference between IS NULL and = NULL?
IS NULL tests whether a value is missing and returns TRUE or FALSE. = NULL compares with an unknown value and always returns NULL, so a WHERE clause using it returns no rows.
Why is NULL = NULL not true in SQL?
Because NULL means unknown, and two unknown values are not known to be equal. The comparison returns NULL. Use IS NOT DISTINCT FROM, or <=> in MySQL, for a comparison that treats two NULLs as equal.
Is NULL the same as FALSE in SQL?
No. NULL is unknown, a third truth value. NOT NULL is still NULL, while NOT FALSE is TRUE.
What is the difference between NOT NULL and IS NOT NULL?
NOT NULL is a column constraint that stops NULLs from being stored. IS NOT NULL is a test in a query that checks whether a value is present.
Why does NOT IN return no rows when the list contains NULL?
Because x NOT IN (…, NULL) includes the comparison x <> NULL, which is always unknown, so the whole condition can never be TRUE.
Does COUNT count NULL values?
COUNT(*) counts every row, NULLs included. COUNT(column) counts only the rows where that column is not NULL.
Can a Boolean column be NULL?
Yes, unless it is declared NOT NULL. It then has three states, TRUE, FALSE and NULL, and WHERE NOT flag skips the NULL rows.
How do I replace NULL with a default value?
With COALESCE(column, default), which returns the first of its arguments that is not NULL.
Related pages
- Boolean logic in SQL, with the engine's PostgreSQL export
- NULL is not FALSE and unassigned is not false
- The SQL NULL rule worked example
- The code exporter
- Laws: De Morgan and implication elimination
Written by Muaz Notiar. The Tangibool engine (semantics tangibool-semantics/1, engine 1.0.0) computed the truth table marked “Computed by the Tangibool engine”, and the 5 tool results the guide links to were re-run through the tools' own code in the build of and match what the text says. © 2026 Foreranger LTD.