Oracle NOT IN 不起作用将可空字段

Question

我必须比较两个结构相同的表(int not null，int not null，varchar2)。在这两个表中，field3为空。

我有下一个 SQL：

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

当 field3 在任何一个(t1 或 t2)中为 NULL 时，查询不会返回任何行。例如，我想从这些数据中返回一行，但它什么也没返回。

表1

field1    field2    field3
1         2         <NULL>

表2

field1    field2    field3
1         2         'some text data' 

可以使用 NVL 函数修复此类问题：NVL(field3, 'dummytextorwhatever')但我不想将如此可怕的事情包含在我的代码中。有什么想法可以用可为空的字段解决这个问题吗？

谢谢！

Answer 1

这是当主表或子查询的结果集中存在空值时 NOT IN 的已知行为。正如@DrCopyPaste所说

"当写WHERE value NOT IN (x, y, z)时，这将在内部解释为WHERE value != x AND value != y AND value != z，并且与NULL进行比较(无论是平等还是不平等)总是产生FALSE">

简单的答案是使用不存在：

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 )

反联接将产生相同的结果

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

---

"为什么一开始就选择空值？">

因为对于 NOT EXIST，子查询返回什么并不重要。重要的是它返回一个非空的结果集。它可能是1或field1但这真的无关紧要，所以为什么不null呢？

Answer 2

尝试使用NVL或Coalesce运算符，如下所示

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)

但是如果在表中数据中有一些数据等于 0 选择将返回该行，因为nvl(field1,0)=nvl(field2,0)时field1=0和field2=null，所以您可以使用任何值(您应该有信心 ) 您的表数据中不存在，例如-99(nvl(field,-99))

或者您可以使用存在/不存在

Answer 3

尝试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
)

样品测试

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
)

输出

FIELD1 FIELD2 FIELD3
1      2      

Answer 4

根据您的查询，您正在尝试查找表 1 中不存在的表 2 中的所有时间。 而不是 NOT IN，请考虑使用减号...

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