Earn income with your data and sql skills

Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest freelancing marketplace for people like you.

SQL NULLIF Function

SQL Coalesce

SQL With Cte

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

#

Our Sql IS NULL Tutorial
Our Sql CASE Reference
Our Sql Tutorial