SQL NULLIF Function

NULLIF returns NULL if two specified values are equal.

If the two values are not equal, NULLIF returns the first value.

NULLIF is shorthand for a searched CASE expression (see below).

Example

#

Return NULL if two values are equal, if not, return the first value.

SELECT NULLIF('SQL', 'NoSQL') AS NotEqual,
       NULLIF('SQL', 'SQL') AS Equal
Result:  1 record
NotEqual Equal
HTML NULL

Using NULLIF

#

NULLIF can be used to capture missing data by checking for both NULL and '' with a single expression, like so:

SELECT * 
  FROM Customer
 WHERE NULLIF(City,'') IS NULL

Syntax

Syntax for the NULLIF function.

NULLIF(value1, value2)

value1 -- any value or column name.

value2 -- any value or column name.

NULLIF is equivalent to this statement.

SELECT 
 CASE WHEN value1 = value2 THEN NULL
 ELSE value1
END

More Examples

NULLIF with COLUMN

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List all suppliers without a fax number. Include records where Fax is NULL and also where Fax = '' (empty string).
SELECT CompanyName, City, Country, Phone, Fax
  FROM Supplier
 WHERE NULLIF(Fax, '') IS NULL

Our sample database has Fax values that are NULL, but not '' (empty string).

Result:  16 records
CompanyName City Country Phone Fax
Exotic Liquids London UK (171) 555-2222 NULL
New Orleans Cajun Delights New Orleans USA (100) 555-4822 NULL
Tokyo Traders Tokyo Japan (03) 3555-5011 NULL
Cooperativa de Quesos 'Las Cabras' Oviedo Spain (98) 598 76 54 NULL
Mayumi's Osaka Japan (06) 431-7877 NULL

You may also like



Guides


vsn 3.1