定義が同じテーブル同士の比較

Question

リンク先のSQL問題を解いています。
2-1-6 同一定義の他テーブルと比較

問題の解答であるSQL文について、
太字個所のcoalesceで行っていることがよくわかりません。

＜問題＞
テーブル1のID、名前、戦闘、采配と、
テーブル2のID、名前、戦闘、采配を比較し(nullとnullは等しい扱いとする)
ID(プライマリキー)が等しくて値が違うデータと、
テーブル1、テーブル2の、片方のみに存在するデータを取得する。
出力形式は、
テーブル1のID(テーブル2のみに存在すればnull),テーブル2のID(テーブル1のみに存在すればnull),
テーブル1の名前,テーブル2の名前,テーブル1の戦闘,テーブル2の戦闘,テーブル1の采配,テーブル2の采配
とする。

テーブル1
ID 名前 戦闘 采配

---

1 織田信長 70 94
2 柴田勝家 86 85
3 前田利家 69 72
5 木下秀吉 59 91
6 徳川家康 null null
7 null null null

テーブル2
ID 名前 戦闘 采配

---

1 吉法師 70 94
2 柴田勝家 86 null
4 明智光秀 61 87
5 木下秀吉 59 91
6 null null null
7 null null null

＜SQL文＞
select a.ID as aID,b.ID as bID,
a.名前 as a名前,b.名前 as b名前,
a.戦闘 as a戦闘,b.戦闘 as b戦闘,
a.采配 as a采配,b.采配 as b采配
from テーブル1 a full outer join テーブル2 b on a.ID=b.ID
where exists(
(select c.ID
, c.名前
, c.戦闘
, c.采配
from テーブル1 c
where c.ID = coalesce(a.ID,b.ID)
minus
select c.ID
, c.名前
, c.戦闘
, c.采配
from テーブル2 c
where c.ID = coalesce(a.ID,b.ID)
)
union all
(select c.ID
, c.名前
, c.戦闘
, c.采配
from テーブル2 c
where c.ID = coalesce(a.ID,b.ID)
minus
select c.ID
, c.名前
, c.戦闘
, c.采配
from テーブル1 c
where c.ID = coalesce(a.ID,b.ID)
)
)
order by coalesce(a.ID,b.ID);

出力結果
AID BID A名前 B名前 A戦闘 B戦闘 A采配 B采配

---

1 1 　 織田信長 吉法師 70 70 94 94
2 2 　柴田勝家 柴田勝家 86 86 85 null
3 null 　前田利家 null 　69 null 72 null
null 4 　null 　明智光秀 null 61 null 87
6 6 　 徳川家康 null null null null null

やってる事としては、
(1)テーブル1とテーブル2を、IDを結合条件として完全外部結合する。
(2)テーブル1にあってテーブル2にないレコードを抽出する。
(3)テーブル2にあってテーブル1にないレコードを抽出する。
(4)上記(2)(3)の和集合を作る。
(5)テーブル1とテーブル2の完全結合表で、(4)の和集合の存在チェックを行い、列を出力。

だと思うんですが、

where c.ID = coalesce(a.ID,b.ID)

はcoalesceで、nullでない値を探しているのでしょうか。

nullとnullは等しい扱いとする

がヒントな気がするのですが。。

Answer 1

where c.ID = coalesce(a.ID,b.ID)

まずcoalesceは（）内の左よりNULL値でないものを順に探して、最初のNULLで無いものを値として返します。
ですのでa.IDがNULLの場合b.IDの値を返します。
a.IDがNULLでない場合はa.IDの値が帰りますし、両方共にNULLで無い場合はa.IDが戻ります。
（）内には幾つも定義ができます。全てがNULLであればNULLが返ります。

その結果よりc.IDと等しいものが選択対象となります。

coalesce使わないSQL

select a.ID as aID,b.ID as bID,
a.名前 as a名前,b.名前 as b名前,
a.戦闘 as a戦闘,b.戦闘 as b戦闘,
a.采配 as a采配,b.采配 as b采配
from テーブル1 a full outer join テーブル2 b on a.ID=b.ID
where exists((select c.ID,c.名前,c.戦闘,c.采配 from テーブル1 c where c.ID = a.ID
        minus select c.ID,c.名前,c.戦闘,c.采配 from テーブル2 c where c.ID = b.ID)
        union all
             (select c.ID,c.名前,c.戦闘,c.采配 from テーブル2 c where c.ID = b.ID
        minus select c.ID,c.名前,c.戦闘,c.采配 from テーブル1 c where c.ID = a.ID))
order by coalesce(a.ID,b.ID);

Answer 2

COALESCEは指定された値からNULLでない最初の値を返します。
そのため、a.IDとb.IDのいずれかに値が入っている場合にNULLではない方が実際の値となります。
どちらもNULLではない場合、a.IDの値となりますが、外部結合の条件からどちらも同じ値になるので問題ありません。
これにより、副問い合わせで使用している3つめのテーブルcでa、bいずれかにしかないIDについても抽出することができます。

結合条件からは出てきませんが、仮にa.ID、b.IDの両方がNULLだった場合、COALESCEはNULLを返しますが、NULLは=では一致しませんので、この場合もテーブルcからは抽出されません。