Understand SQL NULL with Three-Valued Logic

NULL is one of the easiest SQL concepts to recognize and one of the easiest to reason about incorrectly.

The problem starts when NULL is treated as if it were an ordinary value such as 0, an empty string, or the word "unknown". It is none of those. In SQL, NULL represents the absence of a known value, and comparisons involving that absence often produce a third logical result: UNKNOWN.

That third result affects filtering, NOT IN, joins, aggregates, and any predicate that touches a nullable column.

A useful mental model is:

ordinary comparison -> TRUE or FALSE
comparison involving missing information -> often UNKNOWN
WHERE keeps only TRUE

Once that model is clear, many surprising SQL results become predictable.

NULL introduces a third logical result

Most programming conditions are explained with two values: true and false. SQL predicates can also evaluate to UNKNOWN.

Consider a nullable shipped_at column:

SELECT order_id
FROM orders
WHERE shipped_at > CURRENT_TIMESTAMP;

For a row where shipped_at has a timestamp, the comparison can be true or false.

For a row where shipped_at is NULL, SQL cannot determine whether the missing timestamp is later than the current time. The predicate therefore evaluates to UNKNOWN.

A WHERE clause keeps rows for which its condition is TRUE. Rows producing either FALSE or UNKNOWN are filtered out.

This is why nullable data can disappear from results even when no comparison was explicitly intended to exclude it.

Do not compare NULL with equals

This is a common mistake:

SELECT order_id
FROM orders
WHERE shipped_at = NULL;

The expression does not ask whether shipped_at is missing in the way many developers expect. An ordinary equality comparison with NULL evaluates to UNKNOWN, including when both sides are NULL.

Use the dedicated null predicate instead:

SELECT order_id
FROM orders
WHERE shipped_at IS NULL;

And for rows with a known value:

SELECT order_id
FROM orders
WHERE shipped_at IS NOT NULL;

This is not just syntax preference. IS NULL and IS NOT NULL express the intended question directly and produce a definite Boolean result.

UNKNOWN changes AND, OR, and NOT

Three-valued logic matters most when conditions are combined.

The important cases can be summarized as:

TRUE  AND UNKNOWN -> UNKNOWN
FALSE AND UNKNOWN -> FALSE

TRUE  OR  UNKNOWN -> TRUE
FALSE OR  UNKNOWN -> UNKNOWN

NOT UNKNOWN -> UNKNOWN

The result depends on whether the known side already determines the answer.

For example:

WHERE is_priority = TRUE
   OR shipped_at > CURRENT_TIMESTAMP

If is_priority is true, the entire predicate is true even when shipped_at is NULL.

If is_priority is false and shipped_at is NULL, the second condition is unknown, so the whole expression is unknown and the row is filtered out.

This is a useful way to reason about nullable predicates: evaluate the condition as logic, not as text.

Make nullable branches explicit when they matter

Suppose a report should include orders that are either unshipped or were shipped after a cutoff.

This predicate is incomplete:

WHERE shipped_at >= :cutoff

Rows with NULL in shipped_at do not pass because the comparison is unknown.

State the missing-value rule explicitly:

WHERE shipped_at IS NULL
   OR shipped_at >= :cutoff

That query documents the business meaning of NULL: an unshipped order belongs in the result.

Do not automatically add IS NULL to every nullable predicate. Sometimes excluding missing values is exactly correct. The important step is deciding what missing data means for this particular query.

NOT IN has a dangerous interaction with NULL

NOT IN is a frequent source of production bugs because its behavior follows three-valued logic.

Consider:

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (2, 7, NULL);

It is tempting to read this as “return every customer except 2 and 7.”

Conceptually, for customer 5, the predicate behaves like:

5 <> 2
AND 5 <> 7
AND 5 <> NULL

TRUE
AND TRUE
AND UNKNOWN
= UNKNOWN

Because WHERE keeps only TRUE, customer 5 is not returned.

The same problem appears when the right side comes from a subquery:

SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id NOT IN (
    SELECT b.customer_id
    FROM blocked_customers AS b
);

If that subquery can produce even one NULL, rows that have no matching blocked customer may still evaluate to UNKNOWN.

Prefer NOT EXISTS for anti-joins

When the intent is “return rows for which no matching row exists,” NOT EXISTS usually expresses the rule more directly:

SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers AS b
    WHERE b.customer_id = c.customer_id
);

A NULL b.customer_id does not match a non-null c.customer_id, so it does not poison the entire anti-join condition.

Another valid approach is to guarantee that the subquery cannot return NULL, for example through a NOT NULL constraint or an explicit filter:

WHERE c.customer_id NOT IN (
    SELECT b.customer_id
    FROM blocked_customers AS b
    WHERE b.customer_id IS NOT NULL
);

Use the version that best reflects the data contract. If blocked customer identifiers should never be missing, a schema constraint is stronger than repeatedly compensating for nullable data in queries.

IN is less surprising, but NULL still matters

IN has related semantics.

For example:

5 IN (2, 7, NULL)

No equality comparison succeeds, but the comparison with NULL is unknown. The final result is therefore UNKNOWN, not FALSE.

By contrast:

7 IN (2, 7, NULL)

is TRUE because one equality comparison succeeds. Once a true alternative exists, the unknown alternative does not change the result.

This asymmetry is one reason NOT IN feels more dangerous: negating or combining unknown results can remove rows that appear unrelated to the null value.

NULL also changes joins

Suppose two tables contain an optional external identifier:

SELECT a.id, b.id
FROM accounts AS a
JOIN imports AS b
  ON a.external_id = b.external_id;

Rows where both external_id values are NULL do not match through ordinary equality. The comparison NULL = NULL is UNKNOWN, not TRUE.

That is often desirable: two missing identifiers do not prove that two records refer to the same entity.

If the application really needs null-safe equality, make that requirement explicit using the database’s supported null-safe comparison construct. The exact syntax varies across database systems, so check the target database rather than assuming ordinary = treats two nulls as equal.

The data-model question comes first: if a missing identifier cannot establish identity, ordinary equality is the safer semantics.

COUNT(column) does not count NULL values

Aggregates add another important distinction.

Given:

id | discount_code
---+--------------
1  | SAVE10
2  | NULL
3  | SAVE20
4  | NULL

These two counts answer different questions:

SELECT
    COUNT(*) AS row_count,
    COUNT(discount_code) AS known_discount_count
FROM orders;

COUNT(*) counts rows.

COUNT(discount_code) counts rows where discount_code is not NULL.

For the sample above:

row_count = 4
known_discount_count = 2

Other common aggregates such as SUM, AVG, MIN, and MAX generally ignore null input values. If all relevant input values are null, the aggregate result may itself be null, depending on the aggregate.

Do not replace nulls with zeros before aggregation unless zero is actually the domain meaning you want.

COALESCE is useful when a default is semantically correct

COALESCE returns the first non-null expression:

SELECT COALESCE(display_name, username, 'Anonymous')
FROM users;

This is useful when the query needs a fallback representation.

It can also make arithmetic explicit:

SELECT subtotal + COALESCE(shipping_fee, 0)
FROM invoices;

But this is correct only if a missing shipping_fee really means zero.

If NULL means “fee has not been calculated yet,” replacing it with zero hides an important state and can produce misleading totals.

Use COALESCE to express a real default, not merely to make NULL disappear.

Be careful when rewriting predicates with defaults

A predicate such as:

WHERE COALESCE(status, 'pending') = 'pending'

treats a missing status as if the stored value were actually 'pending'.

That may be valid, but it changes the data semantics. Compare it with the more explicit form:

WHERE status = 'pending'
   OR status IS NULL

Both can represent the same intended result under a suitable schema, but the second form makes the two states visible to a reader.

There can also be indexing and optimizer implications when a column is wrapped in an expression. Those details differ by database and index design, so choose the predicate for semantic correctness first and verify performance with the target system’s execution plan.

Constraints can remove ambiguity from queries

Many null-handling problems are really data-model problems.

If every payment must have a currency, declare that requirement in the schema:

CREATE TABLE payments (
    payment_id BIGINT PRIMARY KEY,
    currency_code CHAR(3) NOT NULL
);

Now downstream queries do not need to wonder what a missing currency means.

Use nullable columns when absence is a meaningful and permitted state. Use NOT NULL when the application cannot represent a valid row without the value.

This moves an invariant from scattered query assumptions into the database contract.

Common mistakes

Writing column = NULL or column <> NULL

Ordinary comparisons with NULL produce UNKNOWN. Use IS NULL or IS NOT NULL when testing whether a value is missing.

Assuming WHERE keeps UNKNOWN rows

It does not. WHERE keeps only rows for which the predicate is true.

Using NOT IN with a nullable input set

A single null on the right side can turn non-matching comparisons into UNKNOWN. Use NOT EXISTS for anti-join semantics or guarantee that the input set cannot contain nulls.

Replacing every NULL with a default

A default is correct only when it represents the same domain state. “Unknown”, “not yet calculated”, and zero are not interchangeable.

Treating two missing identifiers as equal identities

NULL = NULL is not true, and in many data models that is exactly the right behavior. Missing identity information should not silently create a match.

A practical review method

When reviewing a query that touches nullable columns, ask four questions:

1. Can this expression evaluate to UNKNOWN?
2. If it does, should the row be included or excluded?
3. Does NULL mean missing, not applicable, not yet known, or something else in this column?
4. Would a schema constraint make the intended rule clearer?

For IN, NOT IN, joins, and compound predicates, also inspect the nullable columns on both sides of the comparison.

This review is more reliable than adding COALESCE or IS NULL reactively after a surprising result appears.

Keep the data meaning visible

SQL NULL becomes manageable when you stop treating it as a strange value and start treating it as missing information.

Comparisons involving missing information can produce UNKNOWN. WHERE keeps only true predicates. NOT IN can therefore fail in surprising ways when its input contains nulls, aggregates may ignore null inputs, and defaults can hide meaningful states if they are applied carelessly.

Write null behavior deliberately. Test for absence with IS NULL, prefer NOT EXISTS when expressing anti-joins over nullable data, use COALESCE only for genuine defaults, and use schema constraints when a value is required.

Those practices turn null handling from a collection of SQL edge cases into a predictable part of query design.