IFNULL vs NULLIF in GBase 8a: Why They Are Not Interchangeable

Both IFNULL and NULLIF accept two arguments, but they perform opposite operations. Knowing the difference is key to avoiding subtle data errors.

Function Comparison

• IFNULL converts NULL → value
• NULLIF converts value → NULL

Why NULLIF Cannot Replace IFNULL

Suppose a country column contains NULLs and you want to show 'Unknown' for those rows.

• Correct usage:

  SELECT IFNULL(country, 'Unknown') FROM worldcup;

Result: NULL → 'Unknown'; 'China' stays 'China'.

• Incorrect swap:

  SELECT NULLIF(country, 'Unknown') FROM worldcup;

Logic: If country = 'Unknown', return NULL. The original NULL remains NULL, and any row actually holding 'Unknown' is turned into NULL — completely breaking the requirement.

They solve opposite problems, so one cannot substitute for the other.

When to Use NULLIF

NULLIF is a conditional nullifier. Typical use cases:

• Prevent division by zero:

  SELECT income / NULLIF(month_count, 0) FROM salary;

• Treat placeholders as NULL:

  SELECT NULLIF(customer_feedback, 'N/A') FROM survey;

• Standardize data:

  UPDATE products SET price = NULLIF(price, -1);

CASE Equivalents

• IFNULL:

  CASE WHEN expr1 IS NULL THEN expr2 ELSE expr1 END

• NULLIF:

  CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Summary

• IFNULL replaces NULLs; NULLIF creates NULLs. Their logic is the exact reverse.
• Handle missing data with IFNULL/NVL; turn specific values into NULL with NULLIF.
• Choose based on the business requirement, not surface‑level similarity.