How do I handle nulls in NOT IN and NOT LIKE statements in Oracle?

Question

I have a long piece of PL/SQL which is working well except for one small part of the where clause.

I'm expecting that one row should be returned but, because the fields the where clause checks are null, the row is not being returned despite meeting the criteria.

I read a very good note here :
http://jonathanlewis.wordpress.com/2007/02/25/not-in/

It explains how Nulls affect NOT IN statements and I presume it's the same for NOT LIKE as well.

What I haven't been able to find is the comparable replacement I need. Do I need to somehow switch this into an IN and LIKE or provide something to return the row if NUll?

Here is a simplified version of my code.

SELECT * FROM Temp_Table T
WHERE -- Other where constraints
AND (T.Col_One NOT LIKE 'AString'
     OR T.Col_Two NOT IN ('BString','CString'))

In my situation the row would have nulls in both Col_One and Col_Two.

Any help is greatly appreciated.

Thanks.

Brame

Answer 1

Try this:

AND (NVL(T.Col_One,'NuLl') NOT LIKE 'AString'
      OR NVL(T.Col_Two,'NuLl') NOT IN ('BString','CString')) 

Jaundiced

Tested and this worked perfectly. Thanks for the fast response. I was concerned that it actually might insert 'NuLl' as a value but it's only used for comparison. Thanks again. – Brame

This is not a general solution. While in this case we know 'AString' cannot be 'NuLl', in real-life this is probably going to be a bound parameter and the client application would have to guarantee that the 'NuLl' is never passed to it. It's better to simply test for NULLs explicitly and avoid any dependence on "magic" values. – Increate

@Branko, good comment, you are correct, and your solution probably is more performant as well, since it could short-circuit on the first null check. – Jaundiced

Answer 2

Something like this should work:

T.Col_One IS NULL OR T.Col_One NOT LIKE 'AString'
OR T.Col_Two IS NULL OR T.Col_Two NOT IN ('BString','CString')

Please note that...

T.Col_Two NOT IN ('BString','CString') 

...is equivalent to...

T.Col_Two <> 'BString' AND T.Col_Two <> 'CString'

...which "collapses" to NULL in case T.Col_Two is NULL.

Increate

Yes, I have to keep reminding myself that NOT IN is not the same as IN. Thanks. – Brame

Answer 3

By convention NULL is not like or equal to anything - the only valid test would be IS NULL which will return true.

Arching

Answer 4

Check the NVL function in Oracle documentation in : http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

Kirima