IS NULL Operator in SQL
Introduction
The SQL IS NULL operator finds rows where an expression contains NULL. NULL represents the absence of a known value according to the data model: information may be missing, unknown, unavailable, not yet determined, or not applicable. Its opposite, IS NOT NULL, finds rows where a value is present.
SELECT customer_id, customer_name, phone
FROM customers
WHERE phone IS NULL;
This query returns customers without a stored phone value. NULL must not be tested with ordinary equality or inequality. SQL uses three-valued logic, so phone = NULL and phone <> NULL do not produce the expected TRUE result. Use IS NULL and IS NOT NULL.
What NULL Means
NULL means that no known value is stored for the expression. Its precise business meaning belongs to the schema. A NULL shipped_at might mean an order has not shipped, while a NULL middle_name may mean it was not supplied or does not apply.
Good database design documents that meaning. If one nullable column is used for several unrelated states, queries and reports become ambiguous.
NULL Is Not Zero
Zero is a known numeric value. NULL means no known numeric value under the model.
account_balance = 0 -- Known balance of zero.
account_balance IS NULL -- Balance is missing or not applicable.
Replacing NULL with zero can change financial totals, averages, eligibility rules, and data-quality metrics. Make that conversion only when the business definition explicitly treats missing as zero.
NULL Is Not an Empty String
An empty string is a known character value containing no characters; NULL is no known value. Some products, notably Oracle Database, have special empty-string behavior, so test the target platform.
WHERE display_name IS NULL
OR TRIM(display_name) = ''
This data-quality condition finds both missing and blank text. Whitespace-only values are another distinct case that should usually be normalized or rejected at write time.
NULL Is Not FALSE
For a nullable Boolean-like attribute, TRUE means known true, FALSE means known false, and NULL means unknown, not supplied, or not applicable. Using NULL as a casual alternative to FALSE creates a hidden third state that every query must understand.
Basic IS NULL Syntax
expression IS NULL
IS NULL is a predicate. It returns TRUE when the expression is NULL and FALSE otherwise.
SELECT ticket_id, subject
FROM support_ticket
WHERE assigned_agent_id IS NULL;
The query identifies unassigned tickets. Presence does not prove validity: a non-NULL agent ID must still satisfy foreign-key, active-user, and authorization rules.
Basic IS NOT NULL Syntax
expression IS NOT NULL
IS NOT NULL returns TRUE when a value is present.
SELECT customer_id, phone
FROM customers
WHERE phone IS NOT NULL;
This finds stored phone values, but it does not guarantee that they are well formatted, verified, current, or reachable.
Why Equality with NULL Fails
SQL has TRUE, FALSE, and UNKNOWN. Ordinary comparisons with NULL generally return UNKNOWN because an unknown value cannot be proven equal or unequal to another value.
phone = NULL -- UNKNOWN, not a NULL test.
phone <> NULL -- UNKNOWN, not a non-NULL test.
A WHERE clause keeps only TRUE rows. FALSE and UNKNOWN are both filtered out, which explains why these incorrect predicates appear to return nothing.
Three-Valued Logic
NULL affects compound conditions. TRUE AND UNKNOWN becomes UNKNOWN, while FALSE AND UNKNOWN is FALSE. TRUE OR UNKNOWN is TRUE, while FALSE OR UNKNOWN is UNKNOWN.
IS NULL is different from ordinary equality because it produces a definite TRUE or FALSE NULL test. Understanding this distinction is necessary for NOT IN, NOT LIKE, optional filters, CHECK constraints, and outer joins.
Combining IS NULL with AND
SELECT order_id, created_at
FROM orders
WHERE tenant_id = :tenant_id
AND shipped_at IS NULL
AND cancelled_at IS NULL;
Every condition must hold: the order belongs to the tenant, has not shipped, and has not been cancelled. Keep identity and authorization conditions mandatory rather than relying on lifecycle NULL tests alone.
Combining IS NULL with OR
OR can identify rows where at least one value is missing:
WHERE email_address IS NULL
OR phone_number IS NULL
When this group sits beside tenant or visibility scope, use parentheses:
WHERE tenant_id = :tenant_id
AND (email_address IS NULL OR phone_number IS NULL)
Without grouping, one OR branch can bypass the security condition.
All Missing, Any Missing, and Complete Records
-- Both missing.
WHERE email_address IS NULL
AND phone_number IS NULL
-- At least one missing.
WHERE email_address IS NULL
OR phone_number IS NULL
-- Both present.
WHERE email_address IS NOT NULL
AND phone_number IS NOT NULL
Translate words such as all, any, and complete carefully. Their AND/OR choices are a common source of data-quality defects.
NULL with Comparison Operators
A comparison such as amount > 100 does not include NULL amounts because the result is UNKNOWN. Include them only when the requirement says so:
WHERE amount > 100
OR amount IS NULL
Do not add IS NOT NULL automatically to every comparison. It may clarify intent, but ordinary comparisons already reject NULL in WHERE; the real question is whether missing values belong in the result.
NULL with BETWEEN, IN, and LIKE
BETWEEN, IN, and LIKE rely on comparisons and normally do not match a NULL tested expression.
WHERE score BETWEEN 60 AND 100 OR score IS NULL
WHERE status IN ('OPEN', 'PENDING') OR status IS NULL
WHERE name LIKE 'A%' OR name IS NULL
Add the explicit NULL branch only when missing values genuinely satisfy the business requirement.
NOT LIKE Does Not Include NULL
A NULL value does not match a pattern, but it also does not become TRUE under NOT LIKE. The result remains UNKNOWN.
-- Known values that do not begin with temp.
WHERE file_name IS NOT NULL
AND file_name NOT LIKE 'temp%'
-- Include missing file names deliberately.
WHERE file_name NOT LIKE 'temp%'
OR file_name IS NULL
NOT IN and NULL
NOT IN is especially sensitive to NULL. If its list or subquery contains NULL, a nonmatching value can produce UNKNOWN and be filtered out.
-- Dangerous if customer_id can be NULL in the subquery.
WHERE customer_id NOT IN (
SELECT customer_id FROM blocked_customer
)
Use NOT EXISTS for nullable anti-membership:
WHERE NOT EXISTS (
SELECT 1
FROM blocked_customer b
WHERE b.customer_id = c.customer_id
)
NULL in CASE Expressions
Use a searched CASE to test NULL:
CASE
WHEN shipped_at IS NULL THEN 'Not shipped'
ELSE 'Shipped'
END
CASE shipped_at WHEN NULL depends on equality with NULL and does not work as intended. NULL tests belong in WHEN predicates using IS NULL.
COUNT and NULL
COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column.
SELECT COUNT(*) AS total_customers,
COUNT(phone) AS customers_with_phone,
COUNT(*) - COUNT(phone) AS customers_without_phone
FROM customers;
The difference is useful for completeness reporting. Conditional aggregation can count several missing fields in one scan.
SUM, AVG, MIN, and MAX
Standard aggregate functions generally ignore NULL input rows. AVG therefore averages known values rather than treating missing values as zero. If every input is NULL, an aggregate such as SUM or AVG may return NULL.
Replacing NULL with zero before aggregation changes the question. Do so only when missing values are defined as zero by the domain.
Calculating NULL Percentages
SELECT
100.0 * SUM(CASE WHEN email_address IS NULL THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS missing_email_percent
FROM customers;
NULLIF prevents division by zero for an empty table. Choose decimal arithmetic appropriate to the database and reporting precision.
COALESCE and NULL
COALESCE returns the first non-NULL expression:
SELECT COALESCE(preferred_name, legal_name, 'Unnamed') AS display_name
FROM customers;
It is useful for display and fallback selection. It does not change stored data, prove completeness, or always substitute for IS NULL. A filter such as COALESCE(amount, 0) = 0 merges missing and known-zero values and may prevent index use.
NULLIF
NULLIF returns NULL when its two arguments compare equal. It can turn a known sentinel into NULL for an expression or prevent division by zero:
revenue / NULLIF(quantity, 0)
Use it deliberately; converting meaningful zero values to NULL can hide important business states.
NULL as a Lifecycle State
A nullable timestamp often models an event that has not happened:
WHERE shipped_at IS NULL
AND cancelled_at IS NULL
This can be clear when the event's absence has one meaning. If NULL must represent pending approval, failed processing, inapplicable, and not supplied, add an explicit status or reason instead of overloading one missing value.
Soft Deletes
Soft-delete designs commonly treat NULL deleted_at as active and a timestamp as deleted.
SELECT document_id, title
FROM documents
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL;
The tenant condition remains mandatory. Centralize active-row filtering in repository methods, views, or row-level policies so a forgotten predicate does not expose deleted data.
Open-Ended Time Intervals
A NULL end time can represent an interval with no known end:
WHERE valid_from <= :as_of
AND (valid_to > :as_of OR valid_to IS NULL)
This half-open design treats valid_to as exclusive. Document whether NULL means indefinitely valid, not yet ended, or unknown; those meanings are not automatically equivalent.
Optional Foreign Keys
A nullable foreign key can model an optional relationship, such as an employee without a manager.
SELECT employee_id, employee_name
FROM employees
WHERE manager_id IS NULL;
A foreign key generally permits NULL because there is no referenced value to validate. When a value is present, referential integrity still applies.
NULL and INNER JOIN
Ordinary equality joins do not match NULL to NULL because the comparison is UNKNOWN.
FROM a
JOIN b ON a.optional_code = b.optional_code
If two missing codes must be considered equal, state null-safe equality explicitly or use a product-supported null-safe comparison. Do not replace NULL with an arbitrary sentinel unless that sentinel cannot collide with real data.
Null-Safe Equality
Some database products provide a null-safe equality operator or the standard-style IS NOT DISTINCT FROM predicate. These treat two NULL values as equivalent for comparison while still comparing non-NULL values normally.
-- Supported by PostgreSQL and some other products.
WHERE old_value IS NOT DISTINCT FROM new_value
A portable manual form is more verbose:
WHERE old_value = new_value
OR (old_value IS NULL AND new_value IS NULL)
Parenthesize this expression when combining it with other predicates. Avoid COALESCE(old_value, sentinel) = COALESCE(new_value, sentinel) unless the sentinel is guaranteed outside the domain, because a genuine sentinel value could be confused with NULL.
Finding Missing Relationships with LEFT JOIN
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
Test a non-nullable right-side key such as the primary key. Testing an optional right-side column can falsely label a matched row as unmatched. NOT EXISTS is an equally clear alternative for anti-existence.
Predicate Placement in Outer Joins
A right-table predicate in WHERE can remove NULL-extended rows and turn a LEFT JOIN into inner-join behavior.
-- Preserve customers without a paid order match.
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'PAID'
Place match eligibility in ON when unmatched outer rows must remain. Use WHERE for filters that should apply after the join result is formed.
NULL and Constraints
A primary key cannot be NULL. A NOT NULL constraint requires a value. UNIQUE treatment of multiple NULLs varies by database. CHECK constraints often accept UNKNOWN, so a check such as amount > 0 may not reject NULL unless NOT NULL is also present.
amount DECIMAL(12,2) NOT NULL
CHECK (amount >= 0)
Combine presence and validity constraints when both are required.
DEFAULT Does Not Replace Explicit NULL
A DEFAULT is commonly used when an INSERT omits a column. Explicitly inserting NULL may still store NULL if the column permits it.
INSERT INTO task (task_id, priority) VALUES (1, NULL);
Do not assume a default silently converts explicit missing data. Validate the intended write behavior in the target database and application layer.
INSERT, UPDATE, and DELETE
Setting a value to NULL is a business change, not merely technical cleanup.
UPDATE support_ticket
SET assigned_agent_id = NULL,
assignment_updated_at = CURRENT_TIMESTAMP
WHERE tenant_id = :tenant_id
AND ticket_id = :ticket_id;
For DELETE with IS NULL, preview the same predicate, count rows, inspect samples, and use transactions, audit records, affected-row limits, and recovery procedures.
Sorting NULL Values
Default NULL placement under ORDER BY varies by database and direction. Some products support NULLS FIRST or NULLS LAST.
ORDER BY completed_at NULLS LAST
A portable custom order can use CASE:
ORDER BY CASE WHEN completed_at IS NULL THEN 1 ELSE 0 END,
completed_at,
task_id
DISTINCT and GROUP BY
DISTINCT typically groups multiple NULL projected values together for duplicate elimination. GROUP BY similarly forms a NULL group. That does not mean NULL equals NULL under ordinary comparisons; grouping has its own defined behavior.
Use conditional aggregation to report NULL and non-NULL counts per group rather than assuming missing rows disappear from every aggregate.
NULL and Arithmetic
Arithmetic involving NULL generally produces NULL because the result cannot be determined.
unit_price * quantity
If either operand is NULL, the total is unknown. Replacing missing quantity with zero may be correct for one domain and dangerously wrong for another. Define the business meaning before applying COALESCE.
Sentinel Values versus NULL
Values such as 1900-01-01, -1, Unknown, or N/A are often used as substitutes for NULL. They can contaminate averages, ranges, date reports, foreign keys, and validation.
Use NULL for genuine absence when the schema permits it. If the reason matters, add a status or reason column rather than overloading an ordinary domain value.
Modeling NULL Reasons
One nullable field cannot distinguish not collected, customer refused, unavailable, not applicable, and import failed. A separate reason code can preserve that information:
phone_number NULL,
phone_missing_reason = 'CUSTOMER_DECLINED'
Add consistency constraints so a reason is required only when the value is missing and absent when a value is present.
NULL and State-Machine Consistency
Lifecycle columns should agree with explicit status. A SHIPPED order should normally have shipped_at, while a PENDING order should not. Bidirectional rules prevent contradictory rows.
CHECK (
(status = 'SHIPPED' AND shipped_at IS NOT NULL)
OR
(status <> 'SHIPPED' AND shipped_at IS NULL)
)
Real workflows may allow several shipped or closed states, so model the actual transition table rather than copying this simplified rule blindly. Test every valid state, required timestamp, forbidden timestamp, and transition. If failures or reversals need history, an event table is often better than repeatedly clearing nullable timestamps.
IS NULL for Data Quality
NULL checks are valuable for completeness audits:
SELECT customer_id
FROM customers
WHERE email_address IS NULL
OR billing_country IS NULL
OR created_at IS NULL;
Completeness is not the same as validity. A non-NULL email may still be malformed, unverified, or obsolete. Data-quality programs need presence, format, domain, relationship, and timeliness checks.
NULL in ETL and Warehouses
ETL pipelines must map source blanks, missing fields, invalid values, and explicit NULL consistently. Converting all of them to one state destroys diagnostic information.
Analytical warehouses may use an Unknown dimension member to preserve fact-table joins. That is a deliberate dimensional-model pattern, not permission to replace every transactional NULL with a sentinel.
NULL in APIs
An API payload may distinguish a missing property from an explicit JSON null. In a PATCH request, omission often means leave unchanged, while null may mean clear the stored value. The contract must define this behavior.
Validate whether a field is nullable and whether clearing it is authorized. Bind SQL NULL through the database driver using the correct type rather than writing the text string 'NULL'.
ORM and Programming-Language NULL Mapping
Object-relational mappers map database NULL to language-specific nullable references, option types, or wrappers. A primitive Java integer cannot represent NULL without a wrapper, while JSON serializers may omit a property or emit an explicit null depending on configuration.
Inspect generated SQL for expressions such as property == null; a reliable ORM should translate this to IS NULL rather than equality with a NULL parameter. Test reads, inserts, updates, PATCH operations, and query filters through the actual framework because change tracking and serialization can alter omission-versus-clear semantics.
Prepared statements also need the correct SQL type when binding NULL. Drivers may require an explicit type because no runtime value exists from which to infer it.
Optional Search Parameters
A nullable parameter can mean no filter, or it can mean search for NULL rows. These are different operations.
-- NULL parameter means omit this filter.
WHERE (:status IS NULL OR status = :status)
-- Explicit search for missing status.
WHERE status IS NULL
For complex optional filtering, trusted dynamic SQL with bound values may produce clearer semantics and better plans than many nullable OR branches.
IS NULL and Indexes
IS NULL is not automatically slow. Many databases can use an index to find NULL entries, but support and effectiveness depend on the product, index type, column distribution, and query.
If only a small fraction of rows is NULL, the predicate may be highly selective. If nearly every row is NULL, a scan may be cheaper. Filtered or partial indexes can help queue-like queries where a small set of unprocessed rows has a NULL processed_at value.
Composite Indexes and Selectivity
WHERE tenant_id = :tenant_id
AND processed_at IS NULL
ORDER BY created_at, job_id
A composite or filtered index aligned with tenant, pending state, and ordering may support this workload. Review actual plans, row estimates, reads, and write overhead. Keep statistics current because NULL distribution strongly affects estimates.
Queue Queries and Partial Indexes
Job queues often use NULL processed_at or claimed_at to represent pending work. A partial or filtered index containing only pending rows can be effective when completed history is much larger.
-- Conceptual filtered-index predicate; syntax varies by product.
WHERE processed_at IS NULL
The worker query still needs deterministic ordering, concurrency control, and an atomic claim operation. IS NULL identifies candidate jobs but does not prevent two workers from selecting the same row. Use the database's locking, skip-locked, update-returning, or queue pattern appropriate to the platform, and test worker crashes and retries.
Avoid Function-Wrapped NULL Filters
A predicate such as COALESCE(processed_at, sentinel) = sentinel is less clear, can merge genuine sentinel values with NULL, and may prevent direct index access. Prefer processed_at IS NULL when that is the actual requirement.
Real-World IS NULL Scenarios
Operational systems use NULL tests to find work that has not reached a milestone. Each query must combine the NULL condition with the state and security facts that make the interpretation valid.
-- Unassigned active tickets.
WHERE tenant_id = :tenant_id
AND assigned_agent_id IS NULL
AND status IN ('OPEN', 'ESCALATED')
-- Paid orders that have not shipped.
WHERE tenant_id = :tenant_id
AND paid_at IS NOT NULL
AND shipped_at IS NULL
AND cancelled_at IS NULL
-- Contracts active at a point in time.
WHERE start_date <= :as_of_date
AND (end_date > :as_of_date OR end_date IS NULL)
An incomplete-profile report may require several fields, while a complete-profile query requires all of them:
-- At least one required profile field is missing.
WHERE legal_name IS NULL
OR email_address IS NULL
OR billing_country IS NULL
-- Every required profile field is present.
WHERE legal_name IS NOT NULL
AND email_address IS NOT NULL
AND billing_country IS NOT NULL
These examples show why IS NULL is only one part of the rule. A paid order may be intentionally pickup-only, an unassigned ticket may already be closed, and a contract with no end date may still be suspended. Include explicit status or type conditions whenever NULL alone cannot describe the complete business state.
Testing IS NULL
Test a genuine NULL, a valid non-NULL value, zero, empty string, whitespace-only text, false, and any legacy sentinel. Verify that only actual NULL passes IS NULL. For IS NOT NULL, confirm that presence is not incorrectly interpreted as validity.
Test AND/OR grouping, tenant scope, soft-delete rules, lifecycle consistency, optional parameters, outer joins, and database-specific empty-string behavior.
Testing Aggregates and Anti-Joins
For aggregates, include all NULL, no NULL, mixed values, and an empty input set. Verify COUNT(*), COUNT(column), AVG, SUM, and percentages separately.
For NOT IN, include a NULL from the subquery. For LEFT JOIN anti-joins, include a matched row whose optional right-side column is NULL and ensure the test uses a non-nullable key. Compare with NOT EXISTS.
Completeness Reports and Operational Monitoring
A data-quality dashboard should report both counts and denominators. Ten missing emails mean something different in a table of twenty rows and a table of ten million rows. Group metrics by trusted dimensions such as source system, import batch, tenant, or creation month to locate the cause.
SELECT source_system,
COUNT(*) AS total_rows,
SUM(CASE WHEN email_address IS NULL THEN 1 ELSE 0 END) AS missing_email,
SUM(CASE WHEN phone_number IS NULL THEN 1 ELSE 0 END) AS missing_phone
FROM customers
GROUP BY source_system;
Monitor sudden changes in NULL rate after schema, ETL, API, or UI releases. A new spike may indicate broken field mapping rather than a genuine change in user behavior.
Common IS NULL Mistakes
- Writing
= NULL,<> NULL, or!= NULL. - Treating NULL as zero, false, or an empty string.
- Assuming NOT LIKE or NOT IN automatically includes NULL.
- Adding NULL to an IN list instead of using IS NULL.
- Applying COALESCE everywhere and erasing important distinctions.
- Using one nullable field for several business states.
- Testing an optional right-side column to detect a missing join.
- Assuming NULL equals NULL in ordinary comparisons.
- Assuming IS NULL cannot use an index.
- Omitting tenant or authorization predicates from NULL-based queries.
- Running destructive IS NULL mutations without a preview.
Production Review Checklist
- Document what NULL means for each nullable column.
- Use IS NULL and IS NOT NULL, never ordinary equality with NULL.
- Distinguish NULL from zero, false, empty, whitespace, and sentinel values.
- Make AND/OR grouping and authorization scope explicit.
- Review NOT IN, NOT LIKE, and anti-join NULL behavior.
- Use NOT NULL with CHECK when both presence and validity are required.
- Define API omission versus explicit null semantics.
- Use COALESCE only when the fallback has correct business meaning.
- Prefer explicit status or reason fields when missingness has several meanings.
- Preview destructive queries and verify affected-row counts.
- Test database-specific empty-string, collation, and unique-NULL behavior.
- Inspect actual plans using realistic NULL distributions.
Interview Questions and Answers
What does IS NULL do? It returns TRUE when an expression contains NULL.
What does IS NOT NULL do? It returns TRUE when a value is present.
Why not use equals NULL? Ordinary comparison with NULL returns UNKNOWN under three-valued logic.
Is NULL equal to NULL? Not under ordinary equality; the result is UNKNOWN.
NULL versus zero? Zero is known; NULL is missing or unknown.
COUNT(*) versus COUNT(column)? COUNT(*) counts rows; COUNT(column) counts non-NULL values.
Do aggregates ignore NULL? Most standard aggregates ignore NULL inputs, while COUNT(*) counts rows.
Can IS NULL use an index? Often yes, depending on product, index, selectivity, and distribution.
Can a primary key be NULL? No.
Does CHECK always reject NULL? Not necessarily; UNKNOWN may pass, so add NOT NULL when presence is required.
How do you find rows without related records? Use NOT EXISTS or LEFT JOIN and test a non-nullable right-side key with IS NULL.
What is COALESCE? A function that returns the first non-NULL expression.
Key Takeaway
IS NULL and IS NOT NULL are the correct SQL predicates for testing missing values. Their importance extends beyond syntax into three-valued logic, joins, aggregates, constraints, lifecycle states, APIs, data quality, and physical design.
RELIABLE NULL HANDLING = DOCUMENTED BUSINESS MEANING
+ IS NULL / IS NOT NULL
+ THREE-VALUED LOGIC
+ EXPLICIT SECURITY SCOPE
+ CORRECT JOIN SEMANTICS
+ CAREFUL AGGREGATION
+ INDEX-AWARE QUERIES
+ COMPLETE NULL TESTS
Use NULL for genuine absence, preserve distinctions that matter, and introduce explicit states or reason fields when one missing value cannot describe the business truth clearly.