Oracle: Coding Around NULL Values

In Oracle, NULLs can complicate your code–a lot.

Suppose, for example, you want to see if two different values are the same in a SQL query. You might be tempted to write something like this:

select *
from my_table
where column1 <> column2

The problem with the above code is that if COLUMN1 is null and COLUMN2 is a non-null value, then that record will not be returned in the above query (and the same applies to the opposite situation where if COLUMN2 is null and COLUMN1 is not null).

Since Oracle didn’t implement the DISTINCT FROM clause that was part of the SQL:1999 standard (sad face) many times developers end up writing ugly code like this:

select *
from my_table
where column1 <> column2
      or
      (column1 is null and column2 is not null)
      or
      (column1 is not null and column2 is null)

This is unsightly. Let’s talk about some alternatives, and their caveats:

Unlikely Value

One way to do it is to use NVL() to ensure that columns are never null–if they are null, just convert them to an “unlikely value.”

select *
from my_table
where nvl(column1, 'uNlIkElYvAlUe') <> nvl(column2, 'uNlIkElYvAlUe')

This is kinda gross, though, since if someone ever *does* enter the unlikely value into COLUMN1 or COLUMN2 and a NULL into the other column, it could throw your results off. Me personally, being a fan of code that works 100.0000000% of the time and not 99.9999999% of the time, means that I can’t really recommend this method–although I have seen others use it, and it does work.

LNNVL()

LNNVL() used to be an undocumented function (I believe it finally made its way into Oracle’s documentation in 10g). The way it works is it accepts a WHERE clause as a parameter…and if the WHERE clause evaluates to TRUE, the LNNVL() function converts it to FALSE….and if the WHERE clause evaluates to FALSE or NULL, LNNVL() converts it to TRUE.

This is subtly different than a NOT because a NOT will convert a TRUE to a FALSE, and a FALSE to a TRUE…but a NOT will leave a NULL predicate alone.

Expression	Evaluates To
…where not(1 = 1)	FALSE (no records)
…where not(1 != 1)	TRUE (records!)
…where not(1 = null)	NULL (no records)
…where not(1 != null)	NULL (no records)
…where lnnvl(1 = 1)	FALSE (no records)
…where lnnvl(1 != 1)	TRUE (records!)
…where lnnvl(1 = null)	TRUE (records!)
…where lnnvl(1 != null)	TRUE (records!)

If the concept of True/False/Null results for SQL where-clauses is new to you, check out this blog article: https://mitchum.blog/null-values-in-sql-queries/

So at first glance, it looks like LNNVL() would be a perfect candidate for our query:

select *
from my_table
where lnnvl(column1 = column2)

If COLUMN1 = ‘X’ and COLUMN2 = ‘X’, the row gets rejected.

And, if COLUMN1 = ‘X’ and COLUMN2 is NULL, it returns the row.

If COLUMN1 = NULL and COLUMN2 = ‘X’ then it returns the row.

And finally, if COLUMN1 = ‘X’ and COLUMN2 = ‘Y’ then it returns the row.

IT’S PERFECT!!!

…except…that pesky scenario where both COLUMN1 and COLUMN2 are null.

SQL> select *
  2  from dual
  3  where lnnvl(null = null);

DUMMY
------
X

1 row selected. 

Elapsed: 00:00:00.093

Of course, you could always concatenate a value onto the end of each column to ensure their non-nullness:

select *
from my_table
where lnnvl(column1 || 'X' = column2 || 'X')

But again, this is a little difficult to understand, and it’s less than ideal…especially if dealing with non-string values (like dates and timestamps that are going to be interpreted based on your NLS settings as they’re converted to string types). So I can’t say I recommend this method either.

DECODE()

Decode is cool, because when comparing two different columns, it considers two NULL values to be equivalent. So you could do something like this:

select *
from my_table
where decode(column1, column2, 'SAME', 'DIFFERENT') = 'DIFFERENT'

This actually works really well. Even though it might be a bit harder for a maintaining developer to pick up on why a DECODE() is being used, rather than a plain not-equals (!=), a well-placed comment ought to clarify things.

Pl/SQL

But…what about Pl/SQL? We can’t use DECODE() in our IF statements!

declare
  v_var1 varchar2(10) := 'X';
  v_var2 varchar2(10) := null;
begin
  if decode(v_var1, v_var2, 'SAME', 'DIFFERENT') = 'DIFFERENT' then
    dbms_output.put_line(q'[They're different]');
  end if;
end;
/

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only

So are we doomed to write nasty IF-conditions like this?

declare
  v_var1 varchar2(10) := 'X';
  v_var2 varchar2(10) := null;
begin
  if v_var1 != v_var2 
     or 
     (v_var1 is null and v_var2 is not null)
     or
     (v_var1 is not null and v_var2 is null)
  then
    dbms_output.put_line(q'[They're different]');
  end if;
end;
/

Nope! I’ve got a simpler way of doing things. Less code, fewer comparisons, more efficient:

declare
  v_var1 varchar2(10) := 'X';
  v_var2 varchar2(10) := null;
begin
  if v_var1 = v_var2 or nvl(v_var1, v_var2) is null then
    null; -- Do nothing
  else 
    dbms_output.put_line(q'[They're different]');
  end if;
end;
/

Hope this was useful! 🙂

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets