COALESCE and NULLIF patterns

Concepts covered: Null Defaulting

Clean first, then default

The 2 functions nest. COALESCE(NULLIF(description, ''), 'No description') runs the inner call first: an empty string becomes NULL and a real description passes through. The outer call then fills every NULL, whether it arrived as NULL or came from NULLIF, with one default. The reverse nesting, NULLIF(COALESCE(description, ''), ''), turns NULL into '' and then '' into NULL, so both kinds of missing collapse into one NULL and nothing is filled.

SELECT
  product_id,
  description,
  COALESCE(NULLIF(description, ''), 'No description') AS display_text,
  NULLIF(COALESCE(description, ''), '') AS normalized
FROM products

P-2 and P-3 arrive as '' and NULL and leave as 'No description' in display_text and as NULL in normalized. P-4 slips through both columns as 'N/A', because the query named '' as the only sentinel. A second NULLIF or a CASE with IN would catch it, once a profile has found it.

The country column holds, row by row: 'US', 'N/A', NULL, ''. What does COALESCE(NULLIF(country, 'N/A'), 'Unknown') return, row by row?

• 'US', 'Unknown', 'Unknown', 'Unknown'
• 'US', 'Unknown', 'Unknown', ''
• 'US', 'N/A', 'Unknown', ''
• 'US', NULL, NULL, ''

A safe divisor with a default

The rate from the last section returned NULL for the campaign with no impressions. A dashboard sorting campaigns by rate wants a number in every row, so COALESCE wraps the division and supplies 0, which changes any AVG computed across campaigns.

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

CMP-B reads NULL in one column and 0.0 in the other. An AVG over click_rate gives 0.055, the mean of the 2 campaigns that ran. An AVG over click_rate_or_zero gives 0.0367, because a campaign nobody saw now counts as a campaign nobody clicked. The 0 belongs in the column a sort or a display needs; the NULL belongs in the column an average will read.

Label the unmatched side of a LEFT JOIN

A LEFT JOIN marks every unmatched row with NULL on the right side, and COALESCE on a right-side column turns that mark into a label. The label covers both gaps the second section separated, a missing key and a key with no matching row; keep that section's CASE when a report needs to tell them apart.

SELECT
  e.emp_id,
  COALESCE(d.dept_name, 'Unassigned') AS dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id

E-101 keeps Sales. E-103, with no key, and E-105, pointing at a department that does not exist, both come out as Unassigned: the COALESCE runs after the join, sees NULL in dept_name for both and supplies the same text.

One query over the shipments table

The shipments table from the first section carries 2 sentinels, -1 for an unweighed parcel and 'N/A' for an unknown carrier, plus 1 true NULL carrier. The query below converts both sentinels to NULL once in a CTE, then LEFT JOINs to carriers, labels the unmatched side with COALESCE and aggregates the cleaned column.

WITH clean AS (
  SELECT
    shipment_id,
    NULLIF(carrier_code, 'N/A') AS carrier_code,
    NULLIF(weight_kg, -1) AS weight_kg
  FROM shipments
)
SELECT
  COALESCE(c.carrier_name, 'Unassigned') AS carrier,
  COUNT(*) AS shipments,
  COUNT(s.weight_kg) AS weighed,
  AVG(s.weight_kg) AS avg_weight_kg
FROM clean s
LEFT JOIN carriers c
  ON s.carrier_code = c.carrier_code
GROUP BY COALESCE(c.carrier_name, 'Unassigned')
ORDER BY shipments DESC, carrier

Unassigned holds 4 shipments: the 3 that said 'N/A' and S-10 with its true NULL, one group because the CTE made them the same value before the join. Only 1 of the 4 was weighed, at 9.0 kilograms. Fastdrop weighed 2 of its 3 parcels and averages 7.25; Northwind Express weighed all 3 for 4.67. With the raw -1 in the AVG, Fastdrop would read 4.5 and Unassigned 1.5. The weighed column beside shipments puts the missing data in the report itself.

Do

• Convert sentinels to NULL in a CTE, then join and aggregate the clean columns
• Name the default for what it means: 'Unassigned', 'No description', 'no carrier'
• Report COUNT(*) beside COUNT(clean_column) so the reader sees how much was missing
• Put NULLIF on any divisor that can hold 0, and decide separately whether the rate defaults

Avoid

• COALESCE a measure to 0 before AVG unless the report means 'missing counts as zero'
• Filter a LEFT JOIN's right-side column in WHERE without an IS NULL branch
• Swap the nesting by habit: NULLIF(COALESCE(x, ''), '') blanks and fills nothing
• Replace an old sentinel with a new one such as -1; the next reader will have to find it

In tickets, resolution_hours holds -1 for a ticket still open and team holds '' for an unassigned one. Clean both sentinels once in a CTE, then report each team's ticket count, resolved count and average resolution hours, with the empty team labelled 'Unassigned'.

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.