Oracle NOT IN does not work will nullable fields

Question

I have to compare two tables with identical structure (int not null,int not null, varchar2). In both tables field3 is a nullable.

I have the next SQL:

Select 
   t1.field1, t1.field2, t1.field3)  
From
   table1 t1
Where  (field1,field2,field3)
   not in 
   (select field1,
    field2,field3
    from table2 t2)

When the field3 is NULL in any of them (t1 or t2) the query does not return any row. For instance I want to return a row from this data but it returns nothing at all.

Table 1

field1    field2    field3
1         2         <NULL>

Table 2

field1    field2    field3
1         2         'some text data' 

There is workaround to fix such thing by using the NVL function: NVL(field3, 'dummytextorwhatever') but I dont want to include such horrible thing into my code. Any idea to solve this problem with nullable fields?

Thank you!

Answer 1

This is known behavior with NOT IN when there are nulls in either the main table or the sub-query's result sets. As @DrCopyPaste puts it so well

"when writing WHERE value NOT IN (x, y, z) this will be internally interpreted as WHERE value != x AND value != y AND value != z, and comparing against NULL (either for equality or unequality) always yields FALSE"

The simple answer is to use NOT EXISTS:

Select 
   t1.field1, t1.field2, t1.field3)  
From
   table1 t1
Where  not exists   
   (select  null   from table2 t2
    where t2.field1 = t1.field1
    and t2.field2 = t1.field2
    and t2.field3 = t1.field3 )

An anti-join will produce the same result

Select 
   t1.field1, t1.field2, t1.field3)  
From
   table1 t1
     left join table2 t2
       on t2.field1 = t1.field1
       and t2.field2 = t1.field2
       and t2.field3 = t1.field3 
where t2.field1 is null

---

"why do you select a null at the beginning?"

Because with NOT EXISTS it doesn't matter what the sub-query returns. All that matters is that it returns a non-empty result set. It could have been 1 or field1 but it really doesn't matter, so why not null?

Answer 2

Try not exists

Select 
  t1.field1, 
  t1.field2, 
  t1.field3
From
  table1 t1
where not exists 
  (select 1
    from table2 t2
  where 
  t1.field1=t2.field1 
  and t1.field2=t2.field2 
  and t1.field3=t2.field3
  )

Sample test

with table1(field1,field2,field3) as
(select 1,2,null from dual),
        table2(field1,field2,field3) as
(select 1,2,'something' from dual)

Select 
  t1.field1, 
  t1.field2, 
  t1.field3
From
  table1 t1
where not exists 
  (select 1
    from table2 t2
  where 
  t1.field1=t2.field1 
  and t1.field2=t2.field2 
  and t1.field3=t2.field3
  )

Output

FIELD1 FIELD2 FIELD3
1      2      

Answer 3

Try to use NVL or Coalesce operators, like this

Select 
   t1.field1, t1.field2, t1.field3 
From
   table1 t1
Where  (nvl(field1,0),nvl(field2,0),nvl(field3,0))
   not in 
   (select nvl(field1,0),nvl(field2,0),nvl(field3,0)
    from table2 t2)

but if in tables data there is some data equals 0 select will be return that row, because nvl(field1,0)=nvl(field2,0) when field1=0 and field2=null, so you can use any value(you should be confident ) wich no exists in your tables data for example -99(nvl(field,-99))

or you can use exists/not exists

Answer 4

Based on your query, you are trying to find all the times in table1 that do not exist in table2. Instead of NOT IN, consider using a MINUS...

Select t1.field1, t1.field2, t1.field3
From   table1 t1
Minus
select t2.field1, t2.field2, t2.field3
from   table2 t2;