🔄 SQL NULLIF() Function – Return NULL When Two Values Match

If you’re working with comparisons and conditional logic in SQL, you might’ve wondered:
“How can I return NULL only if two values are equal?”
That’s exactly where the SQL NULLIF() function comes into play.

In this tutorial, you’ll learn how the NULLIF() function works, when to use it, and how it compares to other conditional functions in SQL like CASE and COALESCE.

🚀 What is SQL NULLIF()?

The NULLIF() function compares two expressions and:

Returns NULL if both expressions are equal
Returns the first expression if they are not equal

📌 Syntax:

NULLIF(expression1, expression2)

✅ SQL NULLIF() Explained with Examples

📊 Example 1: Avoid Division by Zero

SELECT 
  revenue / NULLIF(quantity, 0) AS avg_price
FROM sales;

🔍 Why it matters:
When quantity = 0, NULLIF(quantity, 0) returns NULL, preventing a division-by-zero error.

⚖️ Example 2: Return NULL When Values Match

SELECT 
  employee_id,
  NULLIF(salary, bonus) AS net_difference
FROM employees;

🧠 What it does:
If salary = bonus, it returns NULL. Otherwise, it returns the salary.

📈 Example 3: Use with CASE for Cleaner Logic

SELECT 
  CASE 
    WHEN salary = bonus THEN NULL
    ELSE salary
  END AS adjusted_salary
FROM payroll;

🆚 This is the long-form equivalent of:

SELECT NULLIF(salary, bonus) AS adjusted_salary
FROM payroll;

🧠 When Should You Use SQL NULLIF?

Use NULLIF() when:

You want to avoid division by zero
You need conditional NULLing of values
You’re optimizing complex CASE statements
You want simpler, cleaner code for conditional comparisons

⚠️ Things to Keep in Mind

NULLIF() returns NULL only if both expressions are equal
If the first expression is NULL and second is not, the result is NULL (since NULL = anything is always NULL)
Both arguments should be of comparable data types

🔁 NULLIF vs COALESCE vs CASE

Function	Use Case	Returns When…
`NULLIF()`	Returns NULL if two values are equal	Inputs match
`COALESCE()`	Returns first non-NULL value from a list	First value that isn’t NULL
`CASE`	Custom conditional logic	Based on WHEN conditions

🧠 Real-world Use Case: Avoid Zero Division in Financial Reports

SELECT 
  department,
  total_revenue / NULLIF(total_orders, 0) AS revenue_per_order
FROM department_summary;

✅ This avoids the “division by zero” SQL error, and keeps your reports clean and safe.

NULLIF Function