FB

Oracle NULLIF FunctionThe Oracle NULLIF function is another one of Oracle’s NULL-related functions. Let’s take a look at what NULLIF does and what you can use it for.

Purpose of the Oracle NULLIF Function

The Oracle NULLIF purpose is to compare two values, and determine if they match or not.

If the two values match, the function will return NULL. If they don’t match, NULLIF returns the first value.

 

Syntax

The syntax of the Oracle NULLIF function is:

NULLIF (expr1, expr2)

As mentioned above, this function returns either:

  • NULL if expr1 and expr2 match
  • expr1 if expr1 and expr2 don’t match

Get Your Free PDF: 9 Ways to Improve your Database Skills

Parameters

The parameters of the NULLIF function are:

  • expr1 (mandatory): This is the first value to use for the comparison. It is also returned if the values do not match.
  • expr2 (mandatory): This is the second value to use for the comparison (between expr1 and expr2).

Some things to note for these parameters:

  • Both expr1 and expr2 can be numeric data types, and the function will work.
  • If the data types of expr1 and expr2 are not numeric, they need to be the same. If not, the function will show an error.

 

NULLIF as a CASE Statement

The Oracle NULLIF function can be rewritten as a CASE statement:

CASE expr1 WHEN expr2 THEN NULL ELSE expr1 END;

Or, it can be written this way:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END;

You can read this article for more information on CASE statements.

This function can also be expressed as an IF statement (using general syntax, not Oracle SQL).

if (expr1 = expr2) then
     return null;
else
     return expr1;
end if;

 

Examples of the Oracle NULLIF Function

Here are some examples of the NULLIF function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example uses NULLIF on two matching number values.

SELECT NULLIF(23, 23) AS NULLCHECK FROM dual;

Result:

NULLCHECK
(null)

The result is NULL because both values match.

 

Example 2

This example uses NULLIF on two number values, but they don’t match.

SELECT NULLIF(23, 29) AS NULLCHECK FROM dual;

Result:

NULLCHECK
23

The result shows 23 because the inputs don’t match, so expr1 is returned.

 

Example 3

This example shows NULLIF with two matching values, but they are entered as different numeric data types.

SELECT NULLIF(23.000, 23) AS NULLCHECK FROM dual;

Result:

NULLCHECK
(null)

The result is NULL because both values match, even though they are different numeric types (decimal and integer).

 

Example 4

This example shows NULLIF on two text values that match.

SELECT NULLIF('Sunday', 'Sunday') AS NULLCHECK FROM dual;

Result:

NULLCHECK
(null)

The result is NULL because both expr1 and expr2 values match.

 

Example 5

This example shows the NULLIF function on two text values that don’t match.

SELECT NULLIF('Sunday', 'Saturday') AS NULLCHECK FROM dual;

Result:

NULLCHECK
Sunday

The result shows expr1 which was provided as ‘Sunday’.

 

Example 6

This example shows NULLIF with two different variable types which have the same value.

SELECT NULLIF('23.7', 23.7) AS NULLCHECK FROM dual;

Result:

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

The result is an “inconsistent datatype” error, because even though they are the same value, the data types are different.

 

Example 7

This example shows the NULLIF function, again with two different variable types, and the values don’t match.

SELECT NULLIF(SYSDATE, 'Someday') AS NULLCHECK FROM dual;

Result:

ORA-00932: inconsistent datatypes: expected DATE got CHAR

An error is shown here because the data types do not match.

 

Example 8

This example uses a NULL value for expr1.

SELECT NULLIF(NULL, 'Sunday') AS NULLCHECK FROM dual;

Result:

ORA-00932: inconsistent datatypes: expected - got CHAR

An error is shown because you can’t specify a NULL value for the expr1 value.

 

Example 9

This example uses a NULL value for expr2.

SELECT NULLIF('Sunday', NULL) AS NULLCHECK FROM dual;

Result:

NULLCHECK
Sunday

The result is the value of expr1, which is “Sunday”. Expr2 can be NULL for this function.

 

Similar Functions

Some functions which are similar to the Oracle NULLIF function are:

  • COALESCE – Looks at many input values and returns the first non-NULL value.
  • NVL – Allows you to check a value or expression, and if it is NULL, return a different value.
  • NVL2 – Allows you to check for a value or expression, and specify different values if the result is true or false.
  • CASE – This statement allows the functionality of an IF THEN ELSE statement in SQL.

You can find a full list of Oracle functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your Free PDF: 9 Ways to Improve your Database Skills

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your SQL Function Cheat Sheet Now: