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.

Answer 1

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.

Answer 2

Try this:

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