NULLIF for sentinel values

Concepts covered: Null Substitution

Turning a chosen value into NULL

NULLIF(a, b) compares a with b. When they are equal it returns NULL; otherwise it returns a unchanged. As a CASE it reads CASE WHEN a = b THEN NULL ELSE a END. It is the tool for the sentinels of the first section: name the code, and every row holding it becomes a true missing value that aggregates skip, COUNT(column) leaves out and IS NULL finds.

SELECT
  user_id,
  age,
  NULLIF(age, 0) AS clean_age
FROM legacy_users

Users 2 and 4 held the sentinel 0 and now hold NULL in clean_age; users 1, 3 and 5 pass through. The raw age column stays beside it, so each row records both what was stored and what was known.

The accounts.status column holds, row by row: 'active', 'closed', NULL, 'Active'. What does this query return, row by row?

SELECT NULLIF(status, 'active') AS status_flag
FROM accounts

• NULL, 'closed', NULL, 'Active'
• NULL, 'closed', NULL, NULL
• 'active', 'closed', NULL, 'Active'
• NULL, NULL, NULL, NULL

Repairing an average

Back to legacy_users, where the first section showed AVG(age) reporting 21.2 because of 2 fake zeros. NULLIF inside the aggregate repairs the number without filtering a single row out of the query.

SELECT
  AVG(age) AS avg_with_zeros,
  AVG(NULLIF(age, 0)) AS avg_known_ages,
  COUNT(age) AS rows_with_age,
  COUNT(NULLIF(age, 0)) AS rows_with_known_age
FROM legacy_users

Same 5 rows, and NULLIF restores the figures the WHERE filter found, 35.3 and 3, while every row stays in play. WHERE age > 0 would drop the 2 users entirely, with their cities, orders and signup dates; NULLIF blanks one field and keeps the rest of the row.

The one-value limit

NULLIF names exactly 1 value. A column with 2 sentinels needs 2 nested calls, and a column with 3 or more reads better as a CASE with IN, which the filtering lessons already gave you. A range of impossible values, such as an age below 0 or above 120, also belongs in a CASE, since NULLIF compares for equality and nothing else.

SELECT
  user_id,
  NULLIF(NULLIF(country, 'N/A'), 'unknown') AS country_nested,
  CASE
    WHEN country IN ('N/A', 'unknown', '') THEN NULL
    ELSE country
  END AS country_case
FROM users

The nested form evaluates inside out: the inner NULLIF clears 'N/A', and the outer one receives either a country or a NULL and clears 'unknown'. A NULL reaching the outer call stays NULL, since NULL = 'unknown' is never TRUE.

Division by zero

Dividing by 0 raises an error and stops the whole query. A column of counts holds 0 for every group with nothing to count, so a rate computed from it fails on the first empty group. NULLIF on the divisor turns the 0 into NULL, and anything divided by NULL is NULL, which is the right rate for a campaign nobody saw. The CAST to DOUBLE stops integer division from rounding 50 / 1000 down to 0.

SELECT
  campaign_id,
  clicks,
  impressions,
  CAST(clicks AS DOUBLE) / NULLIF(impressions, 0) AS click_rate
FROM campaigns

CMP-A and CMP-C return 0.05 and 0.06. CMP-B returns NULL in place of an error, and the other 2 rows arrive intact. An AVG over click_rate skips CMP-B and reports 0.055, the average rate among campaigns that were shown.

About this lesson

This section is part of the NULL Values: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.