SQL > SQL NULL > NULLIF Function

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows:

NULLIF(expr1, expr2) is a concise way to convert a specific value into NULL — it returns NULL when both arguments are equal, and returns the first argument otherwise.

NULLIF ("expression 1", "expressions 2")

It is the same as the following CASE statement: 

SELECT CASE ("column_name")
   WHEN "expression 1 = expression 2 " THEN "NULL"
   [ELSE "expression 1"]
   END
FROM "table_name";

For example, let's say we have a table that tracks actual sales and sales goal as below:

Table Sales_Data

 Store_Name  Actual  Goal 
 Store A 50 50 
 Store B 40 50 
 Store C 25 30 

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement: 

SELECT Store_Name, NULLIF(Actual, Goal) FROM Sales_Data;

Result:

Store_Name NULLIF(Actual, Goal)
Store A NULL
Store B 40
Store C 25

Frequently Asked Questions

What does NULLIF return when the two arguments are equal?
NULLIF returns NULL when both arguments are equal. If they differ, it returns the value of the first argument unchanged.
What is a common use case for NULLIF?
A popular use case is preventing division-by-zero errors: value / NULLIF(divisor, 0). When the divisor is 0, NULLIF converts it to NULL, and dividing by NULL yields NULL instead of an error.
Is NULLIF equivalent to a CASE expression?
Yes. NULLIF(expr1, expr2) is shorthand for CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
Does NULLIF work with all data types?
NULLIF works with most comparable data types (numeric, string, date), but both arguments must be of compatible types.

Related Topics

Next: SQL Video Tutorial

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact