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

sql oracle where-clause notin
dee picture dee · Jun 26, 2012 · Viewed 12.4k times · Source

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

DCookie picture DCookie · Jun 26, 2012

Try this:

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

Related questions

Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More
Conditional WHERE clause with CASE statement in Oracle

I'm brand-new to the Oracle world so this could be a softball. In working with an SSRS report, I'm passing in a string of states to a view. The twist is that the users could also pick a selection from …

Read More
SQL Where Not Exists

I think I have a misunderstanding of how NOT EXISTS work and hope it can be clarified to me. Here is the sample code I am running (also on SQL Fiddle) select sum(col1) col1, sum(col2) col1, sum(col3) …

Read More