SQL NULL and Three-Valued Logic

21 Sep 2026 sqldatabases

SQL NULL is a marker meaning that a value is missing, unknown, or not applicable; it is not an ordinary value. Because a comparison involving missing information may itself be unknowable, SQL predicates can produce three results: TRUE, FALSE, or UNKNOWN.

This is why a query can behave differently from equivalent-looking application code. NULL and UNKNOWN are related, but they are not the same thing: NULL appears in data, while UNKNOWN is a result produced when SQL evaluates a condition involving that data.

The problem NULL solves

Real data often has no value to store. A customer's middle name may not be supplied. A shipment may not yet have a delivery date. A measurement may be unavailable. Without NULL, databases would need a special sentinel for each situation: an empty string, zero, a date such as 1900-01-01, or a magic integer like -1.

Sentinels create ambiguity. Is a zero a real measurement or “not measured”? Is an empty string a deliberately empty name or a missing name? NULL gives the database a general marker, while the application can decide what the absence means.

The cost is that ordinary two-valued Boolean reasoning no longer works for every expression.

How NULL changes expressions

A comparison with NULL does not produce TRUE or FALSE, because the answer cannot be established:

SELECT *
FROM orders
WHERE shipped_at = NULL;

This does not find orders whose shipped_at is missing. The comparison asks whether an unknown value equals another unknown value, so its result is UNKNOWN for a row where shipped_at is NULL—and generally for every row in this example.

Use the null test instead:

SELECT *
FROM orders
WHERE shipped_at IS NULL;

Similarly, shipped_at <> NULL is not a way to find non-null dates. Use IS NOT NULL.

Many operations propagate NULL. For example, 10 + NULL is NULL, and NULL > 5 is UNKNOWN. A predicate is a condition that evaluates to a truth result, such as total > 100 or status = 'paid'.

SQL combines predicate results using three-valued logic. The important cases are:

These results follow a useful rule: FALSE makes an AND expression false, and TRUE makes an OR expression true. Otherwise, uncertainty remains. This is why this condition does not necessarily select a row with a missing value:

WHERE shipped_at > CURRENT_DATE OR shipped_at <= CURRENT_DATE

For a non-null date, one side is true. For a NULL date, both comparisons are UNKNOWN; UNKNOWN OR UNKNOWN is still UNKNOWN.

WHERE keeps only TRUE

A WHERE clause filters rows. Its rule is simple but easy to overlook: only TRUE passes. Both FALSE and UNKNOWN remove the row.

SELECT id
FROM orders
WHERE total > 100;

An order with a null total produces UNKNOWN and is excluded, just like an order whose total is 50 produces FALSE and is excluded. This behavior often explains a production query that silently omits records with incomplete data.

If the intended meaning is “totals over 100, while treating missing totals as zero,” state that policy explicitly, using the database's null-handling function or an equivalent expression supported by that database. Do not assume that a failed comparison will behave like a false-valued field in application code.

JOIN conditions also require TRUE

A JOIN condition determines whether rows match. For an inner join, an ON condition must evaluate to TRUE; FALSE and UNKNOWN mean “these rows do not match.”

SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c
  ON o.customer_id = c.id;

If o.customer_id is NULL, the equality is UNKNOWN, so that order has no match in an inner join. This is usually desirable: an absent key should not accidentally match a customer.

An outer join has an additional preservation rule. A LEFT JOIN keeps every row from its left-hand table even when its ON condition is FALSE or UNKNOWN; it fills the right-hand columns with NULL when no match exists.

SELECT o.id, c.name
FROM orders AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.id;

Here, an order with no customer ID still appears, but c.name is NULL. A later WHERE c.name = 'Acme' removes that preserved row, because the new predicate evaluates to UNKNOWN. Moving a condition between ON and WHERE can therefore change a left join into something that behaves like an inner join.

CHECK constraints reject only FALSE

A CHECK constraint protects a row by rejecting it when its condition is FALSE. If the condition is TRUE or UNKNOWN, the row satisfies the constraint.

CREATE TABLE accounts (
    balance DECIMAL(12, 2),
    CHECK (balance >= 0)
);

A negative balance produces FALSE and is rejected. A null balance produces UNKNOWN and is accepted. The constraint says, “if a balance is present, it must not be negative”; it does not say that a balance must be present.

To require both presence and validity, express both requirements:

CREATE TABLE accounts (
    balance DECIMAL(12, 2) NOT NULL,
    CHECK (balance >= 0)
);

This difference is intentional. WHERE and join matching ask whether a row is positively eligible, so only TRUE qualifies. CHECK asks whether a row is proven to violate a rule, so only FALSE is rejected. Once that distinction is clear, many surprising NULL results—missing query rows, unmatched joins, and apparently permissive constraints—become predictable.