NULLIF function

Hi Alec,


SQL NULLIF function takes 2 parameters: it returns NULL if the 2 parameters are equal, otherwise it returns the value of the first parameter.

SQL NULLIF Function Syntax


 1. NULLIF(expression1, expression2) 

NULLIF(expression1, expression2)

SQL NULLIF Function Example

Table: Users
UserId     userName     screenName
1     Liysheet     Liy Sheet
2     Mohisy     Mohisy
3     Nuoo     Nuhoo
4     Mhquu     Mhquu
5     Yuhiaa     Uhiooo

Show NULL if userName is equal to screenName, otherwise show userName:


   1. SELECT userId, NULLIF(userName, screenName) 
   2. FROM Users 

SELECT userId, NULLIF(userName, screenName)
FROM Users

The output

UserId     NULLIF(userName, screenName)
1     Liysheet
2     NULL
3     Nuoo
4     NULL
5     Yuhiaa



Thanks

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned.

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
Store_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 Store_name, NULLIF(Actual,Goal) FROM Sales_Data;

The result is:
Store_name NULLIF(Actual,Goal)
Store A NULL
Store B 40
Store C 25