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.
Syntax – NULLIF Function in SQL Server
NULLIF (expression1, expression2)
Points to Ponder – NULLIF – SQL Server
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.
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.
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.
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.
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.
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.