SQL COALESCE Function: NULL Handling for Data Engineers

NULL represents unknown. Comparing two unknowns does not produce TRUE; it produces NULL. This means WHERE col = NULL returns zero rows. You must use WHERE col IS NULL. This is the single most common NULL mistake in interviews, and interviewers test it deliberately.

-- Wrong: returns no rows even if col has NULLs
SELECT * FROM users WHERE email = NULL;

-- Correct: use IS NULL
SELECT * FROM users WHERE email IS NULL;

Any arithmetic operation involving NULL returns NULL. price * NULL is NULL. revenue + NULL is NULL. This is how dirty data silently corrupts aggregations. A single NULL discount in a pricing table can make an entire calculated column NULL if you do not handle it.

-- If discount is NULL, total becomes NULL
SELECT price * (1 - discount) AS total FROM products;

-- Fix: COALESCE discount to 0
SELECT price * (1 - COALESCE(discount, 0)) AS total FROM products;

SUM, AVG, MIN, MAX all skip NULL values. COUNT(*) counts all rows. COUNT(column) counts non-NULL values only. The danger: AVG ignores NULLs, which changes the denominator. If 30 of 100 rows have NULL salary, AVG(salary) divides by 70, not 100.

-- These return different results if salary has NULLs
SELECT COUNT(*) FROM employees;         -- all rows
SELECT COUNT(salary) FROM employees;    -- non-NULL only
SELECT AVG(salary) FROM employees;      -- average of non-NULL only

If a NOT IN subquery returns any NULL value, the entire NOT IN predicate returns no rows. This is because NOT IN checks every value, and comparing anything to NULL yields NULL (not FALSE). Use NOT EXISTS instead, which handles NULLs correctly.

-- Dangerous: if subquery returns any NULL, zero rows come back
SELECT * FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- Safe: NOT EXISTS handles NULLs correctly
SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees m WHERE m.manager_id = e.id
);

Replace NULL with a meaningful default. This is the most common COALESCE pattern. Any nullable column that feeds into a calculation, display, or downstream system should be wrapped in COALESCE at the transformation layer.

SELECT
  user_id,
  COALESCE(display_name, username, email, 'Anonymous') AS shown_name,
  COALESCE(phone, 'Not provided') AS phone
FROM users;

LEFT JOINs produce NULLs for every right-side column when there is no match. COALESCE provides sensible defaults so downstream queries do not break. This is the most common COALESCE pattern in data pipelines.

SELECT
  c.customer_id,
  c.name,
  COALESCE(SUM(o.amount), 0) AS total_spent,
  COALESCE(COUNT(o.order_id), 0) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.customer_id, c.name;

When merging data from multiple sources, COALESCE picks the best available value. This is common in customer data platforms and master data management pipelines where each source has different coverage.

SELECT
  user_id,
  COALESCE(crm.email, app.email, support.email) AS best_email,
  COALESCE(crm.phone, app.phone) AS best_phone
FROM users u
LEFT JOIN crm_data crm ON u.id = crm.user_id
LEFT JOIN app_data app ON u.id = app.user_id
LEFT JOIN support_data support ON u.id = support.user_id;

Division by zero crashes queries. NULLIF converts the denominator to NULL when it equals zero, and COALESCE catches the resulting NULL to provide a default. This two-function combo is a standard defensive pattern.

SELECT
  product_id,
  revenue,
  cost,
  COALESCE(revenue / NULLIF(cost, 0), 0) AS margin_ratio
FROM financials;

In MERGE/UPSERT operations, COALESCE selects whether to keep the existing value or take the new one. This preserves existing data when the incoming source has NULLs for fields it did not update.

MERGE INTO dim_customer AS target
USING staging AS source ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET
  email = COALESCE(source.email, target.email),
  phone = COALESCE(source.phone, target.phone),
  updated_at = CURRENT_TIMESTAMP;