SQL Server NULLIF

The NULLIF function in SQL Server accepts two arguments and compares them to determine they are the same or different. If both arguments are equal, it will return a NULL value. If the arguments are not identical, they will return the first argument. We can use this function with the clauses like SELECT, WHERE, and GROUP BY directly.

This function enables the user to perform a comparison of both numeric and string values that are either explicitly defined or returned by an expression. It’s a more advanced function with a few basic use cases. The most common of comparing the values of two separate columns and detecting and accounting for empty or incomplete data (i.e., NULL or ‘ ‘) in a column.

Syntax

The following are the syntax of NULLIF function in SQL Server:

SELECT NULLIF (expression1, expression2);  

In the above syntax, we can see two arguments expression1 and expression2. They are scalar expressions that perform a comparison of a scalar value. It can be numeric, string, or anything, i.e., column, parameter, variable, subqueries, mathematical operation, or function that returns a single numeric or string value.

The NULLIF function cannot work with time-dependent functions like the RAND() function in SQL Server. When we use a time-dependent function, the function can be evaluated twice, yielding different results from the two calls.

NOTE: NULLIF expressions must have the same datatype, or numeric expressions must come before string expressions. Otherwise, the query will be failed, and a casting error is returned. It returns a casting error because numeric expressions are often processed first by NULLIF.

NULLIF Function Example

Let us understand how to use the NULLIF function by taking some examples.

1. NULLIF with numeric data

Here we are going to see how NULLIF works with a numeric value. This example uses the NULLIF function and returns the NULL value because both arguments are the same:

SELECT NULLIF(25, 25) AS Result;   

Here is the output:

This example uses the NULLIF function and returns the first argument value because both arguments are not the same:

SELECT NULLIF(35, 25) AS Result;  

Here is the output:

2. NULLIF with string data

Here we are going to see how NULLIF works with string expression. This example uses the NULLIF function and returns the NULL value because both strings are the same:

SELECT NULLIF('javatpoint', 'javatpoint')  AS Result;  

Here is the output:

This example uses the NULLIF function and returns the first argument value because both strings are not the same:

SELECT NULLIF('Hello', 'javatpoint')  AS Result;  

Here is the output:

This example uses the NULLIF function and throws a casting error because NULLIF logic fails when the string does not precede with number:

SELECT NULLIF('Hello', 25) AS Result;  

Here is the output:

3. NULLIF with the CAST function

Here we are going to see how NULLIF works with the CAST function. This example uses the NULLIF and CAST function and returns the NULL value:

SELECT NULLIF(11, CAST(11.65 AS int)) AS Result;  

Here is the output:

We will get a NULL result because the CAST function first returns an integer value 11. Then, the first argument and second argument both become equal and display the NULL output.

NULLIF Function with table

This section will explain the actual case of using the NULLIF function. The NULLIF expression is advantageous when dealing with the legacy data, including a mixture of null and empty strings in a column. See the following example for its demonstration:

To do this, we first need to create a table named ‘Student’ in the specified database using the statement given below:

CREATE TABLE Student  

  

   id INT IDENTITY PRIMARY KEY,  

   name VARCHAR(50) NOT NULL,  

   gender VARCHAR(20) NOT NULL,  

   phone VARCHAR(20),   

   total_marks INT NOT NULL  

)

Next, we will add some values into this table as follows:

INSERT INTO Student   

VALUES ('Jolly Evans', 'Female', '205983494293', 520),   

('Josh Butler', 'Male', '229878353432', 645),   

('Rose Huges', 'Female', '', 610),   

('Laura Bennet', 'Female', '184756843843', 430),   

('Alan Simmons', 'Male', '', 500),   

('Kate Huges', 'Female', '229459433945', 600),   

('Joseph Paul', 'Male', '', 643),   

('Antonio Butler', 'Male', NULL, 513),   

('Diego Bennet', 'Male', '897437574321', 699),   

('Elis Simmons', 'Female', NULL, 540);

We can use the SELECT statement to display the table. We will see the following data in the table:

In the table, we can see that some of the phone column fields are nullable and empty. It contains NULL value because the phone is not known at the time of storing data. And has an empty field due to the data entry mistake. This situation arises a lot while dealing with legacy databases.

If we want to get the student details who do not have the phone number, we can use the following query:

SELECT * FROM Student WHERE phone IS NULL;  

It will display the result containing student detail who do not have the phone number:

Suppose we want to get the result with the empty string in the phone column also. We can do this by using the NULLIF expression:

SELECT * FROM Student WHERE  NULLIF(phone,'') IS NULL;  

It will display the result containing student detail who has a NULL or empty string in the phone number column:

NULLIF and CASE Expression

The NULLIF expression in SQL Server is similar to the CASE expression. See the below expression that uses NULLIF expression:

NULLIF(V1, V2)  

The above expression can be rewritten in the CASE expression as follows:

CASE   

  WHEN V1=V2 THEN NULL   

  ELSE V1   

END

We can understand it with the help of the following examples:

DECLARE @a int = 10, @b int = 20;  

  

-- Uses NULLIF  

SELECT NULLIF(@a, @b) AS Result;  

  

-- Uses CASE Expression  

SELECT  

    CASE  

        WHEN @a = @b THEN NULL  

        ELSE @a  

    END AS Result;

It will display the below result where we can see both the expression gives the same result.