Use the SQL NULLIF function to compare two input expressions and returns NULL if both are equal.
Description
Like the COALESCE function, the NULLIF function is a control flow function in SQL. It checks if the specified expressions are equal. It is a simpler version of a CASE expression with the condition comparing two input values. In the case they are equal, a NULL expression returns. The NULLIF function also helps to work with missing data in nullable columns of the database. We can use it for comparison and handling the missing values.
SQL NULLIF Usage Notes
The two input arguments in the NULLIF function are the two expressions for the comparison. If both specified values are equal, then a NULL expression returns. If they are unequal, then the first expression returns.
The return type is the same as the first expression. For the same input values, the NULL has the same data type as the first expression.
It is not recommended to use the SQL NULLIF function with time-dependent functions. The two evaluations of the NULLIF function may return different values.
Syntax
NULLIF (first_expression, second_expression)
SQL NULLIF Examples
The following examples show how a NULLIF function works.
Passing NULL as the first argument will raise an error as the data type of the first argument should be known.
In the following example, we will use the AdventureWorks2019 database. We select the Middle Name column of the Person table. As the middle name is a nullable column, we will see a lot of empty values. We can replace these empty strings with meaningful data like a person’s last name.
NULLIF returns NULL wherever the middle name is an empty string. COALESCE then replaces these NULL values with the person’s last name. In this way, a SQL NULLIF allows us to present data more meaningfully.
The NULLIF can also help in another case of handling nullable column values. Suppose we want to select all the people except those with NULL Suffix value.
SELECT * FROM Person.Person WHERE MiddleName IS NULL
The WHERE Suffix IS NULL clause will return all the records where the suffix is null. However, there might be some records with an empty string and not NULL due to wrong data insertion. The following SQL query will miss those records.
The above query will now return all the records with NULL and empty Suffix values.
Equivalent SQL NULLIF expressions
Earlier we mentioned NULLIF is a short hand for using CASE. Using our example from above, see the equivalent statement using CASE.
Though the CASE statement clearly explain the logic, it is much longer. You can shorten the expression a bit, by using IIF, but still it is longer:
The recommended approach is to use SQL ISNULL. Though it may take time to get used to the function, it reduces the complexity. In my opinion, it make the SQL easier to read.
