NULLIF - InterSystems SQL Reference - InterSystems IRIS Data Platform 2018.1

docs.intersystems.com

Home / InterSystems SQL Reference / SQL Functions / NULLIF

InterSystems SQL Reference

NULLIF

InterSystems: The power behind what matters

A function that returns NULL if an expression is true.

Synopsis

NULLIF(expression1,expression2)

Arguments

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

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