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.
SELECT NULLIF(expression1, expression2);
The NULLIF()
function takes two arguments:
expression1
: The first expression to compare.expression2
: The second expression to compare.Let's look at some examples of SQL Server NULLIF()
function queries:
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
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
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
.
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
Let's go through a complete example that includes creating a table, inserting data, and using the NULLIF()
function.
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
.
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.
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
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.