（SQL）3つのテーブルをjoinするクイズ

以下の３つのテーブルがあるとする。

この３つのテーブルから下記のテーブルを作りたい。

特徴的なところは、
（１）テーブルがAが基本
（２）テーブルBにもCにもマッチするデータがないときは空白行となる（最後の行）
（３）BとCのテーブルに共通なデータは1行にまとめる。（3行目）
という感じ。

試行錯誤

（１）まず、AにBをleft joinしてみる。

select * from a0_a
left join a0_b on a0_a.a_no = a0_b.b_no

（２）さらにCをleft joinしてみる。データが同じ行は1行でという条件があるのでON句で指定する。

select * from a0_a
left join a0_b on a0_a.a_no = a0_b.b_no
left join a0_c on a0_a.a_no = a0_c.c_no and a0_b.b_yy = a0_c.c_yy

作りたい表の４～５行目が出ていない。あと「３」の行もよろしくない。

（３）ON句の条件を変えてみる。BのデータがNULLなのもOKとしてみる。

select * from a0_a
left join a0_b on a0_a.a_no = a0_b.b_no
left join a0_c
on a0_a.a_no = a0_c.c_no and (a0_b.b_yy = a0_c.c_yy or a0_b.b_yy is null)

よいところまで近づいた。

（４）あとは4～５行目が足りないだけなので、追加してみてはどうだろう。Cをright joinしてみる。

select * from a0_a

left join a0_b on a0_a.a_no = a0_b.b_no

right join a0_c 

on a0_a.a_no = a0_c.c_no and (a0_b.b_yy = a0_c.c_yy or a0_b.b_yy is null)

a_noがNULLの行がまさしく欲しい行である。

（５）条件で絞ってみる。そしてa_noの列にc_noのデータ出すように少し工夫する。

select a0_c.c_no [a_no], a0_b.*, a0_c.* from a0_a

left join a0_b on a0_a.a_no = a0_b.b_no

right join a0_c 

on a0_a.a_no = a0_c.c_no and (a0_b.b_yy = a0_c.c_yy or a0_b.b_yy is null)

where a_no is null

（６）最後に（３）と（５）の内容をunionで結合すれば出来上がりとなる。

select * from a0_a

left join a0_b on a0_a.a_no = a0_b.b_no

left join a0_c 

on a0_a.a_no = a0_c.c_no and (a0_b.b_yy = a0_c.c_yy or a0_b.b_yy is null)

union all

select a0_c.c_no [a_no], a0_b.*, a0_c.* from a0_a

left join a0_b on a0_a.a_no = a0_b.b_no

right join a0_c 

on a0_a.a_no = a0_c.c_no and (a0_b.b_yy = a0_c.c_yy or a0_b.b_yy is null)

where a_no is null

並び順は少し違うが、それはどうでもよい。欲しい表がゲットできた。

＊

他にも（５）のSQLは以下のようにも書ける。

select * from a0_a

left join a0_c on a0_a.a_no = a0_c.c_no

left join a0_b on a0_a.a_no = a0_b.b_no and (a0_b.b_yy = a0_c.c_yy)

where

exists(select * from a0_b where a0_c.c_no = b_no)

 and 

not exists(select * from a0_b where a0_c.c_no = b_no and a0_c.c_yy = b_yy) 

where句は、テーブルBに同じnoが存在し、かつ、同じnoの同じyyは存在しないという回りくどい条件である。こうしないと2行だけがゲットできない。いろいろ考えてこうなった。もっと簡単に書けないものか。SQLは難しい。

試行錯誤２

欲しいテーブルを少し変えてみる。たとえば「12」のデータについてだけ集めた表が欲しい。

あるいは「13」のデータだけに着目した表ならこうなる。

この表の特徴は

（１）テーブルがAが基本。表の行数はAの行数と同じ。
（２）テーブルBにもCにもマッチするデータがないときは空白行となる
（３）BとCのテーブルに共通なデータは1行にまとめる。
という感じ。

このSQLはすごく簡単。たとえば「１２」に着目した表なら以下のよう。

select * from a0_a

left join a0_b on a0_a.a_no = a0_b.b_no and a0_b.b_yy = 12

left join a0_c on a0_a.a_no = a0_c.c_no and a0_c.c_yy = 12

複雑さから解放された感じ。なぜだろう。