Oracle NOT IN does not work will nullable fields

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!

Solution

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?