NULL Values and Three-Valued Logic in MySQL

What NULL means

NULL represents a missing, unknown, or inapplicable value. It is not the number zero, an empty string, a space, or the text "NULL". The reason for absence is a data-model choice: a shipped timestamp might be unknown because an order has not shipped, while an optional middle name may not apply. If the distinction matters to the application, model it intentionally rather than replacing every missing value with a convenient default.

A column permits NULL unless it is defined with NOT NULL. Making a column nullable is not merely a storage detail; it changes filtering, calculations, uniqueness behavior, and application mappings. Decide whether absence is valid for each attribute. A required customer identifier should generally be NOT NULL, while an optional cancellation timestamp can be NULL until cancellation occurs. Constraints and application validation should express the same domain rules.

Three-valued logic in comparisons

Ordinary comparisons with NULL produce UNKNOWN rather than TRUE or FALSE. Thus shipped_at = NULL does not find missing shipment times. Use IS NULL or IS NOT NULL. WHERE retains rows only when the condition is true; false and unknown rows are both excluded. This creates common surprises with negation: if a comparison is unknown, applying NOT still does not make it true.

-- This does not select rows whose shipped_at is NULL
SELECT order_id
FROM orders
WHERE shipped_at = NULL;

-- Use the null predicate instead
SELECT order_id
FROM orders
WHERE shipped_at IS NULL;

The special NULL-safe equality operator <=> can compare two values while treating two NULLs as equal. It is useful for specific comparison tasks, but it does not replace IS NULL for ordinary filtering. Use the operator that most clearly expresses the intent, and document less familiar syntax when readers may not recognize it.

NULL in aggregates and expressions

Most aggregate functions ignore NULL inputs. COUNT(*) counts rows, while COUNT(column) counts only rows where that column is not NULL. SUM and AVG work on non-NULL inputs and return NULL when there are no applicable values. This means a count of rows and a count of known values answer different questions. Do not assume a missing measurement contributes zero to an average.

SELECT COUNT(*) AS all_orders,
       COUNT(shipped_at) AS known_ship_dates,
       MAX(shipped_at) AS latest_ship_date
FROM orders;

Functions such as COALESCE(a, b) return the first non-NULL argument. It can provide a display fallback or support a deliberate calculation, but replacing unknown data with zero can change the meaning of reports. For example, zero revenue is a known value; missing revenue is not necessarily the same. Name output columns so consumers understand whether they are seeing original or substituted values.

NULL and joins

An equality join condition involving NULL does not match those values because the comparison is UNKNOWN. A LEFT JOIN preserves unmatched rows from its left input and fills right-side columns with NULL. This makes a common anti-join pattern possible: select left-side records where a right-side primary key is NULL. Check that the tested right-side column cannot itself be NULL for a matched record; a non-nullable key is usually the clearest marker.

Be careful when adding conditions on the right-side table in WHERE after a LEFT JOIN. A condition such as WHERE right_table.status = 'active' excludes rows where the right side is NULL, effectively removing unmatched rows. If unmatched left rows must remain, the condition may belong in the ON clause. Confirm the intended row-preservation behavior with a small example and see MySQL joins.

NOT IN and NULL traps

A NOT IN predicate can produce UNKNOWN if its list or subquery contains NULL. As a result, rows that seem not to match may also be filtered out. For anti-join logic over a subquery, NOT EXISTS is often a clearer and safer formulation, provided its correlation condition is correct. Test empty sets, duplicate values, and nullable columns. Avoid assuming that a query behaves like a programming-language membership operator.

Design and test missing-value behavior

Use NOT NULL with a meaningful default only when all records truly have a valid value. Do not use sentinel values such as zero dates or magic negative numbers to stand in for missingness unless the domain explicitly defines them and constraints enforce the convention. Sentinel values can collide with legitimate data and complicate comparisons. When the reason for absence matters—unknown, not applicable, or not yet supplied—consider a separate status or richer model rather than overloading one NULL.

Tests should cover NULL filters, comparisons, aggregates, joins, and application serialization. Drivers differ in how they map database NULL into language values, and JSON output may omit, null, or transform fields depending on the API. Align the database contract and response contract so clients do not mistake unknown data for a real default. Learn about nullable column design and aggregate functions.

NULL is useful when it accurately represents absence, but it requires deliberate logic. Prefer explicit null predicates, understand UNKNOWN, and test relationships and calculations with missing values instead of assuming ordinary equality rules apply.