NULL Handling in SQL
Introduction
NULL is SQL's marker for the absence of a known value. It can represent information that is missing, unknown, not yet provided, or not applicable. Because NULL is not an ordinary value, it changes comparison, filtering, arithmetic, aggregation, joins, constraints, sorting, indexing, and application mappings.
The central rule is that NULL is not zero, an empty string, false, or the text 'NULL'. SQL uses three-valued logic: a predicate can evaluate to TRUE, FALSE, or UNKNOWN. Correct NULL handling therefore begins with business meaning and continues through every query and integration boundary.
What Does NULL Mean?
NULL says that the database does not hold one known value for the column in that row. Consider an employee whose middle name has not been recorded. A NULL middle name does not prove that the employee has no middle name; it states only that no known value is stored.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
full_name VARCHAR(150) NOT NULL,
phone_number VARCHAR(30) NULL
);
INSERT INTO employees (employee_id, full_name, phone_number)
VALUES (101, 'John Carter', NULL);
The nullable declaration should be intentional. Documentation must explain whether NULL means unknown, not collected, withheld, or not applicable, because those meanings can require different workflows.
NULL Is Not Zero, Empty, False, or Text
| Representation | Meaning | Same as NULL? |
|---|---|---|
0 | A known numeric value of zero | No |
'' | A known string with zero characters | No in most databases |
FALSE | A known logical value | No |
'NULL' | A four-character string | No |
NULL | No known SQL value | Yes |
Oracle treats a zero-length character string as NULL in SQL, which is an important portability exception. Applications that distinguish empty from missing text should test every supported engine.
Why NULL Exists
Real data is not always complete at row creation. A shipment has no delivery timestamp before delivery, an employee may have no manager, and an optional profile may omit a secondary phone number. Fake substitutes such as zero, 1900-01-01, or N/A look like ordinary values and can corrupt calculations and constraints.
NULL preserves the fact that a value is absent. It is not automatically bad data. The design problem arises when its meaning is unclear, when unrelated missing reasons are combined, or when query writers treat it like an ordinary scalar.
Nullable and NOT NULL Columns
NOT NULL requires a value for every row. Use it for identifiers, required names, creation timestamps, and other mandatory facts. A nullable column is appropriate only when absence is legitimate in the domain.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
ordered_at TIMESTAMP NOT NULL,
delivered_at TIMESTAMP NULL,
cancellation_reason VARCHAR(500) NULL
);
A philosophy of NOT NULL by default can improve clarity, but it must not lead to invented placeholder values. Requiredness should follow business truth rather than a blanket preference.
Primary and Foreign Keys
A primary key cannot be NULL because it must identify every row. A foreign key may often be NULL when the relationship is optional. An employee's manager_id can be NULL for a top-level executive; that NULL does not reference a special manager row.
When a foreign key has a value, it must reference an allowed parent. When it is NULL, ordinary referential checking does not apply. Use NOT NULL as well as the foreign key when every child must have a parent.
Comparing NULL Correctly
Ordinary equality and inequality do not test missingness. These predicates do not behave as intended:
WHERE phone_number = NULL
WHERE phone_number <> NULL
Both comparisons evaluate to UNKNOWN. Use the dedicated predicates instead.
SELECT *
FROM employees
WHERE phone_number IS NULL;
SELECT *
FROM employees
WHERE phone_number IS NOT NULL;
Three-Valued Logic
SQL predicates can return TRUE, FALSE, or UNKNOWN. A comparison involving NULL usually produces UNKNOWN because SQL cannot determine whether an absent value equals or differs from another value. Even NULL = NULL is UNKNOWN in ordinary comparison; two missing markers do not prove two equal values.
| Expression | Result | Reason |
|---|---|---|
10 = 10 | TRUE | Known equal values |
10 = 20 | FALSE | Known unequal values |
10 = NULL | UNKNOWN | One value is absent |
NULL = NULL | UNKNOWN | Neither value is known |
NULL IS NULL | TRUE | Explicit missingness test |
WHERE Keeps Only TRUE
A WHERE clause retains rows for which its predicate is TRUE. Rows producing FALSE or UNKNOWN are both removed. This explains why a negative comparison does not automatically include NULL rows.
-- Excludes CLOSED and also excludes NULL
SELECT *
FROM tickets
WHERE status <> 'CLOSED';
-- Includes every non-CLOSED row plus missing status
SELECT *
FROM tickets
WHERE status <> 'CLOSED'
OR status IS NULL;
Before adding OR ... IS NULL, decide whether missing status belongs in the result. Correctness depends on the business question.
AND, OR, and NOT with UNKNOWN
Logical operators combine UNKNOWN according to three-valued logic. FALSE AND UNKNOWN is FALSE because one false condition makes the conjunction false. TRUE OR UNKNOWN is TRUE because one true condition makes the disjunction true. NOT UNKNOWN remains UNKNOWN.
| Expression | Result |
|---|---|
TRUE AND UNKNOWN | UNKNOWN |
FALSE AND UNKNOWN | FALSE |
TRUE OR UNKNOWN | TRUE |
FALSE OR UNKNOWN | UNKNOWN |
NOT UNKNOWN | UNKNOWN |
Parentheses are essential in complex predicates. Test each nullable combination instead of reasoning from two-valued application logic.
Arithmetic and Concatenation
Arithmetic with NULL normally returns NULL. An unknown bonus added to a known salary does not produce a known total.
salary + bonus
If bonus is NULL, the result is NULL. Replacing it with zero is correct only if the domain defines missing bonus as no bonus. String concatenation behavior differs by database: some operators propagate NULL, while some functions skip it. Build names and addresses using deliberate product-aware logic.
COALESCE
COALESCE returns the first non-NULL expression from its arguments and is standard SQL.
SELECT
employee_id,
COALESCE(preferred_name, legal_name, 'Unnamed') AS display_name
FROM employees;
It is useful for presentation and domain-approved fallback logic. It changes meaning, however. AVG(COALESCE(score, 0)) treats every missing score as a real zero, while AVG(score) excludes missing scores. Choose the expression from the business definition, not merely to remove NULL from output.
MySQL IFNULL, SQL Server ISNULL, and Oracle NVL provide related behavior with product-specific type and evaluation rules. Prefer COALESCE when its semantics and portability fit.
NULLIF
NULLIF(a, b) returns NULL when its two arguments compare equal; otherwise it returns the first argument. It can normalize a known sentinel or prevent division by zero.
SELECT revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM daily_metrics;
When order_count is zero, the divisor becomes NULL and the result is NULL instead of raising a divide-by-zero error. The caller must still decide how to present that missing ratio.
COUNT and NULL
COUNT(*) counts rows. COUNT(column) counts rows where that expression is not NULL. Their difference is useful for completeness analysis.
SELECT
COUNT(*) AS employee_count,
COUNT(phone_number) AS known_phone_count,
COUNT(*) - COUNT(phone_number) AS missing_phone_count
FROM employees;
A conditional count can target a business rule, but the predicate's UNKNOWN behavior must be understood.
SUM, AVG, MIN, and MAX
Most aggregate functions ignore NULL input values. SUM(amount) adds known amounts, and AVG(amount) divides by the count of known amounts. If every input is NULL, these aggregates generally return NULL rather than zero.
Ignoring NULL is not the same as treating NULL as zero. Suppose bonuses are 500, 0, and NULL. The average of known bonuses is 250. Replacing NULL with zero first produces approximately 166.67. Both calculations are valid for different questions, so reports must define their denominator.
GROUP BY and DISTINCT
GROUP BY typically places NULL rows into one group, and DISTINCT generally returns one NULL representation. This grouping behavior is defined for set operations and does not change the rule that ordinary NULL = NULL is UNKNOWN.
Reporting should label the group deliberately, for example COALESCE(region, 'Unassigned'), while preserving the distinction between a missing region and a real region named Unassigned.
Ordering NULL Values
Whether NULL sorts first or last by default varies among database products and sort direction. Some systems support explicit NULLS FIRST and NULLS LAST. A portable technique sorts on a CASE expression before the actual value.
ORDER BY
CASE WHEN completed_at IS NULL THEN 1 ELSE 0 END,
completed_at
Never rely on an untested default when NULL placement affects a user-facing queue or report.
UNIQUE Constraints and NULL
Database products differ in how many NULL values a UNIQUE constraint or index permits. Some allow multiple NULLs because missing values are not considered equal; others apply different rules or offer filtered indexes. Portability requires testing the target engine.
If the business rule is uniqueness only when a value is present, a filtered or partial unique index can express that intent where supported. If exactly one missing value is allowed, that is a specialized rule requiring an explicit design.
NULL in INNER and OUTER JOINs
An INNER JOIN keeps rows for which the join condition is TRUE. Nullable keys do not match each other through ordinary equality because NULL equals NULL is UNKNOWN. A LEFT JOIN preserves every left row and fills right-side columns with NULL when no match exists.
SELECT e.employee_id, e.full_name, m.full_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON m.employee_id = e.manager_id;
The NULLs in the result can come from stored data or be generated by the outer join. Their interpretation depends on the selected column and relationship.
The Outer Join Filtering Trap
A predicate on the nullable side in WHERE can remove the unmatched rows and effectively turn a LEFT JOIN into an INNER JOIN.
-- Keeps customers even when no ACTIVE order exists
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'ACTIVE';
Moving o.status = 'ACTIVE' to WHERE would discard rows where the join produced NULL. Conditions in ON control matching; conditions in WHERE filter the result after joining.
NOT IN and the NULL Trap
NOT IN can produce surprising results when its list or subquery contains NULL. A comparison against that unknown member can make the overall predicate UNKNOWN, excluding rows that appear unrelated.
-- Safer anti-join pattern
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM blocked_customers AS b
WHERE b.customer_id = c.customer_id
);
NOT EXISTS asks whether a matching row exists and avoids the nullable-list problem. Alternatively, filter NULL from the subquery when that reflects the domain, but NOT EXISTS is usually the clearer anti-join.
IN, EXISTS, and NULL
IN also uses equality comparisons. A match can still return TRUE, but no match combined with a NULL list member can produce UNKNOWN. EXISTS ignores selected values and checks whether the correlated subquery returns at least one row, which makes its logic easier to reason about for existence tests.
CASE Expressions
A searched CASE can test missingness directly.
CASE
WHEN shipped_at IS NULL THEN 'Not shipped'
ELSE 'Shipped'
END
A simple CASE expression such as CASE status WHEN NULL THEN ... uses equality and therefore does not match NULL. Use WHEN status IS NULL in a searched CASE.
CHECK Constraints and UNKNOWN
A CHECK constraint rejects rows when its condition is FALSE. In standard behavior, TRUE and UNKNOWN pass. Therefore CHECK (salary >= 0) does not necessarily reject NULL salary; the comparison is UNKNOWN.
salary DECIMAL(12, 2) NOT NULL
CHECK (salary >= 0)
NOT NULL enforces presence, while CHECK enforces validity of a present value. They solve different problems and are often used together.
DEFAULT Does Not Prevent NULL
A DEFAULT supplies a value when an INSERT omits the column or explicitly requests the default. It does not replace an explicit NULL unless a product-specific mechanism does so.
status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
NOT NULL prohibits explicit missingness. DEFAULT defines omitted-input behavior. A migration that adds a default also needs a deliberate plan for existing rows.
NULL-Safe Equality
Sometimes two values should be considered equal when both are NULL. A portable expression is verbose:
(a = b) OR (a IS NULL AND b IS NULL)
Several databases support IS NOT DISTINCT FROM, which treats two NULLs as not distinct, and IS DISTINCT FROM, which provides a two-valued inequality. MySQL provides the NULL-safe equality operator <=>. Use product-aware syntax when it improves clarity, and test ORM generation.
Dates, Booleans, ENUM, JSON, and Binary Values
A NULL date is better than a fake date when an event has not occurred. A nullable Boolean creates three states: true, false, and unknown; use it only when the third state has meaning. A NULL ENUM column is not the same as an UNKNOWN label.
JSON introduces additional distinctions: the SQL column can be NULL, the document can contain JSON null, or a property can be missing. A NULL BLOB is different from a present zero-length byte sequence. Every data type retains the same central requirement: define what absence means.
Indexes and NULL
Whether an index stores NULL entries and how the optimizer uses them varies by product and index type. An IS NULL predicate can be indexable. Wrapping a column in COALESCE inside a predicate may prevent use of a normal index unless a matching expression index exists.
-- Usually clearer and potentially index-friendly
WHERE completed_at IS NULL
NULL distribution affects statistics and plan choice. A filtered or partial index on rows where processed_at IS NULL can efficiently support a work queue when the unfinished subset is small.
Optional Relationships and Soft Delete
NULL is natural for an optional relationship or event timestamp. A nullable deleted_at can represent an active row when NULL and record deletion time otherwise. That design provides timing information that an is_deleted flag cannot.
Using NULL as state is appropriate when the column's meaning is event absence. It becomes confusing when one nullable field is expected to encode several business reasons. History and explicit status may be needed for restoration, legal holds, and audit.
Avoid Sentinel Values
Sentinels such as -1, 9999-12-31, N/A, or an all-zero identifier bypass SQL's NULL semantics but behave like real values. They can violate foreign keys, distort averages and ranges, occupy indexes, and require every query to remember an undocumented exception.
Use NULL for absence and add a separate reason code when missingness has several business meanings. During imports, normalize legacy sentinels only after confirming their source-specific interpretation.
Unknown Versus Not Applicable
One NULL marker cannot distinguish every reason. Unknown marital status differs from a field that does not apply to an organization. A missing laboratory result differs from a test that was not ordered.
When the distinction affects workflow, reporting, or compliance, model it explicitly with a status or reason column. The value column can remain NULL while a constrained reason records NOT_COLLECTED, NOT_APPLICABLE, WITHHELD, or another approved state.
Financial and Measurement Data
NULL financial amounts must not be replaced with zero casually. Unknown revenue, no revenue, and not-applicable revenue answer different questions. Reports should publish coverage and denominator rules alongside totals and averages.
Measurements need similar care. A missing sensor reading is not a reading of zero. Store quality flags, collection time, unit, and missing reason when scientific or operational interpretation depends on them.
Analytics and Data Quality
Analysts must understand whether NULL rows are excluded, imputed, categorized, or treated as failure. Completeness can be measured with COUNT(column) / COUNT(*), but percentages need decimal arithmetic and divide-by-zero handling.
SELECT
COUNT(phone_number) AS populated,
COUNT(*) AS total,
100.0 * COUNT(phone_number) / NULLIF(COUNT(*), 0) AS completeness_pct
FROM employees;
A rising NULL rate can reveal a broken integration or relaxed validation, but an intentional optional field may legitimately remain sparse. Monitor expectations by source and business stage rather than applying one universal threshold.
ETL, CSV, and Data Exchange
CSV has no universal NULL representation. An empty field, missing column, quoted empty string, and token such as NULL can mean different things. Define the import contract and configure the parser explicitly.
Staging tables help preserve raw input before normalization. Profile sentinel values, whitespace, and invalid dates, then map each source according to documented rules. Do not transform every empty string to NULL if empty has a real meaning in that field.
NULL in APIs
JSON APIs distinguish an omitted property from a property explicitly set to null, although application frameworks may collapse them. This difference is critical for PATCH requests: omission can mean leave unchanged, while null can mean clear the stored value.
API schemas should declare which properties are required and nullable and define clear behavior for create, replace, and patch operations. Database nullability and API nullability should agree or be mapped through an explicit domain rule.
Application and ORM Mapping
Java database NULL maps to object wrappers or Optional-style designs, not primitive int or boolean without a policy. Python commonly maps it to None, JavaScript to null, and C# to nullable value types. Drivers often require dedicated methods for writing and detecting SQL NULL.
ORM annotations should match database constraints. If an application property is optional while the column is NOT NULL, failure occurs late at persistence. If the database allows NULL but application code assumes a primitive default, missing information can silently become zero or false.
Prepared Statements and Optional Filters
Bind NULL through the driver rather than using the string 'NULL'. Dynamic search screens also need deliberate optional-filter logic. A pattern such as WHERE (? IS NULL OR status = ?) can express an omitted filter, but it may produce weak plans on large tables.
Generating parameterized SQL with only active predicates can improve selectivity and index use. Keep all values bound as parameters and compare alternative plans under realistic data distributions.
Schema Evolution and Migrations
Adding a nullable column is operationally easy in many databases, but every existing row then contains NULL and every reader needs semantics for it. Adding NOT NULL to an existing column requires finding and resolving missing rows before enforcement.
A safe migration profiles NULL frequency, defines mappings, backfills in bounded batches, adds application validation, verifies no new NULLs appear, and then adds the constraint. Do not invent a default merely to satisfy DDL; that converts unknown data into a potentially false fact.
NULL in Window Functions
Window functions process rows without collapsing them, and NULL handling depends on the function. Ranking functions order rows according to the database's NULL sort rules unless the ORDER BY clause makes placement explicit. Aggregate window functions usually ignore NULL in the same way as ordinary aggregates.
LAG and LEAD can return NULL for two different reasons: the neighboring row may not exist, or the neighboring row's value may itself be NULL. A default argument can distinguish an absent row in some products, but choose a substitute that cannot be confused with real data.
SELECT
account_id,
measured_at,
reading_value,
LAG(reading_value) OVER (
PARTITION BY account_id
ORDER BY measured_at
) AS previous_reading
FROM sensor_readings;
FIRST_VALUE and LAST_VALUE may return NULL when the selected frame endpoint contains NULL. Some databases support IGNORE NULLS for selected window functions, while others do not or use different syntax. Frame boundaries also affect results, so tests must include leading, trailing, and consecutive missing values.
Conditional Nullability Across Columns
Requiredness can depend on another value. A cancellation reason may be required only when status is CANCELLED, and a company may require a tax identifier only in certain jurisdictions. A single NOT NULL declaration cannot express those conditional rules.
CHECK (
(status = 'CANCELLED' AND cancellation_reason IS NOT NULL)
OR
(status <> 'CANCELLED' AND cancellation_reason IS NULL)
)
Decide whether the inverse rule is really required. Some systems retain an old cancellation reason after reopening a case for audit, in which case forcing NULL in all non-cancelled states would destroy history. Constraints must model the actual lifecycle rather than an attractive symmetry.
Several nullable columns can also represent one alternative structure, such as either a domestic address or an international address. A CHECK can require exactly one complete alternative, but complex structures are often clearer in subtype or child tables. If every query contains a long collection of NULL checks, revisit the model.
Application validation should provide friendly messages, while database constraints remain the final integrity boundary. Test each permitted combination, not just individual columns.
Privacy, Redaction, and Data Retention
Privacy workflows often clear personal data by setting a column to NULL. That operation means the value is no longer stored, but it does not prove that the system never knew it. Audit metadata may need to record that erasure occurred, when it occurred, and under which policy without retaining the erased content.
Redaction differs from unknown input. An email that was never collected, an email deliberately deleted after retention expiry, and an email withheld by the subject may all appear NULL in the primary table. If those distinctions affect compliance or processing, store a separate non-sensitive reason or lifecycle event.
Do not replace erased content with text such as REDACTED in the original column unless that string is an intentional valid domain value. It can leak into messages, uniqueness checks, and analytics as though it were real data. NULL plus controlled audit evidence is usually clearer.
Backups, replicas, logs, caches, search indexes, and warehouses may still contain earlier values after the primary row becomes NULL. A complete retention design follows the data through every copy and defines deletion, expiry, and restoration behavior. SQL NULL is only one part of that operational process.
Common NULL Handling Mistakes
- Using
= NULLor<> NULLinstead of IS predicates. - Treating NULL as zero, false, empty text, or the string
'NULL'. - Using NOT IN with a nullable subquery.
- Replacing NULL with zero before AVG without a business rule.
- Using fake dates and numeric sentinel values.
- Allowing a nullable Boolean without defining the third state.
- Assuming CHECK rejects NULL or DEFAULT prevents explicit NULL.
- Filtering the right side of a LEFT JOIN in WHERE unintentionally.
- Assuming default NULL sort or UNIQUE behavior is portable.
- Overloading one NULL with several missing-data reasons.
- Making every column nullable or forbidding every legitimate absence.
- Ignoring driver, ORM, API, and analytics mappings.
NULL Design Checklist
- Define exactly what NULL means for each nullable column.
- Confirm that absence is valid rather than a validation defect.
- Separate unknown, not applicable, withheld, and pending when needed.
- Use NOT NULL for required facts without inventing placeholders.
- Use IS NULL and IS NOT NULL for missingness tests.
- Review UNKNOWN behavior in every compound predicate.
- Define aggregate denominators and fallback semantics.
- Audit outer joins, NOT IN, checks, defaults, and unique constraints.
- Align database, application, API, import, and report nullability.
- Test migrations and query plans with realistic NULL distribution.
Testing NULL Behavior
Test omitted input, explicit NULL, valid values, zero, empty string, false, sentinel values, and invalid types. Exercise IS NULL, equality, inequality, AND, OR, NOT, CASE, COALESCE, NULLIF, arithmetic, sorting, grouping, and every aggregate.
Query tests should cover inner and outer joins, right-side filters, IN, NOT IN with a nullable set, EXISTS, NOT EXISTS, nullable foreign keys, CHECK constraints, defaults, unique indexes, and product-specific null-safe equality. Verify plans for both common and rare NULL distributions.
Integration tests must round-trip NULL through drivers, ORMs, JSON APIs, CSV imports, event messages, and reports. Migration tests should prove that cleanup is accurate and that rollback does not introduce placeholders or lose missing-reason information.
Interview Questions and Answers
What does NULL mean in SQL? It marks the absence of one known value.
Why does column = NULL fail? Ordinary comparison with NULL returns UNKNOWN; use IS NULL.
What is three-valued logic? SQL predicates can evaluate to TRUE, FALSE, or UNKNOWN.
What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts rows, while COUNT(column) excludes NULL results.
Why can NOT IN be dangerous? A NULL in its list or subquery can make the predicate UNKNOWN; NOT EXISTS is often safer.
Does CHECK reject NULL? A CHECK generally rejects FALSE, while UNKNOWN can pass; use NOT NULL for presence.
Does DEFAULT prevent NULL? No. It supplies omitted values but does not replace an explicitly inserted NULL.
Is NULL the same as an empty string? No in most databases, although Oracle SQL treats zero-length character strings as NULL.
Key Takeaway
NULL represents missing information, not a substitute value. Its three-valued logic affects every layer of SQL, from comparison and filtering to joins, aggregates, constraints, and indexes. Correct handling starts by defining what absence means in the domain.
SAFE NULL DESIGN = EXPLICIT BUSINESS MEANING
+ INTENTIONAL NULLABILITY
+ IS NULL PREDICATES
+ THREE-VALUED LOGIC TESTS
+ DEFINED AGGREGATE RULES
+ CONSISTENT SYSTEM MAPPINGS
Use NULL when a value can genuinely be absent, use NOT NULL when it cannot, and introduce a separate status or reason when one missing marker cannot express the required distinctions. Never hide uncertainty behind zero, fake dates, empty text, or undocumented sentinels.