NULL Values Explained in SQL and DBMS
Introduction
NULL is SQL's marker for the absence of a known value. It may represent information that is unknown, not yet available, withheld, or not applicable. A NULL phone number can mean that a customer did not provide one. A NULL shipment date can mean that an order has not shipped. A NULL manager ID can mean that an employee currently has no manager.
NULL is not numeric zero, an empty string, Boolean false, or the literal text 'NULL'. Those are known values with specific meanings. NULL means that SQL does not have an ordinary value to compare or calculate. This difference changes filtering, joins, aggregates, constraints, sorting, and application behavior.
The most important practical rule is that NULL is tested with IS NULL and IS NOT NULL, not with equality. Understanding why requires SQL's three-valued logic: conditions can evaluate to TRUE, FALSE, or UNKNOWN. This tutorial develops that model and applies it to expressions, functions, keys, relationships, reports, APIs, schema design, and testing.
NULL Means Missing or Unknown
NULL has no single universal business meaning. The schema and application must define what absence means for each attribute. A NULL middle name might mean no middle name exists or that it was never collected. A NULL termination date usually means the employment has not ended. A NULL discount may mean no discount applies, or it may mean that pricing has not been calculated.
Those meanings are not interchangeable. If several distinct states matter operationally, one nullable column may be insufficient. A separate status or reason column can distinguish NOT_PROVIDED, NOT_APPLICABLE, PENDING, and REDACTED. NULL can still represent absence, but the model should not force users to guess why the value is absent.
NULL
= no known ordinary value is stored
for this column in this row
NULL Is Not Zero, Empty Text, or FALSE
An account balance of zero means the balance is known and equals zero. A NULL balance means it is unknown or unavailable. An empty string is a known text value containing zero characters, while NULL means no known text value is stored. Boolean false means a condition is known to be false; NULL means the Boolean state is unknown or absent.
| Stored representation | Typical meaning |
|---|---|
| NULL | Missing, unknown, unavailable, or not applicable |
| 0 | Known numeric zero |
| Empty string | Known text containing no characters |
| FALSE | Known Boolean false |
| 'N/A' | Literal text consisting of those characters |
Empty-string behavior has product differences; notably, Oracle SQL generally treats a zero-length character string as NULL. Portable designs and tests should verify the selected DBMS rather than assume identical behavior everywhere.
Why SQL Needs NULL
Real-world records are often incomplete. A new employee may not have a manager yet, a customer may decline to provide a phone number, an active account has no closure date, and an optional product discount may not exist. Requiring a fabricated value such as zero, 1900-01-01, or 'UNKNOWN' can be less accurate than recording absence explicitly.
NULL lets a row exist before every optional fact is available. It also lets optional relationships remain unassigned without inventing a fake parent row. The cost is additional query semantics: calculations may propagate NULL, comparisons become UNKNOWN, and reports must decide how to display or classify missing information.
Nullable and NOT NULL Columns
A nullable column permits NULL. A NOT NULL constraint requires a value for every row. Nullability should follow the business rule rather than convenience.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
manager_id BIGINT NULL,
phone_number VARCHAR(30) NULL
);
Every employee requires a name, while manager and phone are optional in this model. If email is mandatory, declare it NOT NULL; do not depend only on a form to require it.
NOT NULL prevents SQL NULL but does not reject empty strings, blank-only text, zero, or placeholders. Additional CHECK constraints or normalization may be necessary when those values are invalid.
Inserting and Omitting NULL
A caller can explicitly insert NULL into a nullable column.
INSERT INTO employees (
employee_id,
employee_name,
manager_id
)
VALUES (102, 'Alice', NULL);
If an omitted column is nullable and has no applicable default, the stored value is commonly NULL. Exact insert behavior depends on the table definition and DBMS, but omission does not create a meaningful business value automatically.
Applications should distinguish an intentional request to clear a value from a request that simply did not send the field. This becomes especially important in partial update APIs, where missing property and explicit JSON null may represent different commands.
NULL and DEFAULT
A default supplies a value when the insert uses the default behavior, often because the column was omitted or the DEFAULT keyword was specified. Explicit NULL commonly remains NULL when the column permits it, rather than automatically invoking the default.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
shipped_at TIMESTAMP NULL
);
Omitting status produces 'NEW'. Explicitly inserting NULL for status fails because of NOT NULL. The nullable shipment time remains NULL until shipment occurs.
DEFAULT and NOT NULL solve separate problems: the default supplies an ordinary value when appropriate, while NOT NULL forbids absence. Product-specific behavior should be tested for bulk tools, generated columns, and explicit default syntax.
Why Equality Does Not Test NULL
Ordinary comparisons involving NULL do not usually produce TRUE or FALSE. They produce UNKNOWN because SQL cannot decide whether an absent value equals the comparison target.
-- Incorrect NULL tests
WHERE manager_id = NULL
WHERE manager_id <> NULL
-- Correct NULL tests
WHERE manager_id IS NULL
WHERE manager_id IS NOT NULL
Even NULL = NULL is UNKNOWN in ordinary SQL comparison. Two missing values are not proven equal; each simply lacks a known value. The dedicated IS NULL predicate asks whether the marker is present rather than comparing ordinary values.
Three-Valued Logic
SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN is produced when missing information prevents a definite answer. If salary is NULL, salary > 50000 is neither proven true nor proven false.
| Expression | Result | Reason |
|---|---|---|
| TRUE AND UNKNOWN | UNKNOWN | The unknown part can still make the conjunction false |
| FALSE AND UNKNOWN | FALSE | One false condition makes AND false |
| TRUE OR UNKNOWN | TRUE | One true condition makes OR true |
| FALSE OR UNKNOWN | UNKNOWN | The unknown part can still make OR true |
| NOT UNKNOWN | UNKNOWN | Absence remains unresolved |
This logic is the foundation for NULL behavior in WHERE clauses, CHECK constraints, joins, and subqueries.
WHERE Filters Out UNKNOWN
A WHERE clause keeps rows for which its condition is TRUE. Rows producing FALSE or UNKNOWN are excluded. Therefore, WHERE salary > 50000 does not include employees whose salary is NULL.
-- High salary only; unknown salaries are excluded
SELECT employee_name, salary
FROM employees
WHERE salary > 50000;
-- High salary or missing salary
SELECT employee_name, salary
FROM employees
WHERE salary > 50000
OR salary IS NULL;
Queries should handle missing values intentionally. Adding OR column IS NULL is correct only when those rows belong in the requested business category.
NULL-Safe Equality
Sometimes a query needs to treat two NULLs as equivalent for comparison. Standard SQL includes IS DISTINCT FROM and IS NOT DISTINCT FROM, although support and alternative syntax vary by product.
-- True when values are equal, including both being NULL
old_value IS NOT DISTINCT FROM new_value
-- True when values differ, including one NULL and one non-NULL
old_value IS DISTINCT FROM new_value
These predicates are useful in change detection and tests because they produce a definite Boolean result. When a DBMS lacks them, use its documented NULL-safe operator or an explicit expression that handles NULL carefully.
Arithmetic with NULL
Arithmetic generally propagates NULL. If any required operand is unknown, the calculated result is unknown.
salary + bonus
salary = 60000 and bonus = NULL
result = NULL
Replacing NULL with zero is appropriate only when absence truly means zero for that calculation. If bonus is unreported rather than zero, converting it to zero hides uncertainty. The query must follow the business interpretation, not merely produce a convenient number.
COALESCE for Fallback Values
COALESCE returns the first non-NULL expression in its argument list. It is standard SQL and useful for display values, calculation defaults, and fallback selection.
SELECT
employee_name,
COALESCE(bonus, 0) AS displayed_bonus,
COALESCE(work_phone, mobile_phone, 'No phone') AS contact_phone
FROM employees;
COALESCE changes the expression result, not the stored data. A NULL bonus remains NULL in the table unless an UPDATE changes it. This distinction lets reports display zero or friendly text without erasing the underlying fact that the value was absent.
SQL Server also provides ISNULL, MySQL provides IFNULL, and Oracle provides NVL. Their type-resolution and evaluation details can differ. COALESCE is generally the most portable starting point.
NULL and String Concatenation
Concatenating NULL with text varies across DBMS operators and functions. Some expressions return NULL when any part is NULL, while product-specific concatenation functions may skip NULL arguments. Portable code should not assume one behavior.
SELECT
first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name
AS full_name
FROM people;
Even this pattern is product-sensitive because concatenation operators differ. Prefer clear product-appropriate expressions and tests, especially when spacing and empty text matter.
Aggregate Functions and NULL
Most aggregate functions ignore NULL inputs. SUM(salary) and AVG(salary) use only rows with known salary values. MIN and MAX similarly ignore NULL. If no non-NULL values remain, the aggregate commonly returns NULL, except COUNT.
This behavior is not the same as treating missing values as zero. An average over five known salaries and five unknown salaries uses five values, not ten values with five zeros.
SELECT
COUNT(*) AS employee_rows,
COUNT(salary) AS known_salaries,
SUM(salary) AS total_known_salary,
AVG(salary) AS average_known_salary
FROM employees;
Reports should often show both the aggregate and the count of known values so readers understand the missing-data denominator.
COUNT(*) Versus COUNT(column)
COUNT(*) counts rows regardless of NULL values. COUNT(column) counts only rows where that column is not NULL. Their difference can count missing values.
SELECT
COUNT(*) AS total_rows,
COUNT(phone_number) AS rows_with_phone,
COUNT(*) - COUNT(phone_number) AS rows_without_phone
FROM customers;
COUNT(DISTINCT column) typically counts distinct non-NULL values, but exact multi-column forms and behavior vary. Verify the DBMS for portable reporting logic.
NULL in GROUP BY and ORDER BY
Grouping places NULL grouping values together for aggregation, even though ordinary NULL = NULL is UNKNOWN. A report grouped by department ID may therefore contain one group for unassigned employees.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
Default NULL placement in ascending and descending sorting differs among database products. Systems that support NULLS FIRST and NULLS LAST let a query state the desired order explicitly.
SELECT employee_name, termination_date
FROM employees
ORDER BY termination_date NULLS LAST;
Where this syntax is unavailable, a CASE expression can create an explicit sort group. Reports should specify placement rather than depend on a vendor default.
NULL with PRIMARY KEY and UNIQUE
A Primary Key never permits NULL because it must identify every row. A Unique constraint's treatment of NULL differs across DBMS products and definitions. Many systems allow multiple NULL values because missing values are not considered equal; others have different restrictions or offer options that treat NULLs as not distinct.
If a column is a genuine candidate key, declare it NOT NULL UNIQUE. Do not rely on a remembered NULL rule from a different platform. Test the exact database version, collation, index type, and constraint definition.
NULL with FOREIGN KEY Constraints
A nullable Foreign Key can represent an optional relationship. A non-NULL Foreign Key must match an eligible parent key, while NULL says that no relationship is currently asserted.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
manager_id BIGINT NULL,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
A top-level employee may have no manager and store NULL. If every order must have a customer, its Foreign Key should also be NOT NULL. Foreign Key enforcement alone does not make the relationship mandatory.
Composite Foreign Keys have additional match and partial-NULL considerations that vary by definition and DBMS. Avoid partially populated composite relationships unless their semantics are understood and tested.
NULL with CHECK Constraints
A CHECK condition generally rejects FALSE but accepts TRUE or UNKNOWN. Therefore, a nullable age can pass CHECK (age >= 18) when age is NULL because the comparison is UNKNOWN.
age INT NOT NULL,
CONSTRAINT chk_adult_age CHECK (age >= 18)
Use NOT NULL when the value must exist. If NULL is intentionally allowed, the original CHECK already permits it in many systems, or the policy can be made explicit with CHECK (age IS NULL OR age >= 18). CHECK does not replace nullability design.
NULL with IN and NOT IN
IN is a series of equality comparisons. NULL values can therefore produce UNKNOWN. The most dangerous case is NOT IN when its list or subquery contains NULL.
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
);
If the subquery returns NULL, the predicate may become UNKNOWN for every candidate that is not positively matched, producing no rows or unexpected results. Filtering NULL from the subquery can help, but NOT EXISTS usually expresses the anti-relationship more safely.
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
NULL in JOIN Conditions
An inner join keeps rows whose ON condition is TRUE. NULL join keys do not compare equal through ordinary equality, so a child with a nullable NULL Foreign Key does not match a parent row.
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON m.employee_id = e.manager_id;
The left join keeps every employee and returns NULL manager columns for employees without a manager. Those result NULLs can also arise because no matching row exists, even if the selected parent columns are NOT NULL in their own table. A query result can contain NULL introduced by an outer join.
CASE Expressions and NULL
A searched CASE can classify missing values explicitly.
SELECT
employee_name,
CASE
WHEN bonus IS NULL THEN 'Not recorded'
WHEN bonus = 0 THEN 'No bonus'
ELSE 'Bonus assigned'
END AS bonus_status
FROM employees;
Testing WHEN bonus = NULL would not work because the comparison is UNKNOWN. A simple CASE expression also does not ordinarily match NULL through equality, so use a searched CASE with IS NULL.
NULLIF and Safe Calculations
NULLIF(a, b) returns NULL when the two expressions are equal; otherwise it returns the first expression. It can convert a special ordinary value into absence for an expression.
SELECT
total_amount / NULLIF(item_count, 0) AS average_item_amount
FROM order_summaries;
When item_count is zero, NULLIF returns NULL and avoids division by zero in many systems. The result becomes NULL, accurately indicating that the ratio is undefined. COALESCE can then provide a presentation fallback if the business requires one, but replacing undefined with zero should be an explicit decision.
NULL and Date Modeling
NULL dates commonly represent events that have not happened: shipped_at, closed_at, and termination_date. This is usually clearer than using a fake sentinel date.
SELECT order_id
FROM orders
WHERE shipped_at IS NULL
AND status = 'PAID';
An open-ended validity interval may use NULL end_date to mean "still valid." Range queries must handle that meaning explicitly, for example with end_date IS NULL OR end_date >= CURRENT_DATE.
If the system needs to distinguish pending, never applicable, and deliberately removed dates, add lifecycle status or reason data rather than overloading one NULL with several operational meanings.
Avoid Fake Sentinel Values
Values such as 1900-01-01, -1, 'N/A', or an empty string are sometimes used instead of NULL. This can pollute calculations, require every query to remember the convention, collide with legitimate values, and weaken type semantics.
Sentinels may be necessary at an external interface that cannot represent NULL, but the boundary should translate them to a clear internal model. If a special state is a real domain value rather than missing information, model it explicitly with a status, reason, or referenced lookup value.
Do Not Overuse NULL
NULL is useful, but a table with many unrelated nullable columns can indicate that several entity subtypes were compressed into one structure. If corporate customers use tax registration fields while individual customers use personal fields, subtype tables may express the model more clearly than dozens of columns that are NULL for half the rows.
Optional repeating facts such as several phone numbers belong in related tables rather than columns like phone1, phone2, and phone3. Good normalization reduces ambiguous absence and makes constraints more precise.
Unknown Versus Not Applicable
Unknown and not applicable are different business states even though both may be stored as NULL. A missing spouse name might mean the person's marital status was not collected, or it might be inapplicable because the person is unmarried. If workflows, reporting, or compliance treat those cases differently, one nullable spouse field cannot preserve enough information.
Use a status or reason code alongside the nullable value, with CHECK logic that keeps combinations consistent. For example, a phone_status can distinguish PROVIDED, DECLINED, and UNKNOWN. The design should avoid contradictory states such as a provided phone number paired with a missing-status code.
NULL and Data Quality Analysis
Missingness is itself useful information. Data-quality reports should count NULL values and calculate their percentage by source, time period, tenant, or workflow stage.
SELECT
COUNT(*) AS total_customers,
COUNT(phone_number) AS with_phone,
COUNT(*) - COUNT(phone_number) AS missing_phone,
100.0 * (COUNT(*) - COUNT(phone_number))
/ NULLIF(COUNT(*), 0) AS missing_percent
FROM customers;
A rising NULL percentage can reveal a broken integration, optional form change, or incomplete migration. Do not replace NULL in stored data merely to improve a completeness metric; investigate the process that produced it.
Stored Values Versus Display Values
User interfaces and reports often need friendly text such as "Not provided." COALESCE or CASE can produce that display without changing the stored model.
SELECT
customer_name,
COALESCE(phone_number, 'Not provided') AS phone_display
FROM customers;
Display replacement should not be reused blindly for calculations, exports, or API contracts. Once NULL becomes ordinary text, downstream consumers may be unable to distinguish absence from a customer who literally supplied that text. Preserve semantic NULL until the presentation boundary whenever possible.
NULL in JSON and APIs
JSON can represent an explicit null, while a property can also be omitted. APIs often assign different meanings: omitted may mean "leave unchanged" and explicit null may mean "clear this optional value." Create and replace operations may define different rules.
Database adapters, serializers, and object mappers must preserve this distinction where the contract needs it. They should not silently convert empty strings, zeros, false, missing properties, and null into one state. Document nullability in the API schema and align it with database constraints.
NULL, Indexes, and Statistics
Database products differ in whether and how ordinary indexes store NULL entries, how unique indexes treat them, and which predicates use those indexes efficiently. Some systems support filtered or partial indexes such as an index only for rows where a column is not NULL.
The optimizer also tracks NULL frequency in statistics because it affects selectivity. A column that is NULL in most rows behaves differently from one with almost no missing values. Query plans should be measured on representative data.
Avoid wrapping indexed columns in functions without checking the plan. A predicate such as COALESCE(column, value) = ... may be less searchable than separate predicates unless an expression index or product optimization supports it.
Schema Design Questions for NULL
Before allowing NULL, ask whether the attribute is genuinely optional, temporarily unknown, permanently inapplicable, or missing because of a process defect. Decide whether those meanings must be distinguished. Define how calculations, filters, joins, sorting, exports, and updates should behave.
Also ask whether a default is a truthful ordinary value, whether absence belongs in a child table instead, and whether lifecycle status explains the field better. Nullability is part of domain design, not an afterthought left to a database default.
Common NULL Mistakes
The classic errors are using = NULL or <> NULL, treating NULL as zero or empty text, and forgetting that WHERE excludes UNKNOWN. NOT IN with a nullable subquery is another frequent source of empty or incomplete results.
CHECK constraints without NOT NULL may accept missing values unexpectedly. Defaults do not automatically replace explicit NULL. Outer joins can introduce NULL into result columns that are NOT NULL in their source tables. Aggregates ignore NULL rather than treating it as zero.
Modeling mistakes include fake sentinel values, nullable mandatory fields, allowing NULL everywhere for convenience, and using one NULL to represent several business states that need different treatment.
NULL Testing Strategy
Test a nullable column with an ordinary value, explicit NULL, and omission. Test a NOT NULL column with explicit NULL and omitted values both with and without defaults. Verify IS NULL, IS NOT NULL, WHERE filtering, CASE classification, and COALESCE output.
For calculations, test all operands known, one operand NULL, zero denominators, and all-null aggregate groups. Compare COUNT(*) and COUNT(column). Exercise IN, NOT IN, and NOT EXISTS with nullable subqueries. Test inner and outer joins with missing join keys.
Constraint tests should cover nullable Foreign Keys, mandatory Foreign Keys, CHECK UNKNOWN behavior, Primary Keys, and product-specific Unique NULL behavior. API tests should distinguish omitted properties from explicit null. Sorting tests should set NULL placement explicitly where order matters.
NULL Design Workflow
Start with the business meaning of the attribute. If a value must always exist, use NOT NULL and decide whether a legitimate default exists. If absence is valid, document what NULL means and whether several absence reasons need separate modeling.
Define the attribute meaning
-> can the value be absent?
-> no: use NOT NULL
-> yes: define the meaning of NULL
-> separate unknown from not applicable when needed
-> define comparison, calculation, and display behavior
-> define API omission versus explicit null
-> add CHECK, key, and relationship constraints
-> test predicates, aggregates, joins, and boundaries
-> monitor missing-data quality over time
NULL and Integrity Summary
| Constraint or feature | NULL behavior to remember |
|---|---|
| PRIMARY KEY | NULL is never allowed in key columns |
| NOT NULL | Explicitly prohibits NULL |
| FOREIGN KEY | NULL can represent no optional relationship |
| UNIQUE | NULL behavior is DBMS and definition dependent |
| CHECK | UNKNOWN often satisfies the constraint; combine with NOT NULL when required |
| DEFAULT | Does not automatically replace every explicit NULL |
| Aggregates | Most ignore NULL; COUNT(*) still counts rows |
| WHERE | Keeps TRUE and filters both FALSE and UNKNOWN |
Interview Questions and Answers
What does NULL mean in SQL? It marks the absence of a known ordinary value, which may mean unknown, unavailable, or not applicable according to the model.
Is NULL equal to zero or an empty string? No. Zero and empty text are known values. Product-specific empty-string behavior should still be checked.
How do you test NULL? Use IS NULL or IS NOT NULL, not equality or inequality.
What is three-valued logic? SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN; NULL comparisons commonly produce UNKNOWN.
What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts rows, while COUNT(column) ignores rows where that column is NULL.
What does COALESCE do? It returns the first non-NULL expression from its arguments.
Why is NOT IN dangerous with NULL? A NULL in the list or subquery can make nonmatches UNKNOWN. NOT EXISTS is often safer.
Can a Foreign Key be NULL? Yes, when its columns are nullable, to represent an optional relationship. Non-NULL values must still reference a valid parent.
Practical Review Checklist
- Define the exact business meaning of NULL for each nullable column.
- Do not treat NULL as zero, empty text, false, or a sentinel string.
- Use IS NULL and IS NOT NULL for absence tests.
- Remember that WHERE removes UNKNOWN results.
- Use NOT NULL whenever the value is mandatory.
- Combine CHECK with NOT NULL when NULL should not pass.
- Review aggregate denominators because most aggregates ignore NULL.
- Prefer NOT EXISTS over nullable NOT IN subqueries.
- Define NULL placement explicitly in important reports.
- Use COALESCE for results without erasing stored missingness.
- Distinguish omitted API fields from explicit null when required.
- Avoid fake dates, negative IDs, and text placeholders for absence.
- Separate unknown and not-applicable states when workflows differ.
- Test DBMS-specific UNIQUE, index, string, and sorting behavior.
Key Takeaway
NULL represents the absence of a known ordinary value. It is different from zero, empty text, false, and every literal placeholder. Because comparisons with NULL commonly produce UNKNOWN, SQL uses three-valued logic and dedicated predicates for missing values.
NULL
= VALUE UNKNOWN OR ABSENT
Test with:
IS NULL
IS NOT NULL
Do not test with:
= NULL
<> NULL
Good NULL design begins with business meaning. Use NOT NULL for mandatory facts, nullable Foreign Keys for genuinely optional relationships, COALESCE for deliberate fallback expressions, and explicit tests for joins, aggregates, constraints, APIs, and reports. When absence is modeled and queried intentionally, NULL preserves information instead of creating ambiguity.