top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is main feature of NULLIF Function in SQL?

0 votes
394 views
What is main feature of NULLIF Function in SQL?
posted Jul 1, 2015 by Amit Kumar Pandey

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

NULLIF Function

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
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;

Result:

Store_Name  NULLIF (Actual, Goal)
Store A NULL
Store B 40
Store C 25
answer Jul 1, 2015 by Manikandan J
0 votes

Examples

The following example selects those employees from the sample schema hr who have changed jobs since they were hired, as indicated by a job_id in the job_history table different from the current job_id in the employees table:

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name, "Old Job ID";

LAST_NAME Old Job ID


De Haan AD_VP
Hartstein MK_MAN
Kaufling ST_MAN
Kochhar AD_VP
Kochhar AD_VP
Raphaely PU_MAN
Taylor SA_REP
Taylor
Whalen AD_ASST
Whalen

answer Jul 1, 2015 by Arun Gowda
...