NULL
NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.
Overview
NULL is SQL's marker for the absence of a value. It doesn't mean zero, an empty string, or false -- it means "unknown" or "not applicable." This distinction drives a lot of behavior that's surprising to newcomers: arithmetic, comparisons, and even equality checks behave differently once NULL is involved.
Three-valued logic
SQL uses three-valued logic: any expression can be TRUE, FALSE, or UNKNOWN (NULL). Comparing anything to NULL with = or <> produces NULL, not TRUE or FALSE:
Copy code
SELECT NULL = NULL; -- NULL (unknown), not TRUE
SELECT NULL <> 5; -- NULL
SELECT 5 = NULL; -- NULL
A WHERE clause only keeps rows where the condition is TRUE, so WHERE column = NULL never matches anything -- even rows where column is NULL. Use IS NULL / IS NOT NULL instead:
Copy code
SELECT * FROM orders WHERE shipped_date IS NULL; -- correct
SELECT * FROM orders WHERE shipped_date = NULL; -- always returns zero rows
NULL in arithmetic and aggregation
Any arithmetic operation involving NULL produces NULL (5 + NULL is NULL). Most aggregate functions (SUM, AVG, COUNT(column), MAX, MIN) ignore NULL values rather than treating them as zero -- COUNT(*) is the exception, since it counts rows regardless of NULLs.
Handling NULL
COALESCE(expr, default) substitutes a value when an expression is NULL. NULLIF(a, b) does the reverse, turning a specific value into NULL. IS [NOT] DISTINCT FROM provides a null-safe equality comparison that treats two NULLs as equal, unlike =.
DuckDB notes
DuckDB follows standard SQL NULL semantics throughout, including in joins (a NULL join key never matches another NULL join key with =) and sorting, where DuckDB places NULLs last by default in ascending ORDER BY (configurable with NULLS FIRST / NULLS LAST).
Related terms
NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.
COALESCE →COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.
IN operator →The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.
Data profiling →Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it.
GROUPING SETS →GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.
GREATEST and LEAST →GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments.
FAQS
Because NULL represents an unknown value, comparing anything to it with = produces NULL (unknown), not TRUE -- a WHERE clause only keeps rows where the condition is TRUE, so use IS NULL instead.
No -- SUM, AVG, MIN, MAX, and COUNT(column) all ignore NULL values in their input; only COUNT(*) counts every row regardless of NULLs.
Use IS NOT DISTINCT FROM instead of =; unlike standard equality, it returns TRUE when comparing two NULL values.
