Three-Valued Logic: TRUE, FALSE and UNKNOWN in a WHERE Clause

An unknown value changes the answer without being true or false. SQL uses three-valued logic when NULL participates in comparisons. WHERE and CHECK handle that third outcome differently, so both deserve explicit tests.

See What WHERE Keeps Under Three-Valued Logic

A comparison has three possible outcomes: TRUE, FALSE, and UNKNOWN. A NULL represents a missing or unknown value. Equality with NULL produces UNKNOWN rather than TRUE, including NULL compared with NULL.

WHERE retains only TRUE rows. FALSE and UNKNOWN are both excluded, although they remain different logical states when expressions are combined.

Use IS NULL to test missingness. Don't write a predicate comparing a column to the literal NULL with an equals sign. Sessions run with ANSI_NULLS ON by default, and the OFF setting is deprecated. Under the default, the first query below returns no rows and the second returns the NULL row.

The following explicit sample lets you compare the wrong predicate with the intended missing-value test. Its values are invented inputs, not results from a live report.

CREATE TABLE #LogicValues(Value int NULL);
INSERT #LogicValues VALUES (1),(NULL);
SELECT Value FROM #LogicValues WHERE Value = NULL;
SELECT Value FROM #LogicValues WHERE Value IS NULL;

Set Up Five Customers and a NULL Exclusion

Use local temporary tables so the experiment is isolated and easy to repeat. Five customers have IDs 1 through 5. The exclusion list contains 2 and a NULL.

The expected business answer is 1, 3, 4, and 5 if a blank exclusion entry is meant to exclude nobody. The data setup is explicit so we can compare both results and plans without relying on a live application table.

DROP TABLE IF EXISTS #Customers;
DROP TABLE IF EXISTS #Excluded;
CREATE TABLE #Customers (CustomerID int NOT NULL PRIMARY KEY);
CREATE TABLE #Excluded (CustomerID int NULL);
INSERT #Customers (CustomerID) VALUES (1),(2),(3),(4),(5);
INSERT #Excluded (CustomerID) VALUES (2),(NULL);
SELECT * FROM #Customers ORDER BY CustomerID;
SELECT * FROM #Excluded;

This is a correctness test before it is a performance test. I start with the expected set written down. Without that, an empty grid can be mistaken for a fast query that found no eligible customers.

What does NULL mean in the exclusion table: missing data, an unknown customer, or a deliberate marker? Resolve that business meaning first.

Watch NOT IN Return an Empty Result

The direct query looks reasonable. It asks for a customer ID absent from the exclusion subquery. With the NULL present, the result is empty.

The comparison to 2 excludes customer 2. For another customer, the comparison to NULL cannot be true or false; it is UNKNOWN. The WHERE clause keeps only TRUE rows, so none remain.

SELECT c.CustomerID
FROM #Customers AS c
WHERE c.CustomerID NOT IN
      (SELECT e.CustomerID FROM #Excluded AS e)
ORDER BY c.CustomerID;

Think of customer 1 as 1 <> 2 AND 1 <> NULL. The first part is TRUE, and the second is UNKNOWN, so the combined condition is UNKNOWN. SQL uses three-valued logic for NULL, not the two-valued logic many readers expect. This is why adding one bad row to a lookup table can make a report appear empty overnight.

Switch to NOT EXISTS for a Per-Row Match

NOT EXISTS asks whether a matching exclusion row exists for this particular customer. A NULL in the exclusion table does not match any non-NULL CustomerID, so it does not poison the other comparisons. The result is 1, 3, 4, and 5. This form also makes the matching relationship explicit when a real query has several key columns.

SELECT c.CustomerID
FROM #Customers AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM #Excluded AS e
    WHERE e.CustomerID = c.CustomerID
)
ORDER BY c.CustomerID;

I prefer this form when exclusion data can contain NULL and the intended rule is no matching row. It is still important to inspect the outer column. Here CustomerID is NOT NULL.

Decide whether a NULL outer key should be returned. NOT EXISTS can return it because equality with NULL finds no match. The schema and the business rule must agree.

Filter NULL Out of the NOT IN Subquery

If NOT IN reads most clearly for a particular query, make the subquery return only known IDs. Add IS NOT NULL inside the subquery. The result again is 1, 3, 4, and 5 in this example.

The filter states that unknown exclusion values do not exclude a known customer. It should be a deliberate rule, not a patch pasted over unexamined data quality.

SELECT c.CustomerID
FROM #Customers AS c
WHERE c.CustomerID NOT IN
(
    SELECT e.CustomerID
    FROM #Excluded AS e
    WHERE e.CustomerID IS NOT NULL
)
ORDER BY c.CustomerID;

A NOT NULL constraint removes this particular risk. It also tells the optimizer more about the exclusion data. If NULL is not a valid entry, investigate the ingestion path and enforce that rule at the source. Filtering bad rows in every report leaves the bad data in place.

Test Three-Valued Logic on Real Exclusion Data

Before changing a report, count NULLs in the exclusion source and inspect why they exist. Use the real subquery's predicates and joins. A join can introduce NULL even when a base column is constrained.

Check whether a parameter or expression changes the type or collation of the compared values. A test with only clean rows will not reproduce the failure.

I keep a regression case containing one NULL and several normal keys. It is a tiny test with a large payoff: the expected four rows make the intended semantics unambiguous. After the fix, compare historical report counts and verify that only customer 2 is excluded in the sample. A query that returns rows again can still be wrong if the business meaning of the blank entry was different.

Remember That CHECK Accepts UNKNOWN

A CHECK constraint rejects FALSE. It accepts TRUE and UNKNOWN. A nullable column with CHECK(Value > 0) therefore still permits NULL.

Add NOT NULL when missing values are invalid. This difference between WHERE and CHECK is part of three-valued logic. Reusing the same expression in both places doesn't create the same acceptance rule.

I test constraints with NULL input before calling them complete. A positive-value rule and a required-value rule are separate conditions. The sample below deliberately leaves the column nullable.

It accepts the declared missing value under the CHECK semantics. A negative value violates the condition. That is expected behavior, rather than evidence that the constraint failed to validate its expression.

CREATE TABLE #CheckUnknown(Value int NULL CHECK(Value > 0));
INSERT #CheckUnknown VALUES (NULL),(1);
SELECT Value FROM #CheckUnknown;

Settle Three-Valued Logic With IS DISTINCT FROM

SQL Server 2022 added IS DISTINCT FROM and IS NOT DISTINCT FROM. They return a definite comparison result when either input is NULL. Two NULL values are not distinct.

A NULL and a known value are distinct. Use that rule when comparing old and new values. The sample below returns only the row that pairs 1 with NULL. Don't invent a replacement sentinel that can collide with real data.

What does a missing value mean in this report? Keep that meaning beside the query. Test missing values on both sides, a missing value in an exclusion list, and an empty exclusion set.

UNKNOWN doesn't RSVP as FALSE. The application needs a deliberate rule for it. Correct null handling keeps filtering, constraints, and change detection aligned with their actual purpose.

SELECT v.LeftValue,v.RightValue
FROM (VALUES (1,1),(1,NULL),(NULL,NULL)) AS v(LeftValue,RightValue)
WHERE v.LeftValue IS DISTINCT FROM v.RightValue;

Related reading on this blog: NOT IN With a NULL in the List Returns No Rows and Difference Between ISNULL and COALESCE.

A NULL rule is not an afterthought, it is part of the query and constraint contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.