What is the difference between IFNULL(), NULLIF(), and COALESCE()?

Question

Accepted Answer

34
   What is the difference between IFNULL(), NULLIF(), and COALESCE()?
  Level: Intermediate — A tricky trio; NULLIF works in the opposite direction of the other two.

  These three functions all deal with NULLs, but they behave very differently. Two of them remove/replace NULLs, while one creates a NULL.

  
     IFNULL(expr, alt): If expr is NULL, returns alt; otherwise returns expr. Takes exactly 2 arguments.
     COALESCE(v1, v2, ...): Returns the first non-NULL value from a list. Takes many arguments.
     NULLIF(a, b): The opposite — returns NULL if a = b, otherwise returns a. It produces a NULL.
  

  
     Key insight: IFNULL and COALESCE eliminate NULLs (provide fallbacks), while NULLIF generates a NULL when two values are equal — commonly used to avoid "division by zero" errors.
  

   Side-by-Side Comparison
  
    
      
        
          Function
          Arguments
          Purpose
          Returns
        
      
      
        
          IFNULL(a, b)
          2
          Replace NULL with fallback
          a if not NULL, else b
        
        
          COALESCE(a, b, c...)
          Many
          First non-NULL value
          First non-NULL in list
        
        
          NULLIF(a, b)
          2
          Return NULL if equal
          NULL if a=b, else a
        
      
    
  

   Behaviour at a Glance
  
    
      
        
          Expression
          Result
        
      
      
        
          IFNULL(NULL, 'X')
          X
        
        
          IFNULL(10, 'X')
          10
        
        
          COALESCE(NULL, NULL, 5)
          5
        
        
          NULLIF(10, 10)
          NULL
        
        
          NULLIF(10, 20)
          10
        
      
    
  

   Quick Example — Practical Use of NULLIF
-- IFNULL: give a default when phone is missing
SELECT name, IFNULL(phone, 'N/A') FROM employees;

-- COALESCE: pick first available contact method
SELECT name, COALESCE(mobile, landline, email) AS contact FROM employees;

-- NULLIF: prevent division-by-zero errors
-- If total = 0, NULLIF makes it NULL, and dividing by NULL returns NULL (no error)
SELECT
    scored,
    total,
    (scored / NULLIF(total, 0)) * 100 AS percentage
FROM results;

  
     Interviewer tip: The one-liner they want — "IFNULL and COALESCE replace NULLs with fallback values, whereas NULLIF returns NULL when two values are equal — often used to safely avoid division by zero."

Function	Arguments	Purpose	Returns
IFNULL(a, b)	2	Replace NULL with fallback	a if not NULL, else b
COALESCE(a, b, c...)	Many	First non-NULL value	First non-NULL in list
NULLIF(a, b)	2	Return NULL if equal	NULL if a=b, else a

Expression	Result
`IFNULL(NULL, 'X')`	X
`IFNULL(10, 'X')`	10
`COALESCE(NULL, NULL, 5)`	5
`NULLIF(10, 10)`	NULL
`NULLIF(10, 20)`	10

What is the difference between IFNULL(), NULLIF(), and COALESCE()?

Answer with Explanation

What is the difference between IFNULL(), NULLIF(), and COALESCE()?

Side-by-Side Comparison

Behaviour at a Glance

Quick Example — Practical Use of NULLIF