NULLIF – TSQL Function in SQL Server

NULLIF is a function applied to return a NULL value if specified two expressions (expression1= expression2) are equal. NULLIF function is an ANSI function, it is available with other RDBMS vendors like ORACLE, MySQL, Postgres and among others.

NULLIF Function can be useful in cases when applications are using different indicators to represent a Missing or Unknown value which can be transformed to NULL while representing a record in a table.

NULLIF (expression1, expression2)

1. If expression1 and expression2 are equal then NULLIF function returns NULL as output.

2. If expression1 and expression2 are not equal then NULLIF function returns the expression1 as output.

3. NULLIF function is a short hand representation of applying a CASE statement in SQL SERVER.

Let’s walk through with few examples to get sound insights of NULLIF function in SQL Server.

1.1 – SQL Server – NULLIF Function – Returns NULL

In the following example, we can observe that both the expressions are equal, therefore, NULLIF function returns NULL as expected output.

DECLARE  @MyString1 as VARCHAR(20) = 'Tech-recipes.com'
        ,@MyString2 as VARCHAR(20) = 'Tech-recipes.com';

SELECT NULLIF(@MyString1,  @MyString2) as NULLIF_Example;

1.1 NULLIF Example – Live Demo

1.2 – SQL Server – NULLIF Function – Returns 1st Expression

In the below example when expression1 is not equal to expression2, NULLIF function returns the expression1 as output.

DECLARE  @Number1  as INT = 10
        ,@Number2 as INT  = 20;

SELECT NULLIF(@Number1,  @Number2) as NULLIF_Example;

1.2 NULLIF Example – Live Demo

1.3 – SQL Server – NULLIF Function – Comparing Empty String

The foremost business purpose reason to use NULLIF function to compare existing values against empty string ” and storing NULL in a table instead of storing ” empty string.

In most cases, during migrating data from a legacy system, we find many of the rows have empty strings, and as per new database design, we want to have a NULL to show unknown or missing values, in this case, we can use NULLIF function.

Following the example, you can see instead of inserting empty string against ID = 2, we’ve NULL indicator in @Information table.

DECLARE @Information TABLE
(
    Id           INT  
   ,Description  VARCHAR(255) NULL
);    

Insert Into @Information
SELECT 1,NULLIF('Niraj','')
UNION
SELECT 2,NULLIF('','')
UNION 
SELECT 3,NULLIF('Vish','')
UNION 
SELECT 4,NULLIF('Chetan','');
    
SELECT * FROM @Information;

1.3 NULLIF Example – Live Demo

1.4 – SQL Server – NULLIF Function with AVG()

As we’re aware of NULL values are ignored by each aggregate functions except COUNT(1) or COUNT(*). Let’s find the average of the salary where salary is NOT NULL, In a case of salary being “0” or NULL ignore these in average aggregation.

IF OBJECT_ID('Employee') IS NOT NULL
DROP TABLE Employee;

Create Table Employee
(
    Id     INT IDENTITY(1,1)
   ,Name   VARCHAR(255)
   ,Salary DECIMAL(18,2)
);

INSERT INTO Employee
SELECT 'Niraj', 100
UNION  
SELECT 'Vish', 0
UNION
SELECT 'Chetan', 300;

SELECT AVG(NULLIF(Salary, 0)) as AvgSalaryIgnoreZeroSalary
FROM   Employee;

1.4 NULLIF Example – Live Demo

1.5 – SQL Server – CASE Statement and NULLIF Function

Following example shows, NULLIF function is simplified version of the CASE statement, we can obtain similar kind of output using CASE statement as we get with NULLIF function.

DECLARE @TestData TABLE
(
    Id     INT IDENTITY,
    Name1  VARCHAR(255),
    Name2  VARCHAR(255)
);

INSERT INTO @TestData 
SELECT 'Rush', 'Rush'
UNION 
SELECT 'Alp', 'Alpha'
UNION
SELECT 'Nkit', 'Nkit'
UNION
SELECT 'shew', 'shewt';

SELECT Id, Name1, Name2, NULLIF(Name1,Name2)
FROM   @TestData;

SELECT Id, Name1, Name2, CASE WHEN Name1 = Name2 THEN NULL Else Name1 END
FROM   @TestData;

1.5 NULLIF Example – Live Demo

Summary – In this tech-recipes post, we have walked through with NULLIF function in SQL Server with valuable examples, You can find many useful posts from SQL Server Tech-Recipes archives for reference.