SQL Server NULLIF

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the NULLIF function to evaluate 2 expressions and find out if they are same or different.

What is NULLIF in SQL Server?

NULLIF is a function available in most RDBMS to compare 2 expressions and determine if they are same or different. It can compare both numeric and string values specified directly or retuned by an expression. The behavior of the function is that if the expressions are same it returns NULL as the result and if different it returns the first expression as the result. It is an advanced function having specific use cases of which the two most common are to compare the values of 2 different columns and to detect and account for blank or missing information (i.e. NULL or ‘’) in a column. The second function is particularly relevant when dealing with legacy data.

Operation

Since NULLIF is a function that returns a unique value it can be used as an expression with clauses like SELECT, WHERE, GROUP BY etc. An important point to note and keep in mind is that the datatypes of the expressions passed as arguments with NULLIF should be either same or numeric expressions should precede string expressions. Otherwise the query fails and returns a casting error. This is because NULLIF always processes numeric expressions (i.e. numbers) first.

The following shows the different operating cases of the NULLIF function.

1. SELECT NULLIF ('A','B');
2. SELECT NULLIF (100, 20);
3. SELECT NULLIF ('A','A');
4. SELECT NULLIF (100, 100);
5. SELECT NULLIF ('A', 100);

1. It will return the first character string A.
2. It will return the first numeric value 100.
3. It will return NULL as both string values are same.
4. It will return NULL as both numeric values are same.
5. It will throw a casting error since string precedes number and the NULLIF logic fails.

Syntax

The basic syntax of SQL Server NULLIF statement is given below.

SELECT NULLIF (expression1, expression2);

In this syntax,

• NULLIF () – function name which takes 2 expressions as arguments and compares them.
• expression – a literal (numeric or string) value or anything (including table column, parameter, variable, subqueries, mathematical operation, function etc.) that returns a single numeric or string value.

SQL Server NULLIF Examples

We will understand the above-mentioned use cases through the following examples.

1)  SQL Server NULLIF – column value comparison example

Suppose we have a table called states_budgets containing budget allocation information of the different Indian states for 2 consecutive years in crores of rupees.

state	current_year	previous_year
Assam	10000	9000
Delhi	30000	28500
Gujrat	23000	23000
Himachal Pradesh	12000	12000
Karnataka	34000	34000
Kerala	19000	18000
Meghalaya	9000	9000
Punjab	27000	25000

Now we want to check if there has been an increment in the budget allocation in the current year over the previous year or is the allocated amount the same. We can do so with the following query using NULLIF.

SELECT
NULLIF (current_year, previous_year) budget_increment
FROM states_budgets;

The query will generate the following output which shows the current year budget amount for states where there has been an increment (i.e. returns the first value since the values are different for the consecutive years) and NULL for those where there has been no increment (since both year values are same).

state	budget_increment
Assam	10000
Delhi	30000
Gujrat	NULL
Himachal Pradesh	NULL
Karnataka	NULL
Kerala	19000
Meghalaya	NULL
Punjab	27000

2) SQL Server NULLIF – detecting blanks or missing information example

Suppose we have a user table containing basic information (username and email address) about users. It is possible that some users did not share or missed to share their email address (and it was entered as NULL) or the same was missed while entering data into the table. So, we have a table which looks like below.

user_id	first_name	last_name	user_email
1	Aditya	Singh
2	Sek	Ismile	sek_2222@gmail.com
3	Priya	Upala	NULL
4	Vikas	Malagi	vk07mh@gmail.com
5	Pallab	Chakraborty	palchak77@gmail.com
6	Nidhi	Rohatgi
7	Sumit	Bose
8	Feroza	Malik	NULL

Now we might want to find out the records where the user email address does not exist. We can do so with the following NULLIF query in conjunction with IS NULL (which looks for and returns NULL values).

SELECT user_email
FROM users
WHERE NULLIF (user_email, '') IS NULL;

The query will generate the following output.

user_email

NULL

NULL

Explanation: In the query we have passed 2 expressions as required – user_email and ‘’ (i.e. blank) to the NULLIF function. The NULLIF function itself is passed as an expression in a WHERE condition which checks whether the value returned by the NULLIF function is NULL or not. The way it works is that the NULLIF function returns NULL for (NULL, ‘’) and (‘’, ‘’) and the WHERE condition is satisfied and the corresponding records displayed in the output. If the email address value exists in a record then NULLIF returns the email address as the result which fails the WHERE condition and hence the corresponding record is not included in the output. E.g. (sek_2222@gmail.com, ‘’) returns sek_2222@gmail.com as the NULLIF result which is not NULL and hence the WHERE condition is not satisfied and the corresponding record is discarded from the resultset.