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

Function	Syntax	Purpose	Logical Equivalent
IFNULL	IFNULL(expr1, expr2)	Replace NULL: if expr1 is NULL, return expr2; otherwise return expr1.	CASE WHEN expr1 IS NULL THEN expr2 ELSE expr1 END
NULLIF	NULLIF(expr1, expr2)	Nullify equality: if expr1 equals expr2, return NULL; otherwise return expr1.	CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

• 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.