NULL Handling in SQL Functions
Introduction
NULL handling in SQL functions means understanding what a missing or unknown value represents, how functions respond to it, and when a query should preserve, replace, reject, or classify it. NULL is not zero, an empty string, FALSE, or a magic placeholder. It represents the absence of a known value according to the data model.
SELECT UPPER(NULL) AS upper_value,
100 + NULL AS calculated_value;
-- Both results are typically NULL
Many functions and arithmetic operators propagate NULL because a calculation involving an unknown input is also unknown. Other functions, including COALESCE and NULLIF, exist specifically to control NULL behavior. Aggregate functions introduce another rule by ignoring NULL inputs in many calculations.
Correct NULL handling starts with meaning. A missing bonus may mean not assigned, not yet calculated, not applicable, or unavailable. Replacing every missing bonus with zero can create a clean-looking report that communicates the wrong business fact.
What Is NULL?
NULL marks a value as absent or unknown. It can appear because information was not collected, a value does not apply, an optional relationship is missing, a calculation has not completed, or an outer join did not find a matching row.
| Stored state | Possible meaning |
|---|---|
bonus = 0 | The known bonus amount is zero |
bonus IS NULL | No known bonus value is available |
notes = '' | A known string contains zero characters |
notes IS NULL | No known notes value is available |
The application and schema should document which interpretation applies. If several different reasons for missing data matter, a nullable column alone may be insufficient; a status or reason code may also be required.
NULL Is Not Zero
Zero is a known numeric value that participates in arithmetic and comparisons. NULL represents no known numeric value. The difference affects sums, averages, rates, validations, and business decisions.
For example, a zero discount means the order is known to receive no discount. A NULL discount might mean eligibility has not been evaluated. Treating both as zero can cause an order to advance before pricing is complete.
NULL Is Not an Empty String
An empty string is a known text value with no characters. NULL means no known text value. Most databases distinguish them, but Oracle SQL generally treats a zero-length character string as NULL, so portable logic must account for product behavior.
SELECT customer_id
FROM customer
WHERE middle_name IS NULL
OR middle_name = '';
This query intentionally checks both states in products that distinguish them. Whitespace-only text is a third state and may require TRIM for validation.
NULL Propagation
Propagation means that a NULL input causes a NULL result. Arithmetic commonly follows this rule because a known number combined with an unknown number produces an unknown result.
SELECT 100 + NULL AS addition_result,
100 - NULL AS subtraction_result,
100 * NULL AS multiplication_result,
100 / NULL AS division_result;
Many scalar functions also propagate NULL. UPPER(NULL), LOWER(NULL), ABS(NULL), and ROUND(NULL, 2) typically return NULL. Always verify vendor documentation, especially for concatenation, JSON, array, and custom functions.
NULL in String Functions
A string transformation cannot normally produce meaningful text from an unknown string, so NULL is preserved.
SELECT employee_id,
UPPER(nickname) AS uppercase_nickname,
CHAR_LENGTH(nickname) AS nickname_length
FROM employee;
Rows with a NULL nickname normally return NULL for both expressions. Replacing it with a label is a presentation decision that should be visible in SQL or application logic.
NULL in Numeric Functions
Numeric functions normally propagate NULL because magnitude, rounding, and powers cannot be calculated without a known value.
SELECT transaction_id,
ABS(amount) AS amount_magnitude,
ROUND(exchange_rate, 4) AS rounded_rate
FROM transaction;
A NULL result can be valuable because it preserves the distinction between zero and unknown. Do not add COALESCE merely to make every output non-NULL.
NULL in Nested Functions
When an inner function returns NULL, an outer NULL-propagating function usually returns NULL as well. In UPPER(TRIM(name)), a NULL name passes through both layers.
SELECT UPPER(
TRIM(
COALESCE(display_name, legal_name)
)
) AS normalized_name
FROM customer;
COALESCE is placed at the input boundary so TRIM and UPPER receive the first available name. If both names are NULL, the final result remains NULL.
Why Explicit NULL Handling Matters
Unplanned NULL behavior can remove rows from filters, turn calculations into NULL, change aggregate denominators, produce incomplete text, cause conversion errors, or violate an API contract. Over-handling is also harmful because automatic fallbacks can hide missing data.
Every use should answer two questions: what does NULL mean here, and what result should the consumer receive? The technical function follows from that business decision.
COALESCE
COALESCE returns the first non-NULL expression in its argument list. It is standard SQL and supports multiple fallback choices.
SELECT COALESCE(NULL, NULL, 'SQL', 'Java') AS selected_value;
-- Result: SQL
COALESCE is commonly used for display fallbacks, optional contact fields, default calculation inputs, and empty aggregate results. Arguments must be type-compatible according to database precedence rules.
COALESCE with Multiple Columns
Several candidate columns can be checked in priority order. The first known value is returned.
SELECT customer_id,
COALESCE(
mobile_phone,
home_phone,
work_phone,
'No phone available'
) AS contact_phone
FROM customer;
This produces a convenient contact value but does not indicate which source column was chosen. Include a CASE-based source label if that information matters.
COALESCE in Calculations
COALESCE can replace a missing numeric input when the business rule defines a real default.
SELECT order_id,
subtotal - COALESCE(discount_amount, 0) AS amount_after_discount
FROM orders;
The expression is correct only if NULL discount means no discount. If it means discount pending, the query should preserve NULL or exclude incomplete orders.
Do Not Blindly Replace NULL with Zero
Replacing NULL with zero changes an unknown value into a known value. This can change totals, averages, ranking, eligibility rules, alerts, and data-quality metrics.
A dashboard may need both a business total using accepted defaults and a completeness indicator showing how many values were missing. One output should not silently substitute for the other.
NULLIF
NULLIF compares two expressions. It returns NULL when they are equal; otherwise, it returns the first expression.
SELECT NULLIF(status_code, 'UNKNOWN') AS usable_status
FROM staging_event;
NULLIF is useful for converting a sentinel value to NULL, preventing division by zero, and simplifying comparisons. The two expressions should have compatible types.
NULLIF for Safe Division
A zero denominator raises an error in many databases. NULLIF changes zero to NULL so the division result becomes NULL rather than failing.
SELECT campaign_id,
click_count / NULLIF(impression_count, 0) AS click_rate
FROM campaign_metric;
Use decimal types when a fractional result is required. A NULL rate communicates that no valid denominator exists, which is often more accurate than zero.
NULLIF and COALESCE Together
An outer COALESCE can replace the NULL produced by safe division when the consumer explicitly requires a default.
SELECT COALESCE(
click_count / NULLIF(impression_count, 0),
0
) AS displayed_click_rate
FROM campaign_metric;
This makes no impressions appear as a zero rate. Document that reporting convention so consumers do not interpret it as a measured value.
IFNULL, ISNULL, and NVL
Databases provide product-specific alternatives. MySQL and SQLite commonly provide IFNULL, SQL Server provides ISNULL, and Oracle provides NVL. Names, argument counts, return-type selection, truncation behavior, and evaluation can differ.
| Function | Common platform | General purpose |
|---|---|---|
| COALESCE | Standard and widely supported | First non-NULL among multiple expressions |
| IFNULL | MySQL and SQLite | Two-expression fallback |
| ISNULL | SQL Server | Two-expression fallback |
| NVL | Oracle | Two-expression fallback |
COALESCE is a strong portability default, but it is not always behaviorally identical to the vendor function. Test types and lengths, not only displayed values.
NULL in Concatenation
Concatenation behavior varies. An operator or function may propagate NULL, skip NULL, or treat it as empty according to the DBMS and settings.
SELECT CONCAT(
COALESCE(first_name, ''),
' ',
COALESCE(last_name, '')
) AS full_name
FROM customer;
This can produce leading or trailing spaces. An outer TRIM can improve display, but a fully missing name may become an empty string rather than NULL. Choose intentionally.
NULL in Comparisons
Ordinary comparisons with NULL do not return TRUE or FALSE; they return UNKNOWN. Therefore, column = NULL and column <> NULL do not test missing values.
SELECT employee_id
FROM employee
WHERE bonus IS NULL;
SELECT employee_id
FROM employee
WHERE bonus IS NOT NULL;
Use IS NULL and IS NOT NULL for portable missing-value checks.
Three-Valued Logic
SQL predicates can evaluate to TRUE, FALSE, or UNKNOWN. WHERE keeps only rows for which the condition is TRUE. Rows producing FALSE or UNKNOWN are filtered out.
SELECT employee_id
FROM employee
WHERE bonus > 1000;
An employee with NULL bonus is not selected because the comparison is UNKNOWN. Add an explicit NULL branch when the requirement includes those rows.
Explicit NULL Filtering
A business filter should state how missing values are treated.
SELECT employee_id
FROM employee
WHERE bonus > 1000
OR bonus IS NULL;
This includes large known bonuses and unknown bonuses. It does not claim they are equivalent; it defines one reporting population.
NULL in JOIN Conditions
Equality joins do not match two NULL keys because NULL equals NULL is UNKNOWN. This is usually correct because two missing identifiers do not prove that rows refer to the same entity.
Do not use COALESCE to a shared magic value merely to force NULL keys to match. That can create many false matches. If NULL-safe equality is truly required, use a documented product-specific operator or explicit logic and validate cardinality.
Outer Joins Introduce NULL
A LEFT JOIN returns the left row even when no right row matches, filling right-side columns with NULL. A NULL from an outer join can therefore mean no related row, even if the original right-side column is declared NOT NULL.
SELECT c.customer_id,
COALESCE(o.order_count, 0) AS order_count
FROM customer AS c
LEFT JOIN customer_order_summary AS o
ON o.customer_id = c.customer_id;
Here zero is meaningful if no summary row means no orders. Verify that missing summary data cannot also indicate a failed refresh.
The NOT IN and NULL Trap
If a NOT IN subquery returns any NULL, comparisons can become UNKNOWN and the query may return no rows unexpectedly. NOT EXISTS is often safer for anti-joins because correlation is expressed directly.
SELECT c.customer_id
FROM customer AS c
WHERE NOT EXISTS (
SELECT 1
FROM blocked_customer AS b
WHERE b.customer_id = c.customer_id
);
Alternatively, filter NULL from the NOT IN subquery when that matches the requirement. Automated tests should include a NULL in the exclusion set.
NULL in Aggregate Functions
COUNT(expression), SUM, AVG, MIN, and MAX generally ignore NULL inputs. COUNT(*) counts rows regardless of column NULL values. These rules make aggregate choice part of the metric definition.
SELECT COUNT(*) AS employee_rows,
COUNT(bonus) AS known_bonus_rows,
SUM(bonus) AS total_known_bonus,
AVG(bonus) AS average_known_bonus
FROM employee;
The average denominator is the number of non-NULL bonuses, not all employees. This may be exactly right or may overstate a workforce-wide average.
COUNT(*) vs COUNT(column)
COUNT(*) counts every qualifying row. COUNT(column) counts qualifying rows where that expression is non-NULL. The difference is useful for completeness reporting.
If 100 employees exist and 70 have a recorded bonus, COUNT(*) returns 100 and COUNT(bonus) returns 70. Their difference is the missing count.
SUM, AVG, and Empty Sets
SUM and AVG can return NULL when no non-NULL inputs exist. COUNT returns zero. An outer COALESCE can convert an empty sum to zero when the metric defines no qualifying amounts as zero total.
SELECT COALESCE(SUM(total_amount), 0) AS paid_total
FROM orders
WHERE customer_id = :customer_id
AND status = 'PAID';
Applying COALESCE inside AVG changes its denominator by treating missing values as zero. Applying it outside AVG only changes the result when no known values exist. Placement changes meaning.
NULL in GROUP BY and DISTINCT
GROUP BY normally places NULL grouping keys into one group for reporting purposes, and DISTINCT returns one NULL among duplicate NULL values. This behavior does not mean NULL equals NULL in ordinary comparisons.
Label a NULL group with COALESCE only for display. If the fallback text can also occur as a real value, keep a separate status or ordering expression so the groups are not confused.
NULL Ordering
Databases differ in whether NULL sorts before or after non-NULL values by default. Some support explicit NULLS FIRST or NULLS LAST syntax. A portable CASE expression can create an explicit NULL bucket.
SELECT customer_id, last_contact_at
FROM customer
ORDER BY CASE WHEN last_contact_at IS NULL THEN 1 ELSE 0 END,
last_contact_at DESC;
Explicit ordering prevents database migration or sort-direction changes from unexpectedly moving missing values.
NULL in CASE
CASE can distinguish missing, known zero, and other values. Use a searched CASE with IS NULL because a simple equality branch cannot match NULL.
SELECT employee_id,
CASE
WHEN bonus IS NULL THEN 'NOT RECORDED'
WHEN bonus = 0 THEN 'NO BONUS'
ELSE 'BONUS RECORDED'
END AS bonus_status
FROM employee;
CASE is more expressive than COALESCE when different conditions require different labels or calculations.
Data Type Compatibility
COALESCE and CASE choose a common result type according to database precedence rules. A fallback literal can cause conversion, truncation, collation changes, or errors.
SELECT COALESCE(
discount_rate,
CAST(0 AS DECIMAL(7, 4))
) AS discount_rate
FROM product;
Use explicit compatible types when precision and output contracts matter. Test maximum lengths and numeric boundaries.
NULL in User-Defined Functions
A UDF contract should state whether NULL input propagates, receives a default, returns a status, or raises an error. A strict function may return NULL without executing when a required argument is NULL, while another function may handle it explicitly.
Do not hide missing inputs inside a UDF unless that fallback is part of the documented business rule. Test each nullable parameter independently and in combination.
NULL Handling and Indexes
IS NULL and IS NOT NULL may use an index depending on the database, data distribution, index design, and statistics. Wrapping an indexed column in COALESCE can make a predicate non-sargable.
-- Potentially less index-friendly
WHERE COALESCE(status, 'UNKNOWN') = 'ACTIVE'
-- Clearer when only active rows are required
WHERE status = 'ACTIVE'
For logic that truly treats NULL as a category, consider an expression index or generated column only after measuring write and storage costs.
Unique Constraints and NULL
Database products differ in how unique constraints and indexes treat multiple NULL values. Some allow many NULLs, while particular index forms, settings, or products use different semantics.
Do not assume that a unique nullable email column enforces the same rule everywhere. Define whether multiple missing values are allowed and test the target platform's behavior.
CHECK Constraints and UNKNOWN
A CHECK constraint commonly rejects FALSE but permits TRUE or UNKNOWN. Therefore, CHECK (salary > 0) may allow NULL salary unless the column is also NOT NULL.
salary DECIMAL(12, 2) NOT NULL,
CONSTRAINT ck_employee_salary_positive CHECK (salary > 0)
Use NOT NULL to require presence and CHECK to validate known values. Combining them makes intent explicit.
SQL NULL vs JSON null
When SQL works with JSON, several states may exist: the SQL column is NULL, a JSON path is missing, the JSON value is null, or the JSON value is an empty string. Functions can return different results for each state.
Test all states and define the API mapping. Converting them all to one SQL NULL may simplify queries but can lose information needed for patch operations or validation.
NULL at API and Reporting Boundaries
An API schema may distinguish a missing property from a property explicitly set to null. Reports may display blank, N/A, Unknown, or Not Applicable. These are presentation choices, not replacements for the stored semantic state.
Document mappings at the boundary. Avoid converting NULL to a magic string in core queries when downstream consumers need typed values.
Data Modeling and Missing-Value Reasons
A nullable column captures absence but not always its cause. If unknown, not applicable, withheld, and pending have different workflows, add a constrained status or reason column.
Avoid magic numeric or text sentinels such as -1, 9999-12-31, or UNKNOWN when they can collide with valid values or contaminate aggregates. Use NULL plus explicit metadata when the reason matters.
Real-World Employee Example
An employee report can show both bonus value and completeness.
SELECT employee_id,
bonus,
CASE WHEN bonus IS NULL THEN 'MISSING' ELSE 'RECORDED' END
AS bonus_status,
COALESCE(bonus, 0) AS displayed_bonus
FROM employee;
The separate status prevents the displayed zero from hiding missing data.
Real-World Customer Example
A customer contact query can select the first available phone number and indicate its source.
SELECT customer_id,
COALESCE(mobile_phone, home_phone, work_phone) AS contact_phone,
CASE
WHEN mobile_phone IS NOT NULL THEN 'MOBILE'
WHEN home_phone IS NOT NULL THEN 'HOME'
WHEN work_phone IS NOT NULL THEN 'WORK'
ELSE 'NONE'
END AS phone_source
FROM customer;
Real-World Reporting Example
A reliable metric can publish total rows, known values, missing values, total known amount, and average known amount together.
SELECT COUNT(*) AS all_rows,
COUNT(amount) AS known_amounts,
COUNT(*) - COUNT(amount) AS missing_amounts,
SUM(amount) AS total_known_amount,
AVG(amount) AS average_known_amount
FROM payment_event;
This gives consumers the context needed to judge metric completeness.
NULL in Window Functions
Window aggregates generally follow the same NULL rules as ordinary aggregates while preserving detail rows. Ranking and navigation functions introduce additional choices about NULL ordering and missing preceding or following rows.
SELECT employee_id,
bonus,
AVG(bonus) OVER (PARTITION BY department_id) AS department_average,
LAG(bonus) OVER (
PARTITION BY department_id
ORDER BY effective_date
) AS previous_bonus
FROM employee_bonus_history;
LAG can return NULL because no previous row exists or because the previous row's bonus is NULL. A report that needs to distinguish those cases requires additional row-position logic.
NULL in Set Operations
UNION, INTERSECT, and EXCEPT compare complete rows using set-operation rules that treat duplicate NULL-containing rows for elimination purposes. This differs from ordinary equality predicates, where NULL comparisons are UNKNOWN.
Do not infer join behavior from UNION behavior. Test nullable columns explicitly when set operations reconcile data from different systems, especially when empty strings or sentinel values are normalized differently.
NULL in INSERT and UPDATE
Writing NULL may intentionally clear an optional value, while omitting a column may invoke a default. These actions are not always equivalent. An UPDATE that sets a column to NULL changes stored state; COALESCE in a SELECT changes only the returned result.
UPDATE customer
SET secondary_phone = NULL
WHERE customer_id = :customer_id;
APIs should distinguish no change from clear this value. Patch semantics often use a missing property for no change and explicit null for clearing, but the contract must state that behavior.
Defaults and NULL
A column default is normally used when an INSERT omits the column or explicitly requests DEFAULT. Supplying NULL usually stores NULL when the column permits it; the default does not automatically replace every NULL.
If every row requires a value, use NOT NULL with an appropriate default or require the caller to provide one. Do not rely on application convention alone for a database invariant.
Temporal NULL Values
A NULL end date often represents an open-ended period, active record, or unknown completion time. Replacing it with a far-future magic date simplifies some comparisons but can pollute date calculations and expose a false business value.
SELECT contract_id
FROM contract
WHERE start_date <= :as_of_date
AND (end_date > :as_of_date OR end_date IS NULL);
This query makes open-ended semantics explicit. For complex temporal models, exclusion constraints, range types, or effective-dated tables may provide stronger guarantees.
Data Migration and NULL Normalization
Legacy systems often use empty strings, zeros, spaces, N/A, unknown dates, or special numbers to represent missing values. A migration should map only confirmed sentinels to NULL and preserve legitimate values.
Profile source frequencies, review domain rules, retain rejected records, and reconcile counts before and after conversion. A broad NULLIF or CASE expression can silently erase meaningful data when the sentinel assumption is wrong.
Privacy, Redaction, and Withheld Values
A NULL value can mean data was never collected, but it can also mean data was intentionally withheld or redacted. Those meanings are operationally different. A privacy-sensitive design may need a status such as NOT_COLLECTED, WITHHELD, DELETED, or RESTRICTED beside the nullable value.
Do not use COALESCE to reveal another fallback field when policy forbids disclosure. Authorization and purpose limitation must be applied before fallback selection.
Data-Quality Monitoring
Missingness should be measurable. Track NULL counts and rates by source, tenant, field, and time period so ingestion regressions are detected quickly.
SELECT CAST(created_at AS DATE) AS created_date,
COUNT(*) AS row_count,
SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS missing_email,
SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS missing_phone
FROM customer
GROUP BY CAST(created_at AS DATE);
Alerts need expected thresholds and context because a legitimate product change can alter NULL rates. Completeness metrics should use the same authorization and row scope as the process being monitored.
Testing NULL Behavior
Test no rows, one row, all NULL, mixed NULL and known values, zero, empty strings, whitespace, boundary numbers, invalid conversion, missing join matches, and nullable grouping keys. Verify values and return types.
Include product-specific tests for concatenation, unique constraints, NULL ordering, JSON null, and NULL-safe equality. Performance tests should compare function-wrapped predicates with direct indexed predicates.
Common Mistakes
- Using
= NULLor<> NULLinstead of IS NULL or IS NOT NULL. - Treating NULL as zero, empty text, or FALSE without a business rule.
- Ignoring NULL propagation in arithmetic and nested functions.
- Using COUNT(column) when every row should count.
- Using AVG(COALESCE(value, 0)) without accepting the denominator change.
- Hiding incomplete data with a convenient fallback.
- Using magic sentinel values instead of modeled missingness.
- Forcing NULL join keys to match through one shared fallback.
- Using NOT IN without considering NULL in the subquery.
- Assuming every DBMS treats empty strings, sorting, and uniqueness identically.
Best-Practice Checklist
- Define what NULL means for every nullable field.
- Use IS NULL and IS NOT NULL for portable checks.
- Preserve NULL when unknown differs from zero or empty.
- Use COALESCE only with a valid business fallback.
- Use NULLIF for meaningful sentinel conversion and safe division.
- Choose COUNT and AVG denominators deliberately.
- Keep predicates index-friendly where possible.
- Model missing-value reasons when workflows distinguish them.
- Document API, JSON, and report mappings.
- Test NULL at every expression, join, aggregate, and boundary.
Interview Questions and Answers
What is NULL? It represents an absent or unknown value, not zero or an empty string.
How do you check for NULL? Use IS NULL or IS NOT NULL.
What does COALESCE do? It returns the first non-NULL expression from its argument list.
What does NULLIF do? It returns NULL when two expressions are equal; otherwise, it returns the first expression.
How do COALESCE and NULLIF differ? COALESCE selects a non-NULL fallback, while NULLIF can turn a matching value into NULL.
Does COUNT(*) ignore NULL? No. It counts rows. COUNT(column) ignores NULL values in that expression.
Does AVG ignore NULL? Yes. It averages non-NULL inputs and uses their count as the denominator.
What happens to NULL in arithmetic? The result is normally NULL because the numeric input is unknown.
Why use NULLIF in division? It converts a zero denominator to NULL and avoids division-by-zero failure.
Why can NOT IN be dangerous with NULL? A NULL in the comparison set can make the predicate UNKNOWN for rows that appear unrelated.
Key Takeaway
NULL handling is not merely a syntax issue. It defines how a database represents missing knowledge and how that uncertainty flows through functions, arithmetic, comparisons, joins, aggregates, constraints, APIs, and reports.
RELIABLE NULL HANDLING = DEFINED BUSINESS MEANING
+ EXPLICIT IS NULL LOGIC
+ INTENTIONAL FALLBACKS
+ CORRECT AGGREGATE DENOMINATORS
+ INDEX-AWARE PREDICATES
+ COMPLETE EDGE-CASE TESTING
Preserve NULL when it communicates real uncertainty, replace it only when a valid fallback exists, and expose completeness so consumers can interpret the result correctly.