ORACLE NULL IF

In this article, we will learn how to use NULLIF function in Oracle.

NULLIF compares two arguments and returns NULL if both the arguments are equal and in case they are not equal then it returns the first argument.

Syntax:

The general syntax of NULLIF function is as follows:

NULLIF (expression1, expression2 )

Here,

expression1 – It could be a numeric or text value or even a column name. This value could be of any data type.

expression2 – It’s the value we compare with expression1 and just like expression1, this could be a numeric or text value or even a column from a table.

The NULLIF function returns NULL if expression1 is equals to expression2. It returns expression1, if the values are not equal.

If expression1 and expression2 do not belong to the numeric data type then Oracle simply compares the values, but if its a string value then both values should belong to the same data type, otherwise Oracle returns the following error.

ORA-00932: inconsistent datatypes

Lets see some example to understand how to use NULLIF in a query.

Examples

1. NULLIF Function with Numeric and Text Values

Lets see a simple query to understand how NULLIF works:

SELECT
NULLIF(30,30) 
FROM
dual;

Result:

The query returns NULL as both the values are equal.

Let’s have a look at one more query:

SELECT
NULLIF(30, 40) 
FROM
dual;

Result:

In this query, since both the values are not equal we get expression1 as result. In this case, its 30.

Let’s see if we can input NULL value inside NULLIF function:

SELECT
NULLIF(NULL,40)
FROM
dual;

Result:

The query returns ORA-00932 error as we can see from the image.

We can also get the same error if we try to compare a string with numeric data type:

SELECT
NULLIF(40,'50')
FROM
dual;

Result:

The query returns the same ORA-00932 error stating that the data types are inconsistent.

If we want to compare a string with a numeric data type, then we first need to convert it into string data type using TO CHAR function. Let’s see this query:

SELECT
NULLIF(TO_CHAR(40),'50')
FROM
dual;

Result:

The query returns expression1 or 40 in this case as now both are of same data type, but the values are unequal so it returns 40.

2. Comparison between NULLIF and CASE function

The following query is a logical equivalent of NULLIF function and performs the same function as NULLIF does.

CASE
WHEN e1 = e2 THEN
NULL
ELSE
e1
END

As we can see, this is more verbose than simply using NULLIF function.

3. NULLIF with Columns in a Table

Often we need to perform several operations where we need to compare values between columns and this is where NULLIF function comes handy.

For the coming example, we will be using Employee table from HR schema:

Suppose, we want to compare the length of characters in the first name and last name of employees and want the result as NULL each the length of characters is equal. For this we will use the following query:

SELECT
first_name,
last_name,
LENGTH(first_name),
LENGTH(last_name),
NULLIF(LENGTH(first_name), LENGTH(last_name)) AS
"Comparison Output"
FROM
employees;

Result:

The query returns the length of characters in first name and last name of employees and also a comparison column in which we use NULLIF functions. It compares the length across first name and last name columns and returns NULL if length of characters is equal and returns expression1, in case they are unequal.

In this tutorial, we learned how to use NULLIF function to compare two values and return NULL if both are equal.