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 |