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:
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() 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.
|…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.
…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 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.
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! 🙂