SQL

NULLIF in SQL

🔄 The NULLIF() function compares two expressions and returns NULL if they are equal. If the two expressions are different, it returns the value of the first expression. NULLIF() is commonly used to avoid divide-by-zero errors, normalize data, and simplify conditional logic.

📖 What is NULLIF()?

NULLIF() evaluates two expressions. If both expressions have the same value, the result is NULL. Otherwise, the first expression is returned unchanged.

Information

NULLIF() is part of the ANSI SQL standard and is supported by most major relational database systems.

🎯 Why Use NULLIF()?

NULLIF() is useful when a specific value should be treated as missing or when preventing runtime errors in calculations.

📌 Prevent divide-by-zero errors.
📌 Convert specific values into NULL.
📌 Simplify conditional expressions.
📌 Normalize imported or inconsistent data.
📌 Improve query readability.

📋 Sample Table

ProductID	ProductName	TotalSales	TotalOrders
101	Laptop	50000	25
102	Keyboard	10000	0
103	Mouse	15000	30

📝 Basic Syntax

NULLIF() Syntax

NULLIF(expression1, expression2)

If expression1 equals expression2, the function returns NULL. Otherwise, it returns expression1.

💡 Example: Basic Comparison

Simple NULLIF() Example

SELECT NULLIF(10, 10) AS Result;

Since both values are equal, the result is NULL.

Different Values

SELECT NULLIF(10, 20) AS Result;

Since the values are different, the result is 10.

💡 Example: Prevent Divide-by-Zero

One of the most common uses of NULLIF() is avoiding division by zero.

Safe Division

SELECT
    ProductName,
    TotalSales / NULLIF(TotalOrders, 0) AS AverageSale
FROM Products;

If TotalOrders is 0, NULLIF() returns NULL, preventing a divide-by-zero error. The result of the division is typically NULL instead of causing the query to fail.

💡 Example: Convert Empty Values to NULL

NULLIF() can convert a specific value into NULL.

Convert Empty String to NULL

SELECT
    EmployeeName,
    NULLIF(PhoneNumber, '') AS PhoneNumber
FROM Employees;

Empty strings become NULL, while valid phone numbers remain unchanged.

📊 Example Output

ProductName	AverageSale
Laptop	2000
Keyboard	NULL
Mouse	500

⚖️ NULLIF() vs COALESCE()

Feature	NULLIF()	COALESCE()
Purpose	Returns NULL if two values are equal.	Returns the first non-NULL value.
Arguments	Exactly 2	2 or more
Typical Use	Conditional NULL conversion.	Replace NULL values.
SQL Standard	✅ Yes	✅ Yes

⚖️ NULLIF() vs CASE

NULLIF() is functionally equivalent to a simple CASE expression.

Equivalent CASE Expression

CASE
    WHEN expression1 = expression2 THEN NULL
    ELSE expression1
END

NULLIF() offers a shorter and more readable way to express this logic.

💼 Real-World Examples

💰 Prevent divide-by-zero errors in financial reports.
📊 Convert placeholder values such as 0 or empty strings into NULL.
📦 Normalize imported datasets.
📈 Improve statistical calculations that should ignore certain values.
📝 Simplify SQL expressions compared to using a full CASE statement.

🗄️ Database Compatibility

Database System	NULLIF() Support
MySQL	✅ Fully supported.
PostgreSQL	✅ Fully supported.
SQL Server	✅ Fully supported.
Oracle	✅ Fully supported.
SQLite	✅ Fully supported.

⚠️ Common Mistakes

❌ Expecting NULLIF() to replace every NULL value automatically.
❌ Passing incompatible data types as arguments.
❌ Forgetting that the function returns the first expression when values differ.
❌ Using NULLIF() where COALESCE() or CASE is more appropriate.

Warning

Ensure both expressions are compatible data types. The comparison follows your database's type conversion rules, which may affect the result.

⚠️ Best Practices

Best Practice

Use NULLIF() to prevent divide-by-zero errors, normalize placeholder values into NULL, keep comparisons simple, and combine it with COALESCE() when you need to replace the resulting NULL with a default value.

🚀 Key Points to Remember

📌 NULLIF() compares two expressions.
📌 It returns NULL when both expressions are equal.
📌 Otherwise, it returns the first expression.
📌 It is commonly used to prevent divide-by-zero errors.
📌 It is part of the ANSI SQL standard.
📌 It is supported by most major relational database systems.

>>"NULLIF turns specific values into NULL, making SQL expressions safer and easier to manage."

Summary

✅ The NULLIF() function compares two expressions and returns NULL when they are equal. It is especially useful for preventing divide-by-zero errors, converting placeholder values into NULL, and writing cleaner conditional logic. As an ANSI SQL standard function, it offers excellent portability across major database systems.