NULL Values: Advanced

Handle gaps in ordered calculations

Window functions have specific behaviors with NULL values that affect sorting, partitioning, and calculations. Understanding these behaviors is critical for accurate analytics.

Window functions frequently use ORDER BY and PARTITION BY clauses where NULL values create specific behaviors you need to understand.

When using ORDER BY within window functions, NULL values have a default sort position that varies by database. You can control placement explicitly with NULLS FIRST or NULLS LAST:

With NULLS LAST, employees with unknown salaries appear at the end of the ranking. Without this clause, their position depends on database defaults.

When partitioning, all NULL values in the partition column are grouped together into a single partition:

E-104 and E-105 (both with NULL department) are ranked together in their own partition. This might be useful for ranking unassigned employees, but it's often unexpected.

Window functions that perform calculations or navigate between rows handle NULLs consistently with their non-window counterparts, but the results can still surprise you.

Window aggregate functions (SUM, AVG, COUNT) ignore NULL values in calculations, just like regular aggregates.

The moving average for 2024-03 is (100 + 150) / 2 = 125, not (100 + 0 + 150) / 3 = 83.33. The NULL in February is excluded from both the sum and the count.

LAG and LEAD can return NULL in two situations: when reaching the edge of the partition, or when the referenced value is actually NULL:

Compare what happens when LAG hits a NULL value versus wrapping it with COALESCE:

SELECT
  metric_id,
  RANK() OVER (
    ORDER BY ___ ___ ___
  ) AS rank
FROM employee_metrics

Specifying NULLS FIRST or NULLS LAST explicitly is one of those small habits that prevents an entire class of subtle ranking bugs that only surface when production data contains incomplete records.

In window function partitioning, the fact that all NULLs land in one shared partition is usually unintended behavior, so checking whether your partition column contains NULLs before deploying a windowed query is a worthwhile precaution.

Wrapping LAG and LEAD with COALESCE is a defensive practice that handles both the partition-edge case and the case where the referenced value is genuinely missing, preventing NULL from propagating into downstream calculations.

Merge result sets with missing values

UNION and UNION ALL treat NULL values differently when deduplicating rows. Understanding this behavior is crucial for combining datasets correctly.

UNION operations use a special comparison for deduplication that differs from standard equality comparisons.

UNION (without ALL) removes duplicate rows. For deduplication purposes, NULL values in the same column position are considered equal:

If both tables have a row ('Charlie', NULL), UNION returns only one copy. The deduplication treats these NULLs as matching, even though NULL = NULL is UNKNOWN in comparisons.

UNION ALL preserves all rows including duplicates, which includes rows with NULL values.

UNION ALL keeps both copies of ('Charlie', NULL). Use it when you want to preserve all rows including duplicates with NULL values.

Which version keeps duplicate NULL rows? Build the query:

SELECT
  region
FROM orders

___ ___

SELECT
  region
FROM cloud_costs

The UNION deduplication behavior for NULLs is one of the few places in SQL where NULLs are treated as equal to each other, which is the opposite of how they behave in WHERE and JOIN conditions.

Choosing between UNION and UNION ALL is a correctness decision, not just a performance one: if you use UNION when you actually need UNION ALL, you will silently lose rows that happen to be identical including their NULL values.

In most data pipeline contexts, UNION ALL is the safer default because it preserves all rows without the overhead of deduplication, and you can always add a subsequent DISTINCT or GROUP BY if deduplication is truly needed.

Deduplicate when NULLs are present

SELECT DISTINCT treats all NULL values as equal, returning only one NULL in the result. This differs from WHERE and JOIN behavior.

Unlike comparisons in WHERE clauses, DISTINCT considers all NULL values to be the same.

DISTINCT and GROUP BY treat all NULLs as equal, collapsing them into a single group.

Even if 10 employees have NULL department, DISTINCT returns only one NULL row.

Different SQL contexts treat NULL equality differently. This table summarizes the key differences.

SQL has inconsistent rules for NULL equality depending on the operation being performed.

Does COUNT(DISTINCT col) include NULL as a distinct value? Compare the approaches:

SELECT ___
  ___
FROM orders

Understanding that DISTINCT collapses all NULLs into one row is essential for writing accurate distinct-count queries, because COUNT(DISTINCT col) will exclude NULLs entirely even though SELECT DISTINCT would include one NULL row.

The inconsistency between WHERE behavior (NULL never equals NULL) and DISTINCT/GROUP BY behavior (NULLs are equal) is one of the most confusing aspects of SQL, but it reflects a deliberate design decision to make grouping operations predictable.

When writing queries that combine DISTINCT with nullable columns, always verify which context you are in and whether the NULL handling matches the business requirement you are trying to satisfy.

Trace how one NULL spreads through expressions

NULL is "contagious" in SQL: most operations involving NULL produce NULL. Understanding propagation rules helps you predict query results and avoid subtle bugs.

Arithmetic and string operations follow consistent rules: if any operand is NULL, the result is NULL.

Any arithmetic operation with NULL yields NULL, regardless of the other operand:

Even though P-002 has a valid price, the NULL discount causes the entire calculation to return NULL.

String concatenation with NULL also produces NULL in standard SQL:

Jane's full name becomes NULL because her middle name is NULL. Use COALESCE to provide a default value (like an empty string) for any NULL components, preventing the entire concatenation from collapsing.

How can you prevent NULL from breaking an arithmetic expression? Toggle to compare:

CASE expressions and functions have more complex NULL handling that depends on which parts of the expression contain NULLs.

CASE statements have nuanced NULL behavior. The WHEN condition uses standard comparison (where NULL = anything is UNKNOWN), but THEN/ELSE can return NULL:

We use the searched CASE form with IS NULL to detect NULL. The simple form (CASE status WHEN NULL) would never match because status = NULL evaluates to UNKNOWN.

Most scalar functions propagate NULL, but aggregate functions ignore it:

SELECT
  order_id,
  price - ___(discount, ___) AS final_price
FROM orders

Any arithmetic operation with NULL produces NULL. This propagation is the most common source of unexpected NULLs in query results.

Scalar functions like UPPER, LENGTH, and ROUND also propagate NULL. Only aggregate functions like SUM and COUNT handle NULLs by ignoring them.

When debugging unexpected NULLs, check each column in your expression for potential NULL values and add COALESCE where needed.

Avoid the NOT IN trap with NULL values

When NULL is involved in comparisons, the result is UNKNOWN. Here's how AND and OR behave:

Key insight: TRUE AND UNKNOWN = UNKNOWN (not TRUE), but FALSE AND UNKNOWN = FALSE. Similarly, TRUE OR UNKNOWN = TRUE, but FALSE OR UNKNOWN = UNKNOWN.

One of the most dangerous NULL pitfalls is using NOT IN with a subquery that might contain NULL:

If cost_allocs contains even ONE NULL department_id, this query returns NO ROWS. NOT IN expands to: dept_id != 1 AND dept_id != 2 AND dept_id != NULL.

Since anything != NULL is UNKNOWN, the entire AND chain becomes UNKNOWN, and no rows pass the filter.

In classical logic, NOT (A AND B) = (NOT A) OR (NOT B). But with three-valued logic, these queries are NOT equivalent when columns can be NULL:

If status is NULL, Query 1 might include a row while Query 2 excludes it. The != comparison with NULL yields UNKNOWN, changing how rows are filtered.

In classical logic, A OR NOT A is always TRUE. In SQL, this fails when A involves NULL:

For E-003: status = 'active' is UNKNOWN, and status != 'active' is also UNKNOWN. UNKNOWN OR UNKNOWN = UNKNOWN, so the ELSE branch executes.

SELECT SUM(settlement_amt) AS total
FROM claims
WHERE status = 'settled'
-- Returns: $18.4M
-- Actual liability: $20.5M
-- Missing: $2.1M in NULL rows

The NOT IN trap is one of the most dangerous pitfalls with NULL because it silently returns zero rows rather than throwing an error.

Three-valued logic means you can never substitute != NULL for IS NOT NULL: the comparison always returns UNKNOWN, excluding the row from any WHERE filter.

Developing a habit of reviewing whether subqueries in NOT IN can return NULL before using them in production is one of the most valuable defensive practices.

SELECT
  *
FROM orders
WHERE ___ ___
___ profit > 0

The NOT IN trap is one of the most dangerous NULL pitfalls in SQL because it silently returns zero rows rather than throwing an error, making it extremely difficult to debug without specifically knowing to look for NULLs in the subquery.

Three-valued logic is the reason why you can never substitute != NULL for IS NOT NULL: the comparison always returns UNKNOWN, which means the row will be excluded from any WHERE filter regardless of the actual value.

Developing a habit of reviewing whether subqueries in NOT IN or comparison conditions can return NULL before using them in production is one of the most valuable defensive practices for writing reliable SQL.