How to Use NULLIF Function in MySQL: Syntax and Examples

In MySQL, the NULLIF(expr1, expr2) function returns NULL if expr1 equals expr2; otherwise, it returns expr1. It is useful to avoid division by zero or to replace specific values with NULL in queries.

Syntax

The NULLIF function takes two expressions and compares them. If both are equal, it returns NULL. If they are different, it returns the first expression.

• expr1: The first value or expression to compare.
• expr2: The second value or expression to compare against the first.

sql

NULLIF(expr1, expr2)

Example

This example shows how NULLIF returns NULL when two values are equal, and returns the first value when they differ. It also demonstrates avoiding division by zero.

sql

SELECT NULLIF(10, 10) AS result_equal, NULLIF(10, 5) AS result_not_equal;

-- Using NULLIF to avoid division by zero
SELECT 100 / NULLIF(0, 0) AS safe_division, 100 / NULLIF(5, 0) AS normal_division;

Output

result_equal | result_not_equal
------------ | ---------------
NULL         | 10

safe_division | normal_division
------------- | ---------------
NULL          | 20

Common Pitfalls

One common mistake is expecting NULLIF to return expr2 when values differ; it always returns expr1 if they are not equal. Another pitfall is not handling the NULL result properly, which can cause unexpected behavior in calculations or filters.

Also, using NULLIF with non-comparable types or complex expressions without testing can lead to errors.

sql

/* Wrong: expecting expr2 when values differ */
SELECT NULLIF(10, 5) AS wrong_expectation; -- Returns 10, not 5

/* Right: handle NULL result explicitly */
SELECT IFNULL(NULLIF(10, 10), 1) AS handled_null; -- Returns 1 instead of NULL

Output

wrong_expectation
-----------------
10

handled_null
------------
1

Quick Reference

Usage	Description	Example
NULLIF(expr1, expr2)	Returns NULL if expr1 = expr2; else expr1	NULLIF(5, 5) returns NULL
Use with division	Avoid division by zero by returning NULL	100 / NULLIF(divisor, 0)
Handle NULL	Use IFNULL or COALESCE to replace NULL	IFNULL(NULLIF(10,10), 1) returns 1

Key Takeaways

NULLIF returns NULL when two expressions are equal, otherwise returns the first expression.

It is useful to prevent errors like division by zero in queries.

Always handle possible NULL results to avoid unexpected query behavior.

NULLIF never returns the second expression when values differ.

Test NULLIF with your data types to avoid type comparison issues.