SQL Server NULLIF() Function

In SQL Server, the NULLIF() function is a conditional function that compares two expressions and returns NULL if they are equal, otherwise it returns the value of the first expression.

Syntax

NULLIF(expression1, expression2)

Parameter Description:

• expression1: the first expression to compare.
• expression2: the second expression to compare.

Usage: In some cases, we may need to return NULL when two expressions are equal, otherwise return the value of expression 1. In such cases, we can use the NULLIF() function to achieve this requirement. For example, when processing data, we may need to filter out some useless data, some fields in which may contain null or default values. We can use the NULLIF() function to convert these values to NULL.

Examples

Here are two examples of using the NULLIF() function:

Example 1: Convert Default Values to NULL

Assume we have a table containing product information, including product name, price, and description. We find that some of the product descriptions are set to default values, and we want to convert these default values to NULL. We can use the following SQL statement:

SELECT ProductName, Price, NULLIF(Description, 'default') AS Description
FROM Products

Output:

Example 2: Convert Equal Values to NULL

Assume we have a table containing user information, including user ID, name, and email address. We find that some users’ email addresses are the same as their names, and we do not want these email addresses to appear in the results. Instead, we want to convert them to NULL. We can use the following SQL statement:

SELECT UserID, Name, NULLIF(Email, Name) AS Email
FROM Users

Output:

Conclusion

The NULLIF() function is a very useful conditional function that can conveniently convert certain values to NULL. When we need to convert default values or equal values to NULL, the NULLIF() function is a good choice.