DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

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

#gbase #database #数据库

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;
Enter fullscreen mode Exit fullscreen mode

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

  • Incorrect swap: 
  SELECT NULLIF(country, 'Unknown') FROM worldcup;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • Treat placeholders as NULL: 
  SELECT NULLIF(customer_feedback, 'N/A') FROM survey;
Enter fullscreen mode Exit fullscreen mode
  • Standardize data: 
  UPDATE products SET price = NULLIF(price, -1);
Enter fullscreen mode Exit fullscreen mode

CASE Equivalents

  • IFNULL: 
  CASE WHEN expr1 IS NULL THEN expr2 ELSE expr1 END
Enter fullscreen mode Exit fullscreen mode
  • NULLIF: 
  CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)