NULL, Missing Information, and Three-Valued Logic

Learning outcomes

NULL is SQL’s marker for missing or inapplicable information. It is not zero, an empty string, false, or the text “NULL.” Because comparisons involving missing information may be indeterminate, SQL predicates use three truth values: TRUE, FALSE, and UNKNOWN.

What NULL can mean

One NULL marker cannot distinguish these meanings by itself. When the distinction matters, model additional status or reason columns instead of asking applications to guess.

Why ordinary equality does not test NULL

The expression column = NULL asks whether a known value equals an unknown value. SQL cannot establish that, so the result is UNKNOWN—not TRUE. Use the dedicated predicates IS NULL and IS NOT NULL.

-- Wrong: the predicate is never TRUE.SELECT * FROM contact WHERE middle_name = NULL;-- Correct: selects rows with missing middle_name.SELECT * FROM contact WHERE middle_name IS NULL;-- Correct: selects rows with a present value.SELECT * FROM contact WHERE middle_name IS NOT NULL;

Three-valued logic

A comparison with NULL usually returns UNKNOWN. Logical operators then combine TRUE, FALSE, and UNKNOWN according to SQL’s three-valued logic.

The patterns are intuitive when a known value determines the outcome. FALSE AND UNKNOWN is FALSE because the conjunction cannot become true. TRUE OR UNKNOWN is TRUE because the disjunction is already satisfied.

WHERE keeps only TRUE

Suppose discount_percent is NULL for customers with no recorded discount:

-- Excludes NULL because NULL > 10 is UNKNOWN.SELECT * FROM customerWHERE discount_percent > 10;-- Includes high discounts and missing values.SELECT * FROM customerWHERE discount_percent > 10   OR discount_percent IS NULL;-- This does not include NULL rows either.SELECT * FROM customerWHERE NOT (discount_percent > 10);

The final query returns rows where discount_percent > 10 is FALSE. When the discount is NULL, the inner result is UNKNOWN and NOT UNKNOWN remains UNKNOWN.

NULL in constraints

NOT NULL directly forbids NULL. Other constraints may need additional care:

• A CHECK constraint generally rejects FALSE but permits TRUE or UNKNOWN. Therefore CHECK (price > 0) does not itself forbid a NULL price; add NOT NULL when required.
• UNIQUE handling of multiple NULL values varies in details across products and index options. Test the target DBMS.
• A foreign-key column may be NULL unless declared NOT NULL; a NULL foreign key represents no referenced row.
• Primary-key columns are intended to identify every row and cannot be meaningfully missing.

CREATE TABLE product (    product_id INTEGER PRIMARY KEY,    product_name TEXT NOT NULL,    price_minor INTEGER NOT NULL        CHECK (price_minor >= 0),    discontinued_at TEXT        CHECK (discontinued_at IS NULL               OR datetime(discontinued_at) IS NOT NULL)) STRICT;

NULL in expressions and aggregates

Most arithmetic and string expressions propagate NULL because a result cannot be determined from a missing operand.

Ignoring NULL is not the same as treating it as zero. If three rows contain 10, 20, and NULL, AVG(value) is based on two known values, not three values with the missing one assumed to be zero.

COALESCE and NULLIF

COALESCE(a, b, c) returns the first non-NULL argument. Use it when a fallback has valid business meaning, not simply to hide incomplete data. NULLIF(a, b) returns NULL when the two arguments are equal and otherwise returns a.

SELECT    display_name,    COALESCE(preferred_name, legal_name) AS greeting_name,    COALESCE(discount_percent, 0) AS display_discountFROM customer;-- Prevent division by zero: denominator becomes NULL when zero.SELECT completed_jobs * 1.0 / NULLIF(total_jobs, 0) AS completion_rateFROM worker_summary;

Lab: query incomplete contact data

DROP TABLE IF EXISTS contact;CREATE TABLE contact (    contact_id INTEGER PRIMARY KEY,    legal_name TEXT NOT NULL,    preferred_name TEXT,    email TEXT,    phone TEXT,    verified_at TEXT        CHECK (verified_at IS NULL               OR datetime(verified_at) IS NOT NULL)) STRICT;INSERT INTO contact    (legal_name, preferred_name, email, phone, verified_at)VALUES    ('Nadia Rahimi', 'Nadia', 'nadia@example.com', NULL,     '2026-08-01T10:00:00Z'),    ('Omar Haddad', NULL, NULL, '+49-555-0102', NULL),    ('Lina Chen', '', 'lina@example.com', NULL, NULL);SELECT    contact_id,    legal_name,    preferred_name,    preferred_name IS NULL AS name_is_null,    preferred_name = '' AS name_is_emptyFROM contact;SELECT    COUNT(*) AS all_rows,    COUNT(email) AS rows_with_email,    COUNT(verified_at) AS verified_rowsFROM contact;SELECT legal_name,       COALESCE(NULLIF(preferred_name, ''), legal_name) AS greeting_nameFROM contact;

The third row deliberately contains an empty string rather than NULL. The first query demonstrates that SQL can distinguish a known empty string from missing information.

Common mistakes

Writing = NULL or <> NULL

Use IS NULL or IS NOT NULL.

Replacing all NULLs with default values

A default may erase the distinction between unknown, not applicable, and known zero or blank.

Assuming NOT reverses an UNKNOWN filter

NOT UNKNOWN remains UNKNOWN, so the row is still excluded by WHERE.

Using one nullable column to encode a complex workflow

A timestamp alone may not distinguish not-started, pending, failed, cancelled, and completed. Add an explicit status when those states matter.

Checkpoint and practice

Summary and next lesson

NULL models the absence of a SQL value and introduces UNKNOWN into predicate logic. Correct design uses explicit nullability, dedicated null predicates, carefully chosen fallbacks, and status columns when one missing marker is not expressive enough. The final lesson of Chapter 2 compares type systems and syntax across major SQL vendors.

References

• SQLite: NULL Handling
• PostgreSQL: Comparison Predicates
• PostgreSQL: Aggregate Functions
• MySQL: Working with NULL Values