Skip to main content

NULLIF (SQL)

A function that returns NULL if two expressions have the same value.

Synopsis

NULLIF(expression1,expression2)

Description

The NULLIF function returns NULL if the value of expression1 is equal to the value of expression2. Otherwise, it returns the expression1 value.

NULLIF is equivalent to:

SELECT CASE 
WHEN value1 = value2 THEN NULL
ELSE value1
END
FROM MyTable

Collations are applied to expression1 and expression2 before they are compared for equality. If they are not equal, the resulting expression has the collation of expression1.

Arguments

expression1

An expression, which can be the name of a column, a numeric or string literal, a host variable, or the result of another scalar function.

expression2

An expression, which can be the name of a column, a numeric or string literal, a host variable, or the result of another scalar function.

NULLIF returns the same data type as expression1.

NULL Handling Functions Compared

The following table shows the various SQL comparison functions. Each function returns one value if the logical comparison tests True (A same as B) and another value if the logical comparison tests False (A not same as B). These functions allow you to perform NULL logical comparisons. You cannot specify NULL in an actual equality (or non-equality) condition comparison.

SQL Function Comparison Test Return Value
NULLIF(ex1,ex2) ex1 = ex2

True returns NULL

False returns ex1

ISNULL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

IFNULL(ex1,ex2) [two-argument form] ex1 = NULL

True returns ex2

False returns NULL

IFNULL(ex1,ex2,ex3) [three-argument form] ex1 = NULL

True returns ex2

False returns ex3

{fn IFNULL(ex1,ex2)} ex1 = NULL

True returns ex2

False returns ex1

NVL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

COALESCE(ex1,ex2,...) ex = NULL for each argument

True tests next ex argument. If all ex arguments are True (NULL), returns NULL.

False returns ex

Examples

The following example uses the NULLIF function to set to null the display field of all records with Age=20:

SELECT Name,Age,NULLIF(Age,20) AS Nulled20
FROM Sample.Person

See Also

FeedbackOpens in a new tab