NULL · SQL

NULL in SQL is a special marker that indicates a missing, unknown or inapplicable value in a column. It is not a value in the ordinary sense: it is not zero, not an empty string and not false, but the explicit absence of data. Among database fundamentals, NULL is one of the most consequential design elements, because it changes how comparisons, aggregates, joins and constraints behave across every relational database.

The SQL standard handles NULL through three-valued logic, in which a condition can be true, false or unknown. Any ordinary comparison with a null value, including NULL = NULL, yields unknown, and rows for which a WHERE condition is unknown are silently filtered out. This single rule explains most surprises developers encounter with nulls.

How NULL works in three-valued logic

Because NULL stands for an unknown value, SQL cannot say whether it equals anything else, so comparison operators return UNKNOWN instead of TRUE or FALSE. The logical operators then combine these results according to fixed rules: FALSE AND UNKNOWN is FALSE because the outcome is false regardless of the missing value, while TRUE AND UNKNOWN remains UNKNOWN.

The truth table below captures the essential combinations of three-valued logic that every SQL developer should recognize at a glance.

To test for nulls, SQL provides the predicates `IS NULL` and `IS NOT NULL`, which always return TRUE or FALSE. The standard also defines IS DISTINCT FROM, a null-safe comparison that treats two nulls as equal; PostgreSQL, SQLite and recent versions of SQL Server support it, and MySQL offers the equivalent operator <=>.

NULL in expressions, aggregates and sorting

Nulls propagate through most expressions: price * quantity is NULL if either operand is NULL, and string concatenation with || returns NULL in standard SQL. Aggregate functions behave differently, because they ignore nulls rather than propagate them. The distinctions in the table below cause many reporting errors.

Sorting is not consistent across database systems: PostgreSQL and Oracle treat nulls as larger than any value and place them last in ascending order, while MySQL, SQL Server and SQLite place them first. Writing NULLS LAST explicitly, where supported, makes queries portable and predictable.

Functions and constraints for handling NULL

SQL offers several functions that convert or produce nulls deliberately. COALESCE(a, b, c) returns the first non-null argument and is part of the standard, which makes it preferable to vendor functions such as IFNULL, ISNULL or NVL. NULLIF(a, b) returns NULL when the two arguments are equal, a common trick to avoid division by zero with total / NULLIF(count, 0).

At the schema level, the `NOT NULL` constraint is the primary tool for preventing missing data. A primary key column is implicitly NOT NULL, while a foreign key column may be null to express an optional relationship. Unique constraints usually allow multiple nulls because nulls are not equal to each other, although SQL Server traditionally allows only one, and PostgreSQL 15 added NULLS NOT DISTINCT to change this behavior.

A frequent design question is when a column should allow nulls at all. The decision table below summarizes practical guidance.

Common pitfalls with NULL

Most NULL bugs return fewer rows than expected without raising any error, which makes them hard to notice. The most notorious case is NOT IN with a subquery: a single null among the subquery results makes x NOT IN (...) unknown for each candidate, so the result set is completely empty. Using NOT EXISTS avoids this trap.

• Writing `= NULL`: the condition is always unknown, so the query matches nothing; use IS NULL instead.
• Negated filters: WHERE status <> 'closed' also excludes rows where status is null.
• Outer joins: a filter on the outer table in WHERE instead of ON turns a left join into an inner join.
• Oracle empty strings: Oracle Database stores an empty string as NULL, unlike most other systems.
• Application mapping: a SQL null becomes null, None or nil in code and may cause runtime errors there.

The deeper issue is that NULL is semantically overloaded. It may mean that a value is unknown, that it does not apply or that it was never entered, and SQL cannot tell these apart. Some designers therefore document the intended meaning of null per column, which removes guesswork for everyone who writes queries later.

In everyday language

NULL is a database’s way of writing “we do not know” in a box on a form. If a customer form has a phone number field and the customer left it blank, the database does not invent a number; it marks the field as unknown.

The tricky part is that an unknown cannot be compared with anything. If two customers both left their phone number blank, the database cannot say their numbers are the same, because it does not know either of them. That is why asking whether something equals NULL never gives a clear yes, and special wording such as “is null” is needed instead.

Seen in the real world

A sales analyst calculates the average discount per order with AVG(discount) on a table of 10,000 orders. Only 2,000 orders carry a discount, and the remaining 8,000 store NULL rather than 0. Because the aggregate skips nulls, the report shows an average discount of 15 percent instead of the true 3 percent across all orders.

The analyst also tries to list orders without a coupon using WHERE coupon_code NOT IN (SELECT code FROM expired_coupons), and the query returns nothing, because one row in expired_coupons has a null code. After rewriting the average as AVG(COALESCE(discount, 0)) and the filter with NOT EXISTS, both reports become correct, and the team adds a NOT NULL DEFAULT 0 constraint to the discount column so the ambiguity cannot return.