Chapter 2: Transact-SQL Functions

nullif

Supports conditional SQL expressions; can be used anywhere a value expression can be used; alternative for a case expression.

nullif(expression, expression)

nullif: compares the values of the two expressions. If the first expression equals the second expression, nullif returns NULL. If the first expression does not equal the second expression, nullif returns the first expression.

expression: is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions”.

Selects the titles and type from the titles table. If the book type is UNDECIDED, nullif returns a NULL value:

select title,
    nullif(type, "UNDECIDED")
from titles

This is an alternative way of writing Example 1:

select title, 
    case
        when type = "UNDECIDED" then NULL
        else type
    end
from titles

nullif expression alternate for a case expression.
nullif expression simplifies standard SQL expressions by allowing you to express a search condition as a simple comparison instead of using a when...then construct.
You can use nullif expressions anywhere an expression can be used in SQL.
At least one result of the case expression must return a non-null value. For example the following results in an error message:
```
select price, coalesce (NULL, NULL, NULL)
from titles
```
```
All result expressions in a CASE expression must not be NULL.
```
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy, as described in “Datatypes of mixed-mode expressions”. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.

ANSI SQL – Compliance level: Transact-SQL extension.

Anyone can execute nullif.