10-200 having句にこだわったexists述語

SQLパズル

Table1                    Table2
ID   ColA  ColB  ColC     ID   ColX
---  ----  ----  ----     ---  ----
101     1  null     5     101     1
101     1  null  null     101     3
101     3     5     7     101     5
101     5    99    99     101     7
101  null  null    99     102     2
101  null  null  null     102     5
102  null     2     5
103  null  null  null

以下の条件を満たす、Table1の行を出力する。

Table1の、IDが101で、
かつ
ColAがnull、または、Table2にIDが101でColA=ColXな行が存在する。
かつ
ColBがnull、または、Table2にIDが101でColB=ColXな行が存在する。
かつ
ColCがnull、または、Table2にIDが101でColC=ColXな行が存在する。

出力結果
ID   ColA  ColB  ColC
---  ----  ----  ----
101     1  null     5
101     1  null  null
101     3     5     7

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table Table1(ID,ColA,ColB,ColC) as
select 101,   1,null,   5 from dual union
select 101,   1,null,null from dual union
select 101,   3,   5,   7 from dual union
select 101,   5,  99,  99 from dual union
select 101,null,null,  99 from dual union
select 101,null,null,null from dual union
select 102,null,   2,   5 from dual union
select 103,null,null,null from dual;

create table Table2(ID,ColX) as
select 101,1 from dual union
select 101,3 from dual union
select 101,5 from dual union
select 101,7 from dual union
select 102,2 from dual union
select 102,5 from dual;

SQL

--■■■having句でandとorを組み合わせる方法■■■
select ID,ColA,ColB,ColC
  from Table1 a
 where ID = 101
   and exists(select 1
                from Table2 b
               where b.ID = a.ID
              having (a.ColA is null or max(case when b.ColX = a.ColA then 1 else 0 end) = 1)
                 and (a.ColB is null or max(case when b.ColX = a.ColB then 1 else 0 end) = 1)
                 and (a.ColC is null or max(case when b.ColX = a.ColC then 1 else 0 end) = 1))
order by ID,ColA,ColB,ColC;

--■■■having句でブール代数を使う方法■■■
select ID,ColA,ColB,ColC
  from Table1 a
 where ID = 101
   and exists(select 1
                from Table2 b
               where b.ID = a.ID
              having max(case when b.ColX = a.ColA or a.ColA is null then 1 else 0 end)
                   * max(case when b.ColX = a.ColB or a.ColB is null then 1 else 0 end)
                   * max(case when b.ColX = a.ColC or a.ColC is null then 1 else 0 end) = 1)
order by ID,ColA,ColB,ColC;

解説

having句で、
max関数とcase式を組み合わせて、存在肯定命題の真偽を調べてます。

10-169 having句にこだわったupdate文
10-198 existsとforallとunique

---

Thinking in SQL(英語)
Thinking in Aggregates(英語)

---