SQL Server NULLIF

Summary: in this tutorial, you will learn how to use the SQL Server NULLIF expression to return NULL if the first argument equals to the second one.

SQL Server NULLIF expression overview

The NULLIF expression accepts two arguments and returns NULL if two arguments are equal. Otherwise, it returns the first expression.

The following shows the syntax of the NULLIF expression:

NULLIF(expression1, expression2)
Code language: SQL (Structured Query Language) (sql)

In this syntax, the expression1 and expression2 are scalar expressions. It means each of them evaluates to a scalar value.

It is recommended that you not use the time-dependent functions such as RAND() function in the NULLIF function. Because this may cause the function to be evaluated twice and to yield different results from the two function calls.

SQL Server NULLIF examples

Let’s take some examples of using the NULLIF expression

Using NULLIF expression with numeric data examples

This example returns NULL because the first argument equals the second one:

SELECT 
    NULLIF(10, 10) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------
NULL

(1 row affected)Code language: PHP (php)

However, the following example returns the first argument because two arguments are not equal:

SELECT 
    NULLIF(20, 10) result;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

result
------
20

(1 row affected)

Using NULLIF expression with character string data example

The following example uses the NULLIF expression. It returns NULL because the first character string is equal to the second one:

SELECT 
    NULLIF('Hello', 'Hello') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------
NULL

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

This example returns the first argument because both arguments are not the same:

SELECT 
    NULLIF('Hello', 'Hi') result;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

result
------
Hello

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Using NULLIF expression to translate a blank string to NULL

The NULLIF expression comes in handy when you’re working with legacy data that contains a mixture of null and empty strings in a column. Consider the following example.

First, create a new table named sales.leads to store the sales leads:

CREATE TABLE sales.leads
(
    lead_id    INT	PRIMARY KEY IDENTITY, 
    first_name VARCHAR(100) NOT NULL, 
    last_name  VARCHAR(100) NOT NULL, 
    phone      VARCHAR(20), 
    email      VARCHAR(255) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, insert three rows into the sales.leads table:

INSERT INTO sales.leads
(
    first_name, 
    last_name, 
    phone, 
    email
)
VALUES
(
    'John', 
    'Doe', 
    '(408)-987-2345', 
    '[email protected]'
),
(
    'Jane', 
    'Doe', 
    '', 
    '[email protected]'
),
(
    'David', 
    'Doe', 
    NULL, 
    '[email protected]'
);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the sales.leads table:

SELECT 
    lead_id, 
    first_name, 
    last_name, 
    phone, 
    email
FROM 
    sales.leads
ORDER BY
    lead_id;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

The phone column is a nullable column. If the phone of a lead is not known at the time of recording, the phone column will have NULL.

However, from the output, the second row has an empty string in the phone column due to the data entry mistake. Note that you may encounter a situation like this a lot if you are working with legacy databases.

To find the leads who do not have the phone number, you use the following query:

SELECT    
    lead_id, 
    first_name, 
    last_name, 
    phone, 
    email
FROM    
    sales.leads
WHERE 
    phone IS NULL;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

The output missed one row which has the empty string in the phone column. To fix this you can use the NULLIF expression:

SELECT    
    lead_id, 
    first_name, 
    last_name, 
    phone, 
    email
FROM    
    sales.leads
WHERE 
    NULLIF(phone,'') IS NULL;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

NULLIF and CASE expression

This expression that uses NULLIF:

SELECT 
    NULLIF(a,b)
Code language: SQL (Structured Query Language) (sql)

is equivalent to the following expression that uses the CASE expression:

CASE 
    WHEN a=b THEN NULL 
    ELSE a 
END
Code language: SQL (Structured Query Language) (sql)

See the following example:

DECLARE @a int = 10, @b int = 20;
SELECT
    NULLIF(@a,@b) AS result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------
10

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

The following example returns the same result, but use the CASE expression instead:

DECLARE @a int = 10, @b int = 20;
SELECT
    CASE
        WHEN @a = @b THEN null
        ELSE 
            @a
    END AS result;
Code language: SQL (Structured Query Language) (sql)

The CASE expression is verbose while the NULLIF expression is much shorter and more readable.

In this tutorial, you have learned how to use the SQL Server NULLIF expression to return NULL if the first argument equals to the second one.