Oracle SQL NULLIF
NULLIF is a function in Oracle SQL that compares two expressions and returns NULL if they are equal, otherwise it returns first expression. The syntax is:
NULLIF(expression1, expression2)
For example:
SELECT NULLIF(column1, 'value')
This will return NULL if column1 is equal to 'value', otherwise it will return the value in column1.
Examples of Oracle SQL NULLIF
1)
You can also use NULLIF to compare two columns:
SELECT NULLIF(column1, column2)
This will return NULL if column1 is equal to column2, otherwise it will return the value in column1.
2)
NULLIF is often used to avoid dividing by zero or to replace a specific value with NULL. For example:
SELECT NULLIF(column1, 0) / column2
This will return NULL if column1 is zero, avoiding a 'division by zero' error.
3)
Suppose we have a table called "employees" with columns "name" and "manager_name". We want to find all employees who have a manager, but we want to exclude the CEO, whose name is 'John Smith', from the results. We can use NULLIF to achieve this:
SELECT *
FROM employees
WHERE NULLIF(manager_name, 'John Smith') IS NOT NULL;
In this example, NULLIF compares the "manager_name" column to 'John Smith'. If they are equal, it returns NULL, otherwise it returns the "manager_name". The WHERE clause then filters out the rows where "manager_name" is NULL (i.e., where the manager is 'John Smith').
In this way, we get all employees who have a manager, except for the CEO.