Introduction to MySQL NULLIF function

The NULLIF function is one of the control flow functions that accepts 2 arguments. The NULLIF function returns NULL if the first argument is equal to the second argument, otherwise it returns the first argument.

The syntax of the NULLIF function is as follows:

NULLIF(expression_1,expression_2);Code language: SQL (Structured Query Language) (sql)

The NULLIF function returns NULL if expression_1 = expression_2 is true , otherwise it returns expression_1

🧠 MySQL NULLIF() Function – Study Notes

📌 1. What is NULLIF()?

The NULLIF() function is one of MySQL's control flow functions, and it plays a very specific role:

• It compares two values.
• If the values are equal, it returns NULL.
• If they are not equal, it returns the first value.

✅ Primary Use Case:

To prevent division by zero errors or to handle cases where equality should result in a NULL.

🔍 2. Syntax and Explanation

NULLIF(expression_1, expression_2)

🔎 Detailed Breakdown:

⚙️ Logic:

• If expression_1 = expression_2 → Returns NULL
• Else → Returns expression_1

💡 3. Why Use NULLIF()?

• To safely avoid division by zero errors in calculations
• To replace matched values with NULLs during comparison
• To simplify code instead of writing longer CASE statements

🧾 Equivalent CASE Statement:

CASE
    WHEN expression_1 = expression_2 THEN NULL
    ELSE expression_1
END;

🧪 4. Practical Examples

🔹 Example 1: Basic Usage

SELECT NULLIF(10, 10);
-- Returns: NULL

SELECT NULLIF(10, 5);
-- Returns: 10 (not equal)

🔹 Example 2: With Strings

SELECT NULLIF('data', 'data');
-- Returns: NULL

SELECT NULLIF('data', 'science');
-- Returns: 'data'

🔹 Example 3: Involving NULL

SELECT NULLIF(1, NULL);
-- Returns: 1 (NULL is not equal to 1)

SELECT NULLIF(NULL, 1);
-- Returns: NULL (returns expression_1, which is NULL)

🚫 5. Handling Division by Zero

❌ Problem Query (Will Throw Error):

SELECT 100 / 0;

If the MySQL server has ERROR_FOR_DIVISION_BY_ZERO mode enabled, the above will throw an error.

✅ Safe Query Using NULLIF():

SELECT 100 / NULLIF(0, 0);
-- Returns: NULL

Since 0 = 0, NULLIF(0, 0) returns NULL, and thus 100 divided by NULL is safely handled as NULL (no crash or error).

📊 6. Real Example with a Table: Orders in June 2003

Suppose you have an orders table:

You want to calculate:

Number of shipped orders ÷ number of cancelled orders

❌ Query That May Fail:

SELECT
  SUM(IF(status = 'Shipped', 1, 0)) /
  SUM(IF(status = 'Cancelled', 1, 0))
FROM orders
WHERE orderDate BETWEEN '2003-06-01' AND '2003-06-30';

• If there are no 'Cancelled' orders, the denominator becomes 0 → causes error.

✅ Safe Query with NULLIF():

SELECT
  SUM(IF(status = 'Shipped', 1, 0)) /
  NULLIF(SUM(IF(status = 'Cancelled', 1, 0)), 0)
FROM orders
WHERE orderDate BETWEEN '2003-06-01' AND '2003-06-30';

• If cancelled count = 0, NULLIF() returns NULL, and the division result is safely returned as NULL.

💼 7. Finance-Specific Example

Scenario:

You’re analysing Net Revenue per Paying Customer. But in some months, there are no paying customers recorded.

❌ Risky Calculation:

SELECT SUM(revenue) / SUM(paying_customers) FROM monthly_sales;

If paying_customers = 0, this will fail.

✅ Safe Calculation with NULLIF():

SELECT SUM(revenue) / NULLIF(SUM(paying_customers), 0)
FROM monthly_sales;

This ensures division by zero is prevented, and you’ll get NULL instead of a crash.

🧠 8. Summary Table

🔚 Conclusion

• NULLIF() is simple but powerful.
• It protects against errors, especially division by zero.
• It simplifies equality checks without complex CASE statements.
• Ideal for financial and data quality checks where values may be missing or matched.