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.
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);
- It will return the first character string A.
- It will return the first numeric value 100.
- It will return NULL as both string values are same.
- It will return NULL as both numeric values are same.
- It will throw a casting error since string precedes number and the NULLIF logic fails.
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.
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).
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.
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.
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. (email@example.com, ‘’) returns firstname.lastname@example.org 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.