The SQL NULLIF function is a powerful tool that allows you to compare two expressions and return a specific value based on the result of the comparison. This function is commonly used in SQL Server to handle various scenarios where you need to handle NULL values or avoid errors.

Syntax of the NULLIF Function

The syntax of the NULLIF function is as follows:

NULLIF(Expr1, Expr2)

The NULLIF function takes two arguments, Expr1 and Expr2. It compares these two expressions and returns the following values:

• If both the arguments are equal, it returns NULL.
• If both the arguments are not equal, it returns the value of Expr1.

Alternatively, you can achieve the same result using a SQL CASE statement:

CASE 
    WHEN Expr1 = Expr2 THEN NULL 
    ELSE Expr1 
END

Example Usage of the NULLIF Function

Let’s understand the SQL NULLIF function with some examples:

SELECT NULLIF(1, 2) AS result;

In this example, the NULLIF function compares the values 1 and 2. Since they are different, the function returns the value 1 as the result.

SELECT NULLIF(1, 1) AS result;

In this case, the NULLIF function compares the values 1 and 1. Since they are equal, the function returns NULL as the result.

SELECT NULLIF('Hello', ' Hello') AS result;
SELECT NULLIF('Hello', ' Hi') AS result;

These examples demonstrate how the NULLIF function can be used with string values. The function compares the two expressions and returns the first expression if they are not equal. Otherwise, it returns NULL.

Use Cases of the NULLIF Function

The SQL NULLIF function has several use cases that can help you handle specific scenarios in your SQL queries. Here are a couple of examples:

Avoiding Divide By Zero Errors

One common use case of the NULLIF function is to avoid divide by zero errors. When dividing a value by another value, if the denominator is zero, it will result in an error. To prevent this, you can use the NULLIF function to replace the denominator with NULL if it is zero.

DECLARE @salesOrderQuantity INT;
DECLARE @salesOrderValue INT;

SET @salesOrderQuantity = 100;
SET @salesOrderValue = 0;

SELECT (@salesOrderQuantity / NULLIF(@salesOrderValue, 0)) * 100 AS salesOrderPercentage;

In this example, the NULLIF function is used to replace the value of @salesOrderValue with NULL if it is zero. This prevents the divide by zero error and allows the query to execute without any issues.

Translating a Blank String to NULL

Another use case of the NULLIF function is to translate a blank string to NULL. In some cases, you may have a column that allows empty strings, but you want to treat them as NULL values. The NULLIF function can help you achieve this.

CREATE TABLE DemoTable (
   ID INT PRIMARY KEY,
   [Name] VARCHAR(100) NOT NULL,
   MobileNo VARCHAR(20)
);

INSERT INTO DemoTable (ID, [Name], MobileNo)
VALUES (1, 'A', '(111)-111-1111'),
       (2, 'B', ''),
       (3, 'C', NULL);

SELECT * FROM DemoTable WHERE NULLIF(MobileNo, '') IS NULL;

In this example, the NULLIF function is used to compare the MobileNo column with an empty string. If the value is an empty string, the function returns NULL. This allows you to find records that do not have a mobile number specified, including those with an empty string or a NULL value.

As you can see, the SQL NULLIF function is a versatile tool that can be used in various scenarios to handle NULL values and avoid errors. Understanding how to use this function effectively can greatly enhance your SQL Server queries.