SQL nullif | nullif Function in SQL - sql - sql tutorial - learn sql
- In SQL Server (Transact-SQL), the NULLIF function compares expression1 and expression2.
- If expression1 and expression2 are equal, the NULLIF function returns NULL.
- Otherwise, it returns the first expression which is expression1.
- The NULLIF function takes two arguments.
- If the two arguments are equal, then NULL is returned.
- Otherwise, the first argument is returned.
- The syntax for NULLIF is as follows:
NULLIF ("expression 1", "expressions 2")
- It is the same as the following CASE statement:
SELECT CASE ("column_name")
WHEN "expression 1 = expression 2 " THEN "NULL"
[ELSE "expression 1"]
END
FROM "table_name";
- For example, let's say we have a table that tracks actual sales and sales goal as below:
Table Sales_Data
Shop_Name | Actual | Goal |
---|---|---|
Store A | 50 | 50 |
Store B | 40 | 50 |
Store C | 25 | 30 |
- We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different.
- To do this, we issue the following SQL statement:
SELECT Shop_Name, NULLIF (Actual, Goal) FROM Sales_Data;
Result:
Shop_Name | NULLIF (Actual, Goal) |
---|---|
Store A | NULL |
Store B | 40 |
Store C | 25 |