NULLs and Three-Valued Logic

NULL represents the absence of a known value; it is not zero, an empty string, or a special value that equals itself. SQL predicates therefore use three-valued logic: TRUE, FALSE, and UNKNOWN.

Prerequisites

You should be able to filter and sort queries from Select, Filter, and Sort.

Learning goals

• Predict how NULL affects comparisons and Boolean expressions.
• Use IS NULL, IS NOT NULL, and COALESCE intentionally.
• Explain why NOT IN can become UNKNOWN when its subquery contains NULL.
• Distinguish COUNT(*) from COUNT(column).

UNKNOWN is filtered out

The comparison amount = NULL is never TRUE; even NULL = NULL is UNKNOWN. A WHERE clause retains only rows for which its predicate is TRUE. Test missingness with IS NULL or IS NOT NULL.

AND and OR follow three-valued truth tables. For example, FALSE AND UNKNOWN is FALSE, while TRUE AND UNKNOWN remains UNKNOWN. Parenthesize mixed conditions so the intended logic is visible.

Worked example

This portable Standard SQL query explicitly asks for the missing amount and a known comparison row.

SELECT order_id, amount
FROM orders
WHERE amount IS NULL OR order_id = 101
ORDER BY order_id DESC;

order_id	amount
104	NULL
101	40.00

An outer join introduces NULL values for the missing side. Testing the joined key identifies customers with no orders.

SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

customer_id	customer_name
4	Dia

Most aggregates ignore NULL: SUM(amount) skips order 104, COUNT(amount) counts known amounts, and COUNT(*) counts rows. State which population your denominator represents.

For exclusion, prefer NOT EXISTS when nullable values may occur. x NOT IN (subquery) is equivalent to a series of comparisons; one NULL can make the result UNKNOWN for otherwise unmatched values.

Limitations and common mistakes

• Replacing missing amounts with zero using COALESCE changes meaning; “unknown” and “known zero” are different facts.
• A left join followed by WHERE right_table.some_value = ... removes unmatched rows and can accidentally behave like an inner join.
• NULLS FIRST and NULLS LAST should be explicit when order portability matters. DuckDB defaults to NULLS LAST in both directions; other systems differ.

Practice

Compare COUNT(*), COUNT(amount), and SUM(amount) for Ben's two orders. Explain why the counts differ and why the sum is 25.00 rather than unknown.

Next steps

Use this missingness model while studying joins without duplicate surprises.

Primary sources

Checked 2026-10-14: DuckDB NULL values, DuckDB Boolean type, and DuckDB IN.