oracle - 从具有空值的两个表中查找不匹配的记录

Question

我想从 table2 中找到不匹配的 val 记录。它也有空值

表格1

id  val
1   10
2   20
3   30
4   null

表2

id  val
1   10
3   30
4   null

对于上面的例子，它应该返回 20。

标签： oracle

Answer 1

简单的存在结构在这里工作

with tmp1 as(
  select 1 id, 10 val from dual
  union
  select 2 id, 20 val from dual
  union
  select 3 id, 30 val from dual
  union
  select 4 id, null val from dual),
tmp2 as(
  select 1 id, 10 val from dual
  union
  select 3 id, 30 val from dual
  union
  select 4 id, null val from dual)

select
  tmp1.*
from
  tmp1
where
  not exists(
    select
      null
    from
      tmp2
    where
      nvl(tmp1.val,-1) = nvl(tmp2.val,-1))