In SQL Server, the NULLIF() expression checks the value of two specified expressions. It returns a null value if they’re equal, otherwise it returns the first expression.

Syntax

The syntax goes like this:

NULLIF ( expression , expression )

Example

Here’s an example to demonstrate:

SELECT NULLIF(3, 3);

Result:

NULL

Here, both expressions are equal, and so the result is a null value.

Here’s what happens when the expressions are not equal:

SELECT NULLIF(3, 7);

Result:

3

This time the first value is returned.

Here’s an example that encapsulates both outcomes:

SELECT 
    NULLIF(0, 0) AS a,
    NULLIF(3, 3) AS b,
    NULLIF(3, 0) AS c,
    NULLIF(0, 3) AS d;

Result:

+------+------+-----+-----+
| a    | b    | c   | d   |
|------+------+-----+-----|
| NULL | NULL | 3   | 0   |
+------+------+-----+-----+

A Practical Example

Suppose we have a table with the following data:

SELECT 
    ProductName,
    ProductPrice
FROM Products;

Result:

+-------------------------------------+----------------+
| ProductName                         | ProductPrice   |
|-------------------------------------+----------------|
| Left handed screwdriver             | 25.99          |
| Long Weight (blue)                  | 14.75          |
| Long Weight (green)                 | 11.99          |
| Smash 2000 Sledgehammer             | 0.00           |
| Chainsaw (includes 3 spare fingers) | 0.00           |
| Straw Dog Box                       | NULL           |
| Bottomless Coffee Mugs (4 Pack)     | 9.99           |
| Right handed screwdriver            | 25.99          |
+-------------------------------------+----------------+
(8 rows affected)

And suppose we want to find out how many products have a positive price. In other words, we don’t want to include products that have a price or zero or a null value.

To do this, we can use NULLIF() in conjunction with the COUNT() function:

SELECT 
    COUNT(NULLIF(ProductPrice, 0.00)) AS Result
FROM Products;

Result:

+----------+
| Result   |
|----------|
| 5        |
+----------+
Warning: Null value is eliminated by an aggregate or other SET operation.

We get 5 as expected, which is exactly how many rows have a positive value in the ProductPrice column.

This works because the COUNT() function only counts non-null values. By converting the zero amounts to null, we’re able to ignore those values in our calculation.

Here it is again without the NULLIF() function.

SELECT COUNT(ProductPrice) AS Result
FROM Products;

Result:

+----------+
| Result   |
|----------|
| 7        |
+----------+
Warning: Null value is eliminated by an aggregate or other SET operation.

This time it includes the zero amounts and we get 7. It still ignores row 6 because that actually has a null value.

When the First Argument is the Null Constant

The function does not accept the null constant as its first argument:

SELECT NULLIF(null, 3);

Result:

Msg 4151, Level 16, State 1, Line 1
The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

As the error message states, the type of the first argument must be known. The function’s return value uses the data type of the first expression, and this error reflects that.

This error should be quite rare. In most cases, you’ll pass a column name as the first argument, and in SQL Server, columns have a defined data type. In such cases, a null value will actually be accepted, due to the fact that SQL Server knows the data type of the column.

The same could be said for variables. If you pass a variable, you would have had to declare its type, and therefore, you would not get the error.

Invalid Argument Count

Calling the function without passing any arguments results in an error:

SELECT NULLIF();

Result:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

And passing too many arguments also causes an error:

SELECT NULLIF(1, 2, 3);

Result:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.