今回のクエリの説明例として、データベーススペシャリスト平成27年春期 午前Ⅱ 問11 の問題・解答を引用します。
＜問題＞
庭に訪れた野鳥の数を記録する"観測"表がある。観測のたびに通番を振り、鳥名と観測数を記録している。AVG関数を用いて鳥名別に野鳥の観測数の平均値を得るために、一度でも訪れた野鳥については、観測されなかったときの観測数を0とするデータを明示的に挿入する。SQL文のaに入る字句はどれか。ここで、通番は初回を1として、観測のタイミングごとにカウントアップされる。
＜解答＞
CREATE TABLE 観測 ( 通番 INTEGER, 鳥名 CHAR(20), 観測数 INTEGER, PRIMARY KEY(通番, 鳥名)) INSERT INTO 観測 SELECT DISTINCT obs1.通番, obs2.鳥名, 0 FROM 観測 AS obs1, 観測 AS obs2 WHERE NOT EXISTS ( SELECT * FROM 観測 AS obs3 WHERE obs1.通番 = obs3.通番 AND obs2.鳥名 = obs3.鳥名)
このクエリの目的は試験問題の解説記事に記載されています。
"観測"表に存在しないすべての(通番，鳥名)の組み合わせについて、観測数を0にしてレコードを新規追加する
平成27年春期問11 SQL文のaに入る字句はどれか｜データベーススペシャリスト.com
記事ではクエリについてかなり丁寧に説明されていますが、それでもピンときにくい点があるかと思います。そこで、このクエリの分かりにくい点を解説し、集合に含まれない組み合わせをクエリで抽出する方法を説明します。
＜観測表＞
| 通番 | 鳥名 | 観測数 |
|---|---|---|
| 1 | ヒバリ | 1 |
| 1 | メジロ | 2 |
| 2 | スズメ | 1 |
SQLの実行順序は以下のとおり決まっています。
FROM → WHERE → GROUP BY → HAVING → SELECT
この順に沿ってクエリを読み解きます。
(1) FROM
FROM 観測 AS obs1, 観測 AS obs2
FROM句に複数のテーブル名が指定されており、クロス結合が行われます。その結果、以下のワークテーブルが作られます。
| obs1.通番 | obs1.鳥名 | obs1.観測数 | obs2.通番 | obs2.鳥名 | obs2.観測数 |
|---|---|---|---|---|---|
| 1 | ヒバリ | 1 | 1 | ヒバリ | 1 |
| 1 | ヒバリ | 1 | 1 | メジロ | 2 |
| 1 | ヒバリ | 1 | 2 | スズメ | 1 |
| 1 | メジロ | 2 | 1 | ヒバリ | 1 |
| 1 | メジロ | 2 | 1 | メジロ | 2 |
| 1 | メジロ | 2 | 2 | スズメ | 1 |
| 2 | スズメ | 1 | 1 | ヒバリ | 1 |
| 2 | スズメ | 1 | 1 | メジロ | 2 |
| 2 | スズメ | 1 | 2 | スズメ | 1 |
クエリで使われるのは "obs1.通番", "obs2.鳥名" のみなので、説明の為に簡略化します。本来はSELECT句で評価（射影）されることに注意してください。
| obs1.通番 | obs2.鳥名 |
|---|---|
| 1 | ヒバリ |
| 1 | メジロ |
| 1 | スズメ |
| 1 | ヒバリ |
| 1 | メジロ |
| 1 | スズメ |
| 2 | ヒバリ |
| 2 | メジロ |
| 2 | スズメ |
ここでポイントになるのが、obs1, obs2 という個別の表が1つのワークテーブルになり、元のテーブル名が "obs1.通番" のようにワークテーブルの列名の一部になったということです。このようにイメージしたほうが以降の処理を考えやすくなります。
ここで得られた結果は (通番, 鳥名) の全ての組み合わせとなっています。
(2) WHERE
WHERE NOT EXISTS ( SELECT * FROM 観測 AS obs3 WHERE obs1.通番 = obs3.通番 AND obs2.鳥名 = obs3.鳥名)
WHERE句に obs1, obs2, obs3 と複数のテーブル名が登場して混乱しそうになりますが、obs1, obs2 はワークテーブルへと変化したことを思い出してください。ワークテーブルが work という名前とすれば、以下の形に変わります。
WHERE NOT EXISTS ( SELECT * FROM 観測 AS obs3 WHERE work.通番 = obs3.通番 AND work.鳥名 = obs3.鳥名)
これは単純な相関サブクエリの形をしていることが分かります。
この相関サブクエリの意味を紐解きます。
WHERE句の評価は1行ずつ行われます。ここではワークテーブルの (1, スズメ) を評価しているとします。これを相関サブクエリに値として代入します。
WHERE NOT EXISTS ( SELECT * FROM 観測 AS obs3 WHERE 1 = obs3.通番 AND 'スズメ' = obs3.鳥名)
このWHERE句は観測表(obs3)に (1, スズメ) が存在しないときに成立します。これが問題文の「観測されなかったとき」に対応します。
つまり、ワークテーブルの (通番, 鳥名) の全ての組み合わせから観測表に存在しない組み合わせを選択しています。
| obs1.通番 | obs2.鳥名 |
|---|---|
| 1 | スズメ |
| 1 | スズメ |
| 2 | ヒバリ |
| 2 | メジロ |
(3) SELECT
SELECT DISTINCT obs1.通番, obs2.鳥名, 0
このSELECT句より以下の結果が得られます。
| obs1.通番 | obs2.鳥名 | - |
|---|---|---|
| 1 | スズメ | 0 |
| 2 | ヒバリ | 0 |
| 2 | メジロ | 0 |
今回のクエリは「集合に含まれない組み合わせ」を抽出するため、全ての組み合わせ（FROM句）から既に存在する組み合わせを引き算する（WHERE句）ことで実現していました。
＜参考文献＞
SQLの実行順序やFROM句をクロス結合として考えることの参考にしました。
