NULLIF
A function that returns NULL if an expression is true.
Synopsis
NULLIF(expression1,expression2)
Arguments
Argument | Description |
---|---|
expression1 | An SQL expression. |
expression2 | An SQL expression. |
NULLIF returns the same data type as expression1.
Description
The NULLIF function returns NULL if expression1 is equal to expression2, otherwise it returns expression1.
NULLIF is equivalent to:
SELECT CASE WHEN value1 = value2 THEN NULL ELSE value1 END FROM MyTable
Copy code to clipboard
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
Copy code to clipboard