nullif

Description

Supports conditional SQL 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” on page 249 in Chapter 4, “Expressions, Identifiers, and Wildcard Characters” of Reference Manual: Building Blocks.

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

Example 2

This is an alternative way of writing Example 1:

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

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

nullif permission defaults to all users. No permission is required to use it.

See also

Commands case, coalesce, select, if...else, where clause