NULLIF function

Question

Hii, Why we use NULLIF function?

Akash Ahlawat · Answer

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. Example:SELECT Store_name, NULLIF(Actual,Goal) FROM Sales_Data;

Alec Stewart · Answer

Thanks for replying

Satyapriya Nayak · Answer

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

Pravin More · Answer

Hi Alec, NULLIF function in sql server 1.Returns a null value if the two specified expressions are equal. 2.returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression syntax:- NULLIF ( expression , expression ) Thanks, Pravin.

Hemant Kumar · Answer

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

NULLIF function

Answers (5)