1 連絡と準備

小テスト❽ を実施します。
- シラバス記載のように、小テストは最終評価の 35% に相当します。
- 遅刻・欠席等により追試験を希望する場合は第01回講義で案内した手続きをしてください。
Unity 1-Week GAME JAM
- https://unityroom.com/unity1weeks
- 開催終了 … お題「もうひとつ」

1.1 ハンズオン学習の準備

次の手順でSQL演習環境の立ち上げと、教材の更新を取得してください。

SQL演習環境の動作確認@ 第04回講義
教材の更新の取得@ 第04回講義

また、演習環境の from-teacher/11/init-n_db.sql を実行して、次のテーブル群を作成してください。

n_items
n_jobs
n_characters
n_character_items

これらは、次の関係となっています (テーブルの定義を確認しておいてください)。

また、前回講義で使用したテーブル群 (x_****) も使用するので、from-teacher/10/create-x_db.sql と insert-x_db_01.sql もあらためて実行しておいてください。

1.2 今回の講義の概要

前回の講義では、内部結合 (INNER JOIN) を扱いました。

内部結合では、次のように FROM 句を n_characters AS c JOIN n_jobs AS j ON c.job_id = j.job_id のように指定することで、n_characters と n_jobs の 2つのテーブルを結合したもの に対して、クエリを発行することができました。

SELECT
  c.character_id,
  c.name,
  j.name AS "job"
FROM
  n_characters AS c
  JOIN n_jobs AS j ON c.job_id = j.job_id
ORDER BY
  c.character_id;

SQL標準 (PostgreSQL を含む) では INNER JOIN を、JOIN という省略形で表記することができました。

たとえば n_jobs と n_characters テーブルが次のような内容であるとします (ここで、下線は「主キー」であること、空欄は NULL であることを表しています)。

▼ n_jobs テーブル

job_id	name
1	Fighter
2	Monk
3	Ninja
4	Samurai
5	Priest
6	Wizard

▼ n_characters テーブル

character_id	name	job_id
1	Marvin	5
2	Zach
3	Charlie	6
4	Tom	1
5	Alice	5

このとき、select-inner_join_01.sql の実行結果は次のようになります。

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

ここで注意すべきは、character_id が 2 の Zach が、結果セットに含まれないことです。内部結合においては ON で指定した結合条件 (ここでは c.job_id = j.job_id ) が TRUE になった行だけが結果セットに含まれるという特性がありました (前回講義で確認済み)。

Zach の場合、n_characters.job_id は NULL であり、SQL の3値論理では NULL を含む条件式の評価結果は常に UNKNOWN となることから、結果セットから除外されています。

ON に OR IS NULL を追加すると…

Zach を結果に含めたいからといって select-inner_join_01.sql の結合条件を ON c.job_id = j.job_id OR c.job_id IS NULL のようにしてしまうと、以下のように意図せぬ結果が得られるので注意してください。

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            2 | Zach    | Fighter
            2 | Zach    | Monk
            2 | Zach    | Ninja
            2 | Zach    | Samurai
            2 | Zach    | Priest
            2 | Zach    | Wizard
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

c.job_id IS NULL の行が、n_jobs 側の全行と結び付くため、Zach の行がジョブの数だけ増えてしまいます (後半で解説する「直積」に近い処理になってしまいます)。

このような内部結合の挙動は、たとえば「ジョブ別のキャラ人数を集計したい」といった目的には適していません。

SELECT
  j.job_id,
  MAX(j.name) AS "name",
  COUNT(*)
FROM
  n_characters AS c
  JOIN n_jobs AS j ON c.job_id = j.job_id
GROUP BY
  j.job_id
ORDER BY
  j.job_id;

実行結果は次のようになります。

 job_id |  name   | count 
--------+---------+-------
      1 | Fighter |     1
      5 | Priest  |     2
      6 | Wizard  |     1

この結果セットには Monk や Ninja、Samurai といったキャラが 0 人のジョブの情報が欠落しています。内部結合では、n_characters 側に対応する行が存在しないジョブは、集計対象にすら含まれません。

このように「0 人であること」も集計結果に含めたい場合、内部結合では要件を満たすことができません。この問題を解決する方法として、今回の講義では「外部結合 OUTER JOIN」について学んでいきます。

1.2.1 演習

select-inner_join_01.sql の FROM を次のように書き換えても 結果セットの内容が同じであること を確認してください (つまり、内部結合では JOIN の順番が結果に影響しないことを確認してください)。
- 変更前: FROM n_characters AS c JOIN n_jobs AS j ON c.job_id = j.job_id
- 変更後: FROM n_jobs AS j JOIN n_characters AS c ON c.job_id = j.job_id

確認後は、変更前の状態に戻しておいてください。

1.2.2 SQLドリル💻 (前回の復習)

ex-01_1.sql 👉 サブクエリを使用して n_characters と n_jobs から次のような結果を得る SQL を記述せよ。
- サブクエリ (副問い合わせ) については前回講義で学習済み

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            2 | Zach    |
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

ex-01_2.sql 👉 サブクエリを使用して n_characters と n_jobs から次のような結果を得る SQL を記述せよ。

 job_id |  name   | count 
--------+---------+-------
      1 | Fighter |     1
      2 | Monk    |     0
      3 | Ninja   |     0
      4 | Samurai |     0
      5 | Priest  |     2
      6 | Wizard  |     1

ex-01_3.sql 👉 サブクエリを使用して n_characters と n_jobs から次のような結果を得る SQL を記述せよ。
- job カラムが NULL の箇所を --- で表示する。
- ヒント: COALESCEを利用

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            2 | Zach    | ---
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

1.2.3 SQLドリル💻 (EX)

前回講義で扱ったテーブル群 ( x_○○○ ) を使用する演習問題です。

ex-01_4.sql 👉 次のように各ジョブの最高レベルのキャラを出力する SQL を記述せよ。
- ヒント: 内部結合に加えて WHERE 句で相関サブクエリを使用する

 job_id |   job   | character_id |  name  | level 
--------+---------+--------------+--------+-------
      1 | Fighter |           17 | Wendy  |    56
      2 | Monk    |            5 | Ivan   |    39
      3 | Ninja   |            2 | Zach   |    62
      4 | Samurai |            9 | Walter |    73
      5 | Priest  |           15 | Trent  |    50
      6 | Wizard  |           13 | Mallet |    64

ex-01_5.sql 👉 2種類の異なるアイテムの「セット販売」を考える (価格は単品合計の3割引きで、端数は四捨五入する)。次のように割引後価格が 3,500 ゴールド以上となるアイテムセットを全て出力する SQL を記述せよ。
- ヒント: x_items テーブル同士を内部結合する

     item_1 (price)      |     item_2 (price)      | discounted_price 
-------------------------+-------------------------+------------------
 Potion (200)            | High Mana Potion (5000) |             3640
 High Potion (600)       | High Mana Potion (5000) |             3920
 Mega Potion (1200)      | Giga Potion (4000)      |             3640
 Mega Potion (1200)      | High Mana Potion (5000) |             4340
 Giga Potion (4000)      | Mana Potion (1000)      |             3500
 Giga Potion (4000)      | High Mana Potion (5000) |             6300
 Giga Potion (4000)      | Angel Feather (1200)    |             3640
 Giga Potion (4000)      | Climbing Rope (1000)    |             3500
 Mana Potion (1000)      | High Mana Potion (5000) |             4200
 High Mana Potion (5000) | Antidote (300)          |             3710
 High Mana Potion (5000) | Paralyze Cure (600)     |             3920
 High Mana Potion (5000) | Angel Feather (1200)    |             4340
 High Mana Potion (5000) | Chimera Wing (500)      |             3850
 High Mana Potion (5000) | Torch (820)             |             4074
 High Mana Potion (5000) | Climbing Rope (1000)    |             4200
(15 行)

2 外部結合

内部結合 (INNER JOIN) において、結合条件を満たさない行は結果セットから除外されてしまいました。これに対して 外部結合 (OUTER JOIN) は結合条件を満たさない行も結果セットに含めることができる結合方法となります。

たとえば、LEFT OUTER JOIN (省略形 LEFT JOIN ) を用いると、左側のテーブルに存在する行は、右側に対応する行がなくても結果セットに残すことができるようになります。このような結合を「左外部結合」「左側外部結合」「左結合」といいます。そのため「キャラが 0 人のジョブも含めた集計」などがシンプルに記述できます。

右側のテーブルに存在する行を基準に結合する RIGHT OUTER JOIN (省略形 RIGHT JOIN ) もあります。

2.1 外部結合の使用例

さきほどの select-inner_join_01.sql の 第07行目 を、以下のように JOIN から LEFT JOIN に書き換えます。

非省略形で記述する場合、INNER JOIN から LEFT OUTER JOIN に書き換えます。

SELECT
  c.character_id,
  c.name,
  j.name AS "job"
FROM
  n_characters AS c
  LEFT JOIN n_jobs AS j ON c.job_id = j.job_id -- ◀ JOIN から LEFT JOIN に変更
ORDER BY
  c.character_id;

この SQL を実行すると、結果セットが…

(INNER JOIN の場合)

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

…から、以下のよう変化します。内部結合では除外されていた character_id = 2 の Zach の行が、結果セットに含まれていることが分かります。

(LEFT OUTER JOIN の場合)

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            2 | Zach    |
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

以上のように FROM 句の n_characters AS c LEFT JOIN n_jobs AS j において LEFT JOIN キーワードの左側のテーブル、つまり n_characters に存在する行は 右側のテーブルに対応する行がなくても (＝結合条件を満たさない行であっても) 結果セットに含まれるようになります。

また、その場合、当該行で右側テーブルを参照するカラムの値は NULL となります。

2.2 左外部結合 (LEFT JOIN) と右外部結合 (RIGHT JOIN) の挙動の違い

つづいて、外部結合を利用して、次のような ジョブ別のキャラクタ人数の集計 を取得するような SQL を考えていきます。

 job_id |  name   | count 
--------+---------+-------
      1 | Fighter |     1
      2 | Monk    |     0
      3 | Ninja   |     0
      4 | Samurai |     0
      5 | Priest  |     2
      6 | Wizard  |     1

まず、さきほどの内部結合を用いた SQL である select-inner_join_02.sql (＝ジョブ別のキャラ人数の集計) の 第07行目 を、以下のように JOIN から LEFT JOIN に書き換えてみます。

SELECT
  j.job_id,
  MAX(j.name) AS "name",
  COUNT(c.character_id)
FROM
  n_characters AS c
  LEFT JOIN n_jobs AS j ON c.job_id = j.job_id -- ◀ JOIN から LEFT JOIN に変更
GROUP BY
  j.job_id
ORDER BY
  j.job_id;

これを実行すると、次のような実行結果となります (実際に実行してみてください)。

 job_id |  name   | count 
--------+---------+-------
      1 | Fighter |     1
      5 | Priest  |     2
      6 | Wizard  |     1
        |         |     1

この結果は、期待する出力 (ジョブ別のキャラ人数の集計) とは異なっていることが分かります。

これは n_characters AS c LEFT JOIN n_jobs AS j のように指定しているため、左側のテーブル (＝n_characters) を基準に行を残す形で集計が行われていることに起因します。

ここでは「ジョブ別のキャラクタ人数」を集計したいため、n_jobs (＝右側テーブル) を基準に外部結合する必要があります。その場合、次のように RIGHT JOIN (右外部結合) を用います。

SELECT
  j.job_id,
  MAX(j.name) AS "name",
  COUNT(c.character_id) -- ◀ 注意
FROM
  n_characters AS c
  RIGHT JOIN n_jobs AS j ON c.job_id = j.job_id -- ◀ JOIN から RIGHT JOIN に
GROUP BY
  j.job_id
ORDER BY
  j.job_id;

これを実行すると、以下のように期待どおりの結果が得られます。

 job_id |  name   | count 
--------+---------+-------
      1 | Fighter |     1
      2 | Monk    |     0
      3 | Ninja   |     0
      4 | Samurai |     0
      5 | Priest  |     2
      6 | Wizard  |     1

なお、第04行目 を COUNT(*) としてしまうと意図せぬ結果になるので注意してください。実際に試してみてください。

第05回講義で学んだように、COUNT(*) は行そのものの数を数えるため、キャラが存在しないジョブでも 1 行としてカウントされてしまいます。

2.3 左外部結合と右外部結合の書き換え

左外部結合 (LEFT JOIN) と 右外部結合 (RIGHT JOIN) は、相互に書き換えが可能となっています。例えば、さきほどの右外部結合の SQL (select-outer_join_02-b.sql) は、次のような 左外部結合 の SQL に書き換えが可能です。

FROM
  n_jobs AS j
  LEFT JOIN n_characters AS c ON c.job_id = j.job_id

つまり、次の2つは等価となります。

FROM n_characters AS c RIGHT JOIN n_jobs AS j ON ... 👈 右外部結合
FROM n_jobs AS j LEFT JOIN n_characters AS c ON ... 👈 左外部結合

このように、外部結合では「どのテーブルを基準として行を残したいか」を意識して結合の向き (LEFT or RIGHT) を指定することが大切になってきます。

2.3.1 定着確認

次の結合と論理的に同等な結果セットとなるように RIGHT JOIN を用いて書き換えよ。
- FROM jobs AS j LEFT JOIN characters AS c ON c.job_id = j.job_id
- 答え: FROM characters AS c RIGHT JOIN jobs AS j ON c.job_id = j.job_id
標準SQLにおいて、内部結合を表す正式なキーワードは (　　　) である。なお、このキーワードは JOIN という省略形で記述することができる。括弧にあてはまる語を答えよ。
- 答え: INNER JOIN
標準SQLにおいて、右側のテーブルを基準に結合を行う外部結合を指定する正式なキーワードは (　　　) である。なお、このキーワードは RIGHT JOIN という省略形で記述することができる。括弧にあてはまる語を答えよ。
- 答え: RIGHT OUTER JOIN
内部結合と WHERE を組み合わせることで、一般に、外部結合を用いた場合と等価な結果セットを得ることができる。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切ではない

2.3.2 SQLドリル💻

ex-02_1.sql 👉 n_characters と n_jobs から次のような結果を得る SQL を記述せよ。ただし、右外部結合 RIGHT JOIN を使用すること。特定のジョブに就いていない場合は --- を出力すること。
- サブクエリを用いたバージョン (ex-01_3.sql) と SQL の構成を比較せよ (可読性や保守性など)。

 character_id |  name   |   job   
--------------+---------+---------
            1 | Marvin  | Priest
            2 | Zach    | ---
            3 | Charlie | Wizard
            4 | Tom     | Fighter
            5 | Alice   | Priest

2.4 3つ以上のテーブルの外部結合

内部結合 (INNER JOIN) の場合と同様に、外部結合 (OUTER JOIN) でも 3つ以上のテーブルを結合すること ができます。

たとえば、n_characters、n_items、n_character_items の3つテーブルが次のような内容であるとします (ここで、下線は「主キー」であること、空欄は NULL であることを表しています)。

▼ n_characters テーブル (再掲)

character_id	name	job_id
1	Marvin	5
2	Zach
3	Charlie	6
4	Tom	1
5	Alice	5

▼ n_items テーブル

item_id	name
1	Potion
2	High Potion
3	Mega Potion
4	Giga Potion
5	Mana Potion

▼ n_character_items テーブル (中間テーブル)

character_id	item_id	qty
1	1	3
1	2	2
1	3	1
2	3	1
4	1	2
4	5	3

このとき、次のような キャラを基準として所持アイテムの一覧 を取得したいとします。

 character_id |  name   |    name     | qty 
--------------+---------+-------------+-----
            1 | Marvin  | Potion      |   3
            1 | Marvin  | High Potion |   2
            1 | Marvin  | Mega Potion |   1
            2 | Zach    | Mega Potion |   1
            3 | Charlie |             |
            4 | Tom     | Mana Potion |   3
            4 | Tom     | Potion      |   2
            5 | Alice   |             |

これは、次のような SQL によって取得することができます。

SELECT
  c.character_id,
  c.name,
  i.name,
  ci.qty
FROM
  n_characters AS c
  LEFT JOIN n_character_items AS ci ON c.character_id = ci.character_id
  LEFT JOIN n_items AS i ON ci.item_id = i.item_id
ORDER BY
  c.character_id;

ここでも FROM 句のなかでの「結合の記述順」が重要となってきます。外部結合のみで構成されている場合、最終的な結果は、記述した順番に 1 個ずつ結合していった場合と同じになります。つまり、記述順に、まず n_characters と n_character_items を結合し、さらに、その結果に対して n_items を結合したものと同じになります。

補足

ここでの「最終的な結果は、記述した順番に結合した場合と同じ」という説明は、「結果の解釈についての話」です。データベース内部においては、最適化の都合により、必ずしも記述した順番で処理されるとは限らないので注意してください。ただし、外部結合のみで構成されている場合、記述した順に結合した場合と同じになることが保証されます。

具体的には、まず n_characters AS c LEFT JOIN n_character_items AS ci ON c.character_id = ci.character_id によって、キャラを基準とした次のような結果セットが内部的に得られます。

 character_id |  name   | job_id | character_id | item_id | qty 
--------------+---------+--------+--------------+---------+-----
            1 | Marvin  |      5 |            1 |       1 |   3
            1 | Marvin  |      5 |            1 |       2 |   2
            1 | Marvin  |      5 |            1 |       3 |   1
            2 | Zach    |        |            2 |       3 |   1
            3 | Charlie |      6 |              |         |
            4 | Tom     |      1 |            4 |       1 |   2
            4 | Tom     |      1 |            4 |       5 |   3
            5 | Alice   |      5 |              |         |

つづいて、その結果セットに対して LEFT JOIN n_items AS i ON ci.item_id = i.item_id が適用され、次のような結果になります。

 character_id |  name   | job_id | character_id | item_id | qty | item_id |    name     
--------------+---------+--------+--------------+---------+-----+---------+-------------
            1 | Marvin  |      5 |            1 |       1 |   3 |       1 | Potion
            1 | Marvin  |      5 |            1 |       2 |   2 |       2 | High Potion
            1 | Marvin  |      5 |            1 |       3 |   1 |       3 | Mega Potion
            2 | Zach    |        |            2 |       3 |   1 |       3 | Mega Potion
            3 | Charlie |      6 |              |         |     |         |
            4 | Tom     |      1 |            4 |       5 |   3 |       5 | Mana Potion
            4 | Tom     |      1 |            4 |       1 |   2 |       1 | Potion
            5 | Alice   |      5 |              |         |     |         |

最後に、SELECT 句によって必要なカラムだけが射影され、さらに ORDER BY により整列されて、最終的に次のような結果セットが得られます。

 character_id |  name   |    name     | qty 
--------------+---------+-------------+-----
            1 | Marvin  | Potion      |   3
            1 | Marvin  | High Potion |   2
            1 | Marvin  | Mega Potion |   1
            2 | Zach    | Mega Potion |   1
            3 | Charlie |             |
            4 | Tom     | Mana Potion |   3
            4 | Tom     | Potion      |   2
            5 | Alice   |             |

このように、外部結合を 2 つ以上用いる場合は「どのテーブルを基準に、どの順序で結合するか」をしっかりと意識することが重要となります。

LEFT JOIN と RIGHT JOIN の併用は避ける

3つ以上のテーブルを結合する場合、FROM 句において LEFT JOIN と RIGHT JOIN を混在させることは、SQL の仕様上は可能 となっています。

ただし、実務においては「可読性」や「保守性」の観点から、外部結合はどちらか一方に統一して記述することが推奨されます。特に、基準となるテーブルを左側に置き、そこから関連するテーブルを順に結合していくという考え方が分かりやすいため、LEFT JOIN で統一して記述することが一般的となっています。

2.4.1 SQLドリル💻

init-n_db.sql で初期化されるテーブル群 n_**** について、次の各問いに答えよ。

ex-03_1.sql 👉 次のような結果 (アイテムを基準とした所持キャラの一覧) を得る SQL を外部結合を用いて記述せよ。

 item_id |    name     | holder | qty 
---------+-------------+--------+-----
       1 | Potion      | Marvin |   3
       1 | Potion      | Tom    |   2
       2 | High Potion | Marvin |   2
       3 | Mega Potion | Zach   |   1
       3 | Mega Potion | Marvin |   1
       4 | Giga Potion |        |
       5 | Mana Potion | Tom    |   3

ex-03_2.sql 👉 次のような結果 (アイテムを基準とした所持キャラの一覧) を得る SQL を外部結合を用いて記述せよ。
- NULL の処理に注意すること

 item_id |    name     | holder |   job   | qty 
---------+-------------+--------+---------+-----
       1 | Potion      | Marvin | Priest  |   3
       1 | Potion      | Tom    | Fighter |   2
       2 | High Potion | Marvin | Priest  |   2
       3 | Mega Potion | Marvin | Priest  |   1
       3 | Mega Potion | Zach   | ---     |   1
       4 | Giga Potion | ---    | ---     |   0
       5 | Mana Potion | Tom    | Fighter |   3

ex-03_3.sql 👉 次のような結果 (キャラを基準とした所持アイテム総数の一覧) を得る SQL を外部結合を用いて記述せよ。
- アイテム未所持のキャラについても出力に含めること
- 文字列の結合については第03回講義で学習済み

 character_id |    name (job)    | total_qty 
--------------+------------------+-----------
            1 | Marvin (Priest)  |         6
            2 | Zach             |         1
            3 | Charlie (Wizard) |         0
            4 | Tom (Fighter)    |         5
            5 | Alice (Priest)   |         0

ex-03_4.sql 👉 次のような結果 (アイテムを基準とした所持キャラ人数) を得る SQL を外部結合を用いて記述せよ。

 item_id |    name     | holder_count 
---------+-------------+--------------
       1 | Potion      |            2
       2 | High Potion |            1
       3 | Mega Potion |            2
       4 | Giga Potion |            0
       5 | Mana Potion |            1

2.4.2 定着確認

SQLの仕様上、FROM 句において LEFT JOIN と RIGHT JOIN を混在させることはできない。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切ではない。
左外部結合 (LEFT OUTER JOIN) のみで構成される場合、結合の記述順序を変更すると、結果セットの正味の内容が変化することがある。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切である。

2.5 内部結合と外部結合を混在させた場合の注意点

FROM 句のなかで 内部結合 と 外部結合 を混在させることも可能ですが、その挙動は初学者にとって直感に反しやすいため注意が必要となります。

たとえば、さきほどの select-outer_join_03.sql (＝キャラを基準とした所持アイテムの一覧を出力するSQL) の FROM 句を、次のように書き換えたとします。

FROM
  n_characters AS c
  LEFT JOIN n_character_items AS ci ON c.character_id = ci.character_id
  JOIN n_items AS i ON ci.item_id = i.item_id -- LEFT JOIN を JOIN に変更

ここで、第09行目 の JOIN n_items AS i ON ci.item_id = i.item_id だけに着目すると「n_character_items と n_items を item_id で内部結合している」という処理をするように読めて、一見すると問題がない (LEFT JOIN と同じ結果が得られる) ように感じられます。

しかし、実際に実行してみると、次のような結果になります。

 character_id |  name  |    name     | qty 
--------------+--------+-------------+-----
            1 | Marvin | Potion      |   3
            1 | Marvin | High Potion |   2
            1 | Marvin | Mega Potion |   1
            2 | Zach   | Mega Potion |   1
            4 | Tom    | Potion      |   2
            4 | Tom    | Mana Potion |   3

これは、結合処理内容が…

n_character_items と n_items を、item_id で内部結合している

…のではなく、実際には…

n_characters と n_character_items を外部結合した結果と n_items を item_id で内部結合している

…ためです。 n_characters と n_character_items を 外部結合 (LEFT JOIN) した結果は、以下のようになります。

 character_id |  name   | job_id | character_id | item_id | qty 
--------------+---------+--------+--------------+---------+-----
            1 | Marvin  |      5 |            1 |       1 |   3
            1 | Marvin  |      5 |            1 |       2 |   2
            1 | Marvin  |      5 |            1 |       3 |   1
            2 | Zach    |        |            2 |       3 |   1
            3 | Charlie |      6 |              |         |
            4 | Tom     |      1 |            4 |       1 |   2
            4 | Tom     |      1 |            4 |       5 |   3
            5 | Alice   |      5 |              |         |

ここから分かるように、Charlie と Alice の行の item_id カラムは NULL となります。これに対して n_items テーブルと item_id による内部結合が行われるため、item_id が NULL の行は結合条件を満たさず、最終的な結果セットから除外されます。

このように、内部結合と外部結合を混在させると「どの時点で内部結合が適用されるのか」によって、大きく結果が変わるため注意してください。

select-outer_join_03.sql と同様の結果セットを、外部結合と内部結合の組み合わせで取得したい場合は、次のように 括弧を用いて結合順序を明示的に指定 します。

FROM
  n_characters AS c
  LEFT JOIN (
    n_character_items AS ci
    JOIN n_items AS i ON ci.item_id = i.item_id
  ) ON c.character_id = ci.character_id

ただし、このような記述は構造が複雑で、可読性が悪くなりがちです。そのため、特別な理由がない限り LEFT JOIN のみで表現する方が可読性・保守性の両面で望ましいと言えます。

2.5.1 定着確認

FROM 句において、内部結合と外部結合を混在させることは SQL の仕様上は問題ない。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切である。

問題1 次の SQL を実行したときに得られるものは、以下の 結果セット A から C のうちどれか。

答え: 結果セットB

SELECT
  i.item_id, i.name AS "item", c.name AS "character", j.name AS "job", ci.qty
FROM
  n_items AS i
  JOIN n_character_items AS ci ON i.item_id = ci.item_id -- ◀ 内部結合
  LEFT JOIN n_characters AS c ON ci.character_id = c.character_id
  LEFT JOIN n_jobs AS j ON c.job_id = j.job_id
ORDER BY
  i.item_id, c.character_id;

▼ 結果セット A

item_id |    item     | character |   job   | qty 
---------+-------------+-----------+---------+-----
       1 | Potion      | Marvin    | Priest  |   3
       1 | Potion      | Tom       | Fighter |   2
       2 | High Potion | Marvin    | Priest  |   2
       3 | Mega Potion | Marvin    | Priest  |   1
       5 | Mana Potion | Tom       | Fighter |   3

▼ 結果セット B

 item_id |    item     | character |   job   | qty 
---------+-------------+-----------+---------+-----
       1 | Potion      | Marvin    | Priest  |   3
       1 | Potion      | Tom       | Fighter |   2
       2 | High Potion | Marvin    | Priest  |   2
       3 | Mega Potion | Marvin    | Priest  |   1
       3 | Mega Potion | Zach      |         |   1
       5 | Mana Potion | Tom       | Fighter |   3

▼ 結果セット C

 item_id |    item     | character |   job   | qty 
---------+-------------+-----------+---------+-----
       1 | Potion      | Marvin    | Priest  |   3
       1 | Potion      | Tom       | Fighter |   2
       2 | High Potion | Marvin    | Priest  |   2
       3 | Mega Potion | Marvin    | Priest  |   1
       3 | Mega Potion | Zach      |         |   1
       4 | Giga Potion |           |         |
       5 | Mana Potion | Tom       | Fighter |   3

問題2 次の SQL を実行したとき得られるものは、結果セット A から C のうちどれか (結果セットの選択肢は 問題1 と同じ)。

答え: 結果セットA

SELECT
  i.item_id, i.name AS "item", c.name AS "character", j.name AS "job", ci.qty
FROM
  n_items AS i
  LEFT JOIN n_character_items AS ci ON i.item_id = ci.item_id
  LEFT JOIN n_characters AS c ON ci.character_id = c.character_id
  JOIN n_jobs AS j ON c.job_id = j.job_id -- ◀ 内部結合
ORDER BY
  i.item_id, c.character_id;

問題3 次の SQL を実行したとき得られるものは、結果セット A から C のうちどれか (結果セットの選択肢は 問題1 と同じ)。

答え: 結果セットB

SELECT
  i.item_id, i.name AS "item", c.name AS "character", j.name AS "job", ci.qty
FROM
  n_items AS i
  LEFT JOIN n_character_items AS ci ON i.item_id = ci.item_id
  JOIN n_characters AS c ON ci.character_id = c.character_id -- ◀ 内部結合
  LEFT JOIN n_jobs AS j ON c.job_id = j.job_id
ORDER BY
  i.item_id, c.character_id;

3 完全外部結合 (FULL OUTER JOIN)

テーブルを結合する際、左右どちらにしか存在しない行も含めるような外部結合を「完全外部結合 (FULL OUTER JOIN)」と言います。完全外部結合は「全結合」とも呼ばれることもあります。また、標準SQL では FULL JOIN という省略形を用いることもできます。

テーブル同士に適切な FK制約 (外部キー制約) が設定されている場合、それらのテーブルの結合は、内部結合 (INNER JOIN) もしくは 左外部結合 (LEFT OUTER JOIN) で要件を満たせることがほとんどであり、完全外部結合 (FULL OUTER JOIN) が必要となるケースは限定的となります。

完全外部結合が使われるのは、直接つながっていないテーブル同士を比較したい場合や「差分」や「過不足」を確認したい場面となります。

ここでは、ギルド「Yamato」と「D.D.D」に所属するキャラクタの一覧を「比較」する例を考えます (いま、キャラクタは複数のギルドに所属可能である前提とします)。

注意

ここからは、前回講義で扱ったテーブル群 ( x_**** ) を使用します。from-teacher/10/create-x_db.sql と insert-x_db_01.sql を再実行しておいてください。

まず、Yamato に所属するキャラは、内部結合を用いて次のように取得できます。

SELECT
  c.character_id,
  c.name
FROM
  x_characters AS c
  JOIN x_guild_characters AS gc ON gc.character_id = c.character_id
  JOIN x_guilds AS g ON gc.guild_id = g.guild_id
WHERE
  g.name = 'Yamato';

実行結果は、次のようになります。

 character_id |  name  
--------------+--------
            1 | Marvin
           12 | Dave
           15 | Trent
            6 | Alice
            7 | Trudy

また、第09行目 を g.name = 'D.D.D' に変更すれば、D.D.D の所属キャラを次のように取得できます。

 character_id |  name  
--------------+--------
           11 | Ellen
            2 | Zach
            6 | Alice
           13 | Mallet
           14 | Eve
           17 | Wendy

以上を踏まえて、これら (各ギルドの所属キャラの) の比較に 完全外部結合 (FULL OUTER JOIN) を使用する例を示します。

SELECT
  COALESCE(c1.character_id, c2.character_id) AS "c_id",
  c1.name AS "Yamato",
  c2.name AS "D.D.D"
FROM
  (
    SELECT
      c.character_id,
      c.name
    FROM
      x_characters AS c
      JOIN x_guild_characters AS gc ON gc.character_id = c.character_id
      JOIN x_guilds AS g ON gc.guild_id = g.guild_id
    WHERE
      g.name = 'Yamato'
  ) AS c1
  FULL OUTER JOIN ( -- ◀ 完全外部結合
    SELECT
      c.character_id,
      c.name
    FROM
      x_characters AS c
      JOIN x_guild_characters AS gc ON gc.character_id = c.character_id
      JOIN x_guilds AS g ON gc.guild_id = g.guild_id
    WHERE
      g.name = 'D.D.D'
  ) AS c2 ON c1.character_id = c2.character_id
ORDER BY
  c1.character_id,
  c2.character_id;

第07行目 から 第15行目 に、先ほど示した「Yamato」の所属メンバの抽出 の SELECT 文がサブクエリとして埋め込まれています。第18行目 から 第26行目 も同様です。
第02行目 で COALESCE(c1.character_id, c2.character_id) を使用しているのは、左右どちらか一方にしか存在しない行でも、キャラクタID を欠損させずに表示するためです。

実行結果は次のようになります。

 c_id | Yamato | D.D.D  
------+--------+--------
    1 | Marvin |
    6 | Alice  | Alice
    7 | Trudy  |
   12 | Dave   |
   15 | Trent  |
    2 |        | Zach
   11 |        | Ellen
   13 |        | Mallet
   14 |        | Eve
   17 |        | Wendy

この結果セットでは「両ギルドに所属しているキャラ」と「どちらか一方にしか所属していないキャラ」が明確に分離して表示されます。このような用途は年度マスタの比較や設定変更前後の差分確認で頻出します。

以上のように完全外部結合は、日常的なデータ取得よりも、比較・検証といった用途で用いられることが多くなります。

(プロンプト例)

SQL に関する質問です。完全外部結合の「つかいどころ」がいまいちわかりません。どんな場面で使用されるか具体例とともに解説してください。

3.0.1 定着確認

標準SQLにおいて OUTER JOIN と記述した場合、それは「完全外部結合」として解釈される。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切ではない。
2つのテーブルに限った完全外部結合においては、結合順を入れ替えると結果セットの正味の内容 (集合としての行) が変化することがある。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切ではない。
3つ以上のテーブルの完全外部結合においては、結合順を入れ替えると結果セットの正味の内容 (集合としての行) が変化することがある。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切である。(A FULL JOIN B) FULL JOIN C ≠ A FULL JOIN (B FULL JOIN C)
標準SQLにおいて、完全外部結合を表す正式なキーワードは FULL OUTER JOIN である。なお、このキーワードは (　　　) という省略形で記述することができる。括弧にあてはまる語を答えよ。
- 答え: FULL JOIN

3.0.2 SQLドリル💻

create-x_db.sql と insert-x_db_01.sql で初期化されるテーブル群について、次の各問いに答えよ。

ex-04_1.sql 👉 次のような結果を得る SQL を完全外部結合を用いて記述せよ。

 i_id |    item_name     | Marvin | Jack | Alice |    description     
------+------------------+--------+------+-------+--------------------
    1 | Potion           |      2 |      |       | 単体HPを小回復
    3 | Mega Potion      |        |    1 |       | 単体HPを大回復
    5 | Mana Potion      |        |      |     2 | 単体MPを小回復
    6 | High Mana Potion |        |    1 |       | 単体MPを大回復
    7 | Antidote         |      1 |      |       | 毒状態を回復
    9 | Angel Feather    |        |    1 |     1 | 戦闘不能を回復
   10 | Chimera Wing     |      1 |    2 |     1 | 指定の街に瞬間移動

4 直積 (CROSS JOIN)

直積 (CROSS JOIN) は 結合条件を使用せずに 2つのテーブルの全組み合わせ を生成する結合となります。左側のテーブルの各行に対して、右側のテーブルのすべての行が組み合わされるため、結果セットの行数は「左テーブルの行数 \(\times\) 右テーブルの行数」となります。

直積は「クロス結合」とも呼ばれます。

SELECT
  c.character_id,
  c.name,
  j.job_id,
  j.name AS "job"
FROM
  n_characters AS c
  CROSS JOIN n_jobs AS j -- 直積
ORDER BY
  c.character_id,
  j.job_id;

実行結果は、次のようになります。

 character_id |  name   | job_id |   job   
--------------+---------+--------+---------
            1 | Marvin  |      1 | Fighter
            1 | Marvin  |      2 | Monk
            1 | Marvin  |      3 | Ninja
            1 | Marvin  |      4 | Samurai
            1 | Marvin  |      5 | Priest
            1 | Marvin  |      6 | Wizard
            2 | Zach    |      1 | Fighter
            2 | Zach    |      2 | Monk
            2 | Zach    |      3 | Ninja
            2 | Zach    |      4 | Samurai
            2 | Zach    |      5 | Priest
            2 | Zach    |      6 | Wizard
            3 | Charlie |      1 | Fighter
            3 | Charlie |      2 | Monk
 ～以下略～

直積はテストデータの生成や全パターンの列挙など用途が明確な場合にのみ使用される結合となります。

通常のデータ取得ではほとんど使われず、意図せず直積が発生すると、結果セットが爆発的に増加し、パフォーマンス低下の原因となる点に注意してください。

4.0.1 定着確認

SQLにおいて、「直積」は「全結合」ともよばれる。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切ではない。
クロス結合においては、結合順を入れ替えると結果セットの正味の内容 (集合としての行) が変化することがある。この説明は「適切である」か「適切ではない」かを答えよ。
- 答え: 適切ではない。
クロス結合は (　　　) とも呼ばれる。括弧にあてはまる語を漢字2文字で答えよ。
- 答え: 直積

4.0.2 定着確認

次に示すテーブル群 (table1、table2、table3) に対する SQL 操作に関する各問いに答えよ。

▼ table1

id	tbl2_id	tbl3_id
1	2	3
2	4
3	3	5
4		2
5
6	4	3

▼ table2

id	attr_a
1	hoge
2	fuga
3	piyo
4	hogera

▼ table3

id	attr_b
1	foo
2	bar
3	baz
4	qux
5	quux

問題1: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 4行

SELECT
  *
FROM
  table2 AS "t2"
  JOIN table1 AS "t1" ON t2.id = t1.tbl2_id;

問題2: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 6行

SELECT
  *
FROM
  table2 AS "t2"
  RIGHT JOIN table1 AS "t1" ON t2.id = t1.tbl2_id;

問題3: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 5行

SELECT
  *
FROM
  table2 AS "t2"
  LEFT JOIN table1 AS "t1" ON t2.id = t1.tbl2_id;

問題4: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 8行

SELECT
  *
FROM
  table3 AS "t3"
  FULL JOIN table1 AS "t1" ON t3.id = t1.tbl3_id;

問題5: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 20行

SELECT
  *
FROM
  table2 AS "t2"
  CROSS JOIN table3 AS "t3";

問題6: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 6行

SELECT
  *
FROM
  table1 AS "t1"
  LEFT JOIN table2 AS "t2" ON t1.tbl2_id = t2.id
  LEFT JOIN table3 AS "t3" ON t1.tbl3_id = t3.id;

問題7: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 4行

SELECT
  *
FROM
  table1 AS "t1"
  JOIN table2 AS "t2" ON t1.tbl2_id = t2.id
  LEFT JOIN table3 AS "t3" ON t1.tbl3_id = t3.id;

問題8: 次の SQL によって得られる結果セットのレコード数 (行数) を答えよ。答え: 4行

SELECT
  *
FROM
  table1 AS "t1"
  LEFT JOIN table2 AS "t2" ON t1.tbl2_id = t2.id
  JOIN table3 AS "t3" ON t1.tbl3_id = t3.id;

5 非正規化 (正規化崩し)

前回と今回の講義では、内部結合 (INNER JOIN)、外部結合 (LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN)、直積 (CROSS JOIN) について学びました。これらの結合を用いて実際に SQL を記述することで、その挙動を体感できたと思います。

SQLを記述するなかで気づいたと思いますが、結合は SQL の記述を非常に複雑にする要因 となります。また、結合は内部処理的にも負荷が大きく実行パフォーマンスにも影響を与えます。本講義では最大で 3 つのテーブルを結合する例を扱いましたが、実務では十数個のテーブルを結合するようなケースもあり、その場合、SQL の記述や保守の観点だけでなく、実行パフォーマンスの面でも無視できない影響が生じます。

これらの結合は、テーブルを「正規化」することで必然的に生じるものですが、こうしたデメリットもあるため、実務においては、性能や運用を優先して、あえて正規化を崩す という判断が行われることもあります。このような設計は「非正規化」や「正規化崩し」と呼ばれます。

非正規化や正規化崩しについては、📖 教科書「達人に学ぶDB設計徹底指南書 (第2版)」の 第5章論理設計とパフォーマンス～正規化の欠点と非正規化～ において詳しく解説されています。

6 授業時間外学習の指示

🚨本科目は「学修単位科目」であり、1回の講義あたり「4時間相当」の授業時間外学習が求められる科目です🏃

次回の講義で「小テスト❾」を実施します。
- 主に SQLドリル、定着確認、演習から出題します。
📖 教科書「達人に学ぶDB設計徹底指南書 (第2版)」の 第5章論理設計とパフォーマンス～正規化の欠点と非正規化～ について読んで勉強しておいてください。
- 参考: 教科書通りの正規化が現場で嫌われる理由@YouTube動画(9:16)

この講義資料を再読・熟読し「不明な用語」や「理解が不十分な用語」があればインターネットや、ChatGPTなどの生成AIを利用して解決してください。また、興味関心を持ったトピックについて、ウェブ、生成AI、YouTube動画などを利用して知識を広げ、理解を深めてください。
- 特に (プロンプト例) を示しているものについては、実際に生成AIにプロンプトを投げ、さらに対話を重ねることで、知識の幅を広げるだけでなく、理解をより深く確かなものにしてください。
講義資料内の「演習」や「SQLドリル」に再度取り組んでください。特に、SQLドリル💻 は、授業時間中に1回取り組むだけでは定着しないので注意してください。