nullif

Description

Allows SQL expressions to be written for conditional values. nullif expressions can be used anywhere a value expression can be used; alternative for a case expression.

Syntax

nullif(expression, expression)

Parameters

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”.

Examples

Example 1

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

Alternately, you can also write:

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

Usage

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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Anyone can execute nullif.