SQL Server NULLIF() Function

SQL Server NULLIF() Function

The SQL Server NULLIF() function returns NULL if the two specified expressions are equal. Otherwise, it returns the first expression. This function is useful for handling and comparing NULL values in SQL queries.

Syntax

SELECT NULLIF(expression1, expression2);

The NULLIF() function takes two arguments:

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

Example SQL Server NULLIF() Function Queries

Let's look at some examples of SQL Server NULLIF() function queries:

1. Basic NULLIF() Example

SELECT NULLIF(1, 1) AS result;

This query returns NULL because the two expressions (1 and 1) are equal. The result will be:

result
------
NULL

2. NULLIF() with Different Values

SELECT NULLIF(1, 2) AS result;

This query returns 1 because the two expressions (1 and 2) are not equal. The result will be:

result
------
1

3. NULLIF() with Column Values

SELECT id, value1, value2, NULLIF(value1, value2) AS result
FROM comparison_table;

This query compares value1 and value2 for each row in comparison_table and returns NULL if they are equal, otherwise returns value1. The result will show the original id, value1, value2, and the corresponding result.

4. NULLIF() with a Variable

DECLARE @value1 INT = 10;
DECLARE @value2 INT = 10;
SELECT NULLIF(@value1, @value2) AS result;

This query uses variables to store values and returns NULL because @value1 and @value2 are equal. The result will be:

result
------
NULL

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the NULLIF() function.

Step 1: Creating a Table

This step involves creating a new table named products to store some sample data with product prices.

CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2),
    discounted_price DECIMAL(10, 2)
);

In this example, we create a table named products with columns for id, price, and discounted_price.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the products table.

INSERT INTO products (id, price, discounted_price) VALUES (1, 100.00, 90.00);
INSERT INTO products (id, price, discounted_price) VALUES (2, 200.00, 200.00);
INSERT INTO products (id, price, discounted_price) VALUES (3, 300.00, 250.00);

Here, we insert data into the products table.

Step 3: Using the NULLIF() Function

This step involves using the NULLIF() function to compare price and discounted_price and return NULL if they are equal.

SELECT id, price, discounted_price, NULLIF(price, discounted_price) AS result
FROM products;

This query retrieves the id, price, discounted_price, and the result of NULLIF(price, discounted_price) for each row in the products table. The result will be:

id  price  discounted_price  result
--- ------ ----------------- ------
1   100.00 90.00             100.00
2   200.00 200.00            NULL
3   300.00 250.00            300.00

Conclusion

The SQL Server NULLIF() function is a powerful tool for handling and comparing NULL values. Understanding how to use the NULLIF() function and its syntax is essential for effective data processing and manipulation in SQL Server.