Summary -

In this topic, we described about the NULLIF Function with detailed example.

NULLIF function has two expressions. NULLIF function compares two expressions values, If they are same NULLIF function returns NULL value in result set otherwise it returns first expression value in result set. It returns numeric or string values based on requirement.

Syntax -

SELECT NULLIF(Expression1, Expression2) from table; 

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Getting numeric values by using NULLIF function on existing table.

Requirement - Getting numeric values by using NULLIF function on existing employee_details table. The query was as follows –

SELECT NULLIF(emp_id, salary)result FROM employee_details;  

By executing above query we can compare emp_id values with salary values on employee_details table and it returns emp_id values in result set because emp_id, salary columns does not have matched values. The output was as follows –

result
001
002
003
004
005
006
007
008
009

Scenario – Getting string values by using NULLIF function on existing table.

Requirement – Getting string values by using NULLIF function on existing employee_details table. The query was as follows –

SELECT NULLIF(designation, emp_name)result FROM employee_details;  

By executing above query we can compare designation values with emp_name values on employee_details table and it returns designation values in result set because designation, emp_name columns does not have matched values. The output was as follows –

result
Director
Director
Manager
Manager
Analyst
Analyst
Clerk
Salesman
Salesman

Scenario – Getting NULL values by comparing values on existing table by using NULLIF.

Requirement – Getting NULL values by comparing same column values on employee_details table by using NULLIF. The query was as follows –

SELECT NULLIF (date_of_hire, date_of_hire)result FROM employee_details; 

By executing above query we can get NULL values in result set because we can compare with same date_of_hire column which have same values on employee_details table. The output was as follows –

result
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

Scenario – Getting rows from existing table by using NULLIF function.

Requirement - Getting rows from existing employee_details table by using NULLIF function. Let us consider on manager_id column we have blank space instead of NULL. If we trying to fetch those employees details by using manager_id is NULL, In result set we get nothing in the output like as shown in below –

SELECT * FROM employee_details where manager_id IS NULL; 

By executing above query we can get output like as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id

Actually we have employees details who does not have manager_id on employee_details table. By using NULLIF function we can get those values. The query was as follows –

SELECT * FROM employee_details WHERE NULLIF(manager_id, ' ') IS NULL; 

By executing above query we can get output like as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000