2025-4I データベース工学 第10回 講義資料

2025年12月18日 (木) 3-4時限

1 連絡と準備

1.1 ハンズオン学習の準備

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

1.2 今回の講義の概要

第07回講義では、データの冗長性を排除し、効率的かつ一貫性のある構造を実現するために、情報を複数のテーブルに分解する 正規化 について学びました。また、第09回講義では、具体的なテーブル定義 (CREATE TABLE) と、それらのテーブル同士を整合性を保ちながら関連付けるため の外部キー制約 (REFERENCES) について学びました。

ここからの講義では、複数のテーブルに分散しているデータを対象に、それらを組み合わせて情報を引き出すクエリ (問い合わせ) について学んでいきます。具体的には、サブクエリ (副問い合わせ) と、内部結合・外部結合 によるデータ取得について学んでいきます。

2 準備

演習環境の from-teacher/10/create-x_db.sql を実行して、次のテーブル群を作成してください。

実行後、create-x_db.sql を読解して「各テーブルがどのようなカラムを持っているか」、「外部キー制約によってどのように紐付いているか」を確認してください。

(プロンプト例)

RDBに関する質問です。SQL を読んでいたら「XXXのマスタテーブル」や「XXXに関するトランザクションテーブル」というコメントが書かれていました。「マスタテーブル」と「トランザクションテーブル」とは何ですか？性質や役割が違うのですか？

「マスタテーブル」と「トランザクションテーブル」という文脈において、それらは SQL の START TRANSACTION / COMMIT とは関係ありますか？

つづいて from-teacher/10/insert-x_db_01.sql を実行して、各テーブルにレコードを挿入してください (※意図的にレコードを挿入していないテーブルもあります)。また insert-x_db_01.sql を読解してください。

2.0.1 演習

2.0.2 定着確認

create-x_db.sql で定義されるテーブル群について、次の各問いに答えよ。

2.0.3 SQLドリル💻

 item_id |       name       | price | weight_kg |     description      
---------+------------------+-------+-----------+----------------------
       1 | Potion           |   200 |       0.2 | 単体HPを小回復
       2 | High Potion      |   600 |       0.2 | 単体HPを中回復
       3 | Mega Potion      |  1200 |       0.3 | 単体HPを大回復
       4 | Giga Potion      |  4000 |       0.3 | 単体HPを完全回復
       5 | Mana Potion      |  1000 |       0.1 | 単体MPを小回復
       6 | High Mana Potion |  5000 |       0.2 | 単体MPを大回復
       7 | Antidote         |   300 |       0.1 | 毒状態を回復
       8 | Paralyze Cure    |   600 |       0.1 | 麻痺状態を回復
       9 | Angel Feather    |  1200 |       0.1 | 戦闘不能を回復
      10 | Chimera Wing     |   500 |       0.1 | 指定の街に瞬間移動
      11 | Torch            |   820 |       1.5 | 洞窟内を明るく照らす
      12 | Climbing Rope    |  1000 |       1.5 | 崖や段差を乗り越える
 job_id |  name   
--------+---------
      1 | Fighter
      2 | Monk
      3 | Ninja
      4 | Samurai
      5 | Priest
      6 | Wizard
 item_id | character_id | qty 
---------+--------------+-----
       1 |            1 |   2
       1 |            4 |   1
       1 |            7 |  10
       1 |            9 |   1
       1 |           15 |   2
       2 |            5 |   2
       2 |           14 |   1
       3 |            2 |   1
       3 |           12 |   1
 ～～以下略～～
 item_id | 所持キャラ数 | 所持総数 
---------+--------------+----------
       1 |            5 |       16
       2 |            2 |        3
       3 |            3 |        3
       5 |            3 |        9
       6 |            3 |        4
       7 |            5 |        8
       9 |            5 |        6
      10 |            6 |        7
      11 |            5 |        9
      12 |            1 |        1
(10 行)
 item_id | 所持総数 
---------+----------
       1 |       16
       5 |        9
       7 |        8
       9 |        6
      10 |        7
      11 |        9
(6 行)
 job_id 
--------
      5
(1 行)

3 サブクエリ (副問い合わせ)

SQLにおける サブクエリ (副問い合わせ) とは、ある SQL 文の内部に「入れ子」として記述される SELECT 文のことを意味します。これにより、1つのクエリのなかで 複数テーブルにまたがる検索条件や、集計結果 を扱うことが可能になります。

サブクエリは、主に WHERE 句や SELECT 句 などの一部として利用されます。

3.1 WHERE句におけるサブクエリの利用

たとえば、正規化によって「キャラクタデータ」と「ジョブデータ」が別々のテーブルに分解されている状況を考えます。このとき「ジョブが Wizard または Priest であるキャラの名前を取得したい」という要求があるとします。

この要求を原始的に実現しようとすると (＝「サブクエリ」や「結合」を使用せずに実現しようとすると) 、まずは x_jobs に対して該当する job_id を取得するために、次のような SQL を発行する必要があります。

SELECT
  job_id
FROM
  x_jobs
WHERE
  name IN ('Wizard', 'Priest');

この SQL の実行結果から、以下のように Wizard と Priest に対応する job_id (ここでは 5 と 6) を得ます。

 job_id 
--------
      5
      6
(2 行)

つづいて、この結果をもとに、x_characters テーブルに対して次のような SQL を発行し、条件に一致するキャラクタ情報を取得する という 2段階の手順 を踏むことになります。

SELECT
  character_id,
  name,
  job_id
FROM
  x_characters
WHERE
  job_id IN (5, 6) -- 先の実行結果から手作業で値を転記
ORDER BY
  character_id;

▼ 実行結果

 character_id |  name   | job_id
--------------+---------+--------
            1 | Marvin  |      5
            3 | Charlie |      6
            6 | Alice   |      5
           11 | Ellen   |      6
           13 | Mallet  |      6
           15 | Trent   |      5
           18 | Carol   |      5
           19 | Jack    |      6
(8 行)

このような手続きは煩雑になりやすく、特に 第08行目 のように、別の SQL の実行結果を手作業で転記する場合、ミスが起こりやすいという問題があります。ここでは 2個の整数値を転記していますが 1000個の UUID を転記するような状況は現実的ではありません。

サブクエリは、このような問題を解消するために有効に利用できます。サブクエリを用いることで、以下の SQL のように 1つのクエリのなかで、x_characters と x_jobs にまたがる検索条件を記述することが可能 となります。

SELECT
  character_id,
  name,
  job_id
FROM
  x_characters
WHERE
  job_id IN (
    SELECT
      job_id
    FROM
      x_jobs
    WHERE
      name IN ('Wizard', 'Priest')
  )
ORDER BY
  character_id;

上記の SQL の 第09行目 から 第14行目 のように、親クエリの内部に「入れ子」で記述された SELECT 文を サブクエリ といいます。サブクエリは必ず 括弧で囲む必要がある ので注意してください。

実際に SQL を実行し、先の場合と同じく以下の結果が得られることを確認してください。

 character_id |  name   | job_id
--------------+---------+--------
            1 | Marvin  |      5
            3 | Charlie |      6
            6 | Alice   |      5
           11 | Ellen   |      6
           13 | Mallet  |      6
           15 | Trent   |      5
           18 | Carol   |      5
           19 | Jack    |      6
(8 行)

3.2 SELECT句におけるサブクエリの利用

サブクエリは WHERE 句 だけではなく、SELECT句 でも利用することができます。SELECT 句で利用すれば、先の結果セットに Wizard や Priest といった「ジョブ名」をあわせて出力すること も可能になります。

ただし、通常、このような処理は、後のセクションで学ぶ 結合 (JOIN) を使用したほうが、記述がシンプルとなり、さらに実行効率の面でも有利になる場合が多いので注意 してください。

SELECT
  character_id,
  name,
  job_id,
  (
    SELECT
      name
    FROM
      x_jobs
    WHERE
      job_id = x_characters.job_id
  ) AS job_name
FROM
  x_characters
WHERE
  job_id IN (
    SELECT
      job_id
    FROM
      x_jobs
    WHERE
      name IN ('Wizard', 'Priest')
  )
ORDER BY
  job_id,
  character_id;

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

 character_id |  name   | job_id | job_name 
--------------+---------+--------+----------
            1 | Marvin  |      5 | Priest
            6 | Alice   |      5 | Priest
           15 | Trent   |      5 | Priest
           18 | Carol   |      5 | Priest
            3 | Charlie |      6 | Wizard
           11 | Ellen   |      6 | Wizard
           13 | Mallet  |      6 | Wizard
           19 | Jack    |      6 | Wizard
(8 行)

なお、この SQL の 第11行目 ように 外側 (親側) のクエリのカラム (x_characters.job_id) を参照するサブクエリ は 相関サブクエリ (Correlated Subquery) と呼ばれます。

(プロンプト例)

SQLに関する質問です。「サブクエリ」と「相関サブクエリ」の違いがいまいち分かりません。SELECT句に用いるものを「サブクエリ」、WHERE句に用いるものを「相関サブクエリ」というのですか🤔

3.2.1 定着確認

3.3 サブクエリを利用する場合の注意点

式として使うサブクエリは、外側のクエリから見ると「スカラー値」または「スカラー値の集合」として扱われます。そのため、原則として 「1カラムの1レコード」または「1カラムの複数レコード」 の結果セットを返すような SELECT 文を記述します。

サブクエリとして 複数のカラム を含む結果セットを返す SELECT を記述すると次のようなエラーが発生します。

psql:<stdin>:XX: ERROR:  副問い合わせは1列のみを返さなければなりません

また、単一のスカラー値が期待されている箇所で、サブクエリとして 複数のレコード を返す SELECT を記述すると、次のようなエラーが発生します。

psql:<stdin>:XX: ERROR:  式として使用された副問い合わせが2行以上の行を返しました

たとえば、WHERE job_id IN ( SELECT ... ) のように IN キーワードによって「スカラー値の集合」が期待される場合は、複数のレコードを返すサブクエリを記述しても問題ありません。

一方で、WHERE job_id = ( SELECT ... ) のような「単一のスカラー値」が期待される場合は、必ず 1レコードのみ を返すサブクエリ を記述する必要があります。

(プロンプト例)

SQLに関する質問です。式として使用するサブクエリは、スカラー値 (1カラム1レコード) または スカラー値の集合 (1カラム複数レコード) を返す必要がある、と言われました。意味が分かりません。どういうことですか？丁寧に解説してください。

補足

ここでは WHERE 句 や SELECT 句の「式として使うサブクエリ」に絞って説明しています。

FROM 句 において FROM ( SELECT ... ) のように「表」として扱うサブクエリは「複数カラムの複数レコード」を返すように記述することができます。

(プロンプト例)

SQLに関する質問です。サブクエリは、WHERE 句 や SELECT 句 で使うことが多いが、FROM 句でも使用できると聞きました。どのような使い道があるのか教えてください。

3.3.1 SQLドリル💻

やや難易度の高い問題も含まれているため、数分考えても解決しない場合は、解答例（演習環境の from-teacher/10/drill）を参照して理解してください。そのうえで、必ず自分で SQL を書き直してみてください。

解答例を見て「分かった」と感じても、実際に SQL を書けない場合、それは理解できているのではなく 理解したという錯覚 に陥っているだけです。注意してください。

 item_id |     name      |   description    
---------+---------------+------------------
       4 | Giga Potion   | 単体HPを完全回復
       8 | Paralyze Cure | 麻痺状態を回復
 item_id |       name       |     description      
---------+------------------+----------------------
       2 | High Potion      | 単体HPを中回復
       3 | Mega Potion      | 単体HPを大回復
       4 | Giga Potion      | 単体HPを完全回復
       6 | High Mana Potion | 単体MPを大回復
       8 | Paralyze Cure    | 麻痺状態を回復
      12 | Climbing Rope    | 崖や段差を乗り越える
 character_id |  name   |     deleted_at      
--------------+---------+---------------------
            3 | Charlie | 
            4 | Tom     | 
            5 | Ivan    |
            9 | Walter  | 2023-08-31 07:10:00
           18 | Carol   |
 character_id |  name   | job_id | deleted_at 
--------------+---------+--------+------------
            1 | Marvin  |      5 |
            3 | Charlie |      6 |
            6 | Alice   |      5 |
           11 | Ellen   |      6 |
           13 | Mallet  |      6 |
           18 | Carol   |      5 |
           19 | Jack    |      6 |
 item_id |     name      |     description      
---------+---------------+----------------------
       2 | High Potion   | 単体HPを中回復
       4 | Giga Potion   | 単体HPを完全回復
       8 | Paralyze Cure | 麻痺状態を回復
      12 | Climbing Rope | 崖や段差を乗り越える
 item_id |     name      | weight_kg |     description      
---------+---------------+-----------+----------------------
      11 | Torch         |       1.5 | 洞窟内を明るく照らす
      12 | Climbing Rope |       1.5 | 崖や段差を乗り越える
 character_id |  name   | item_kinds 
--------------+---------+------------
            1 | Marvin  |          3
            2 | Zach    |          2
            3 | Charlie |          2
            4 | Tom     |          1
            5 | Ivan    |          2
            6 | Alice   |          3
            7 | Trudy   |          3
            8 | Bob     |          0
            9 | Walter  |          4
 ～～以下略～～

4 内部結合

先のセクションでは、データが複数のテーブルに分散して存在しているとき、それらを組み合わせて情報を引き出すための方法のひとつとして「サブクエリ」について学びました。

同様に、複数のテーブルを対象に情報を引き出す方法として「結合」があります。結合には、大きくわけて「内部結合 (INNER JOIN)」「外部結合 (OUTER JOIN)」「直積 (CROSS JOIN)」が存在します。

まずは、シンプルで使用頻度も高い「内部結合」について学んでいきます。

4.1 内部結合の概要

内部結合 (INNER JOIN) は、複数テーブルの間に定義されたリレーションに基づいて 両方のテーブルに「共通して存在する行だけ」を結合 する方法です。その結果、次のような特徴があります。

文章だけでは分かりづらいため、ここからは具体例をみていきます。以下では、次に示すような jobs と characters という2つのテーブルがあるものとします。

▼ jobs テーブル

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

▼ characters テーブル

character_id name level job_id
1 Marvin 35 5
2 Zach 62 3
3 Charlie 57
4 Tom 1 1

各テーブルの主キーは「下線」で示しています。また、characters.job_id に、次のような「外部キー制約」が設定されているとします。

job_id INTEGER REFERENCES jobs (job_id)

このとき characters.job_id = jobs.job_id という結合条件を用いて 内部結合 INNER JOIN を行うと、次のような結果セットを得ることができます。

▼ 内部結合の結果セット

character_id character_name level job_id job_name attack_gain
1 Marvin 35 5 Priest 0
2 Zach 62 3 Ninja 5
4 Tom 1 1 Fighter 3

内部結合では 対応づけに成功した行の組だけ (＝結合条件の評価結果が TRUE となるものだけ) が、結果セットに含まれます。そのため、次のようになっていることに注意してください。

このような特性があるため、内部結合は SQLドリル の ex-02_1.sql のように「どのキャラも所持していないアイテムを抽出」や「どのキャラも就いていないジョブを抽出」といったクエリには適しません。この点が、次回講義で扱う「外部結合」との大きな違いとなります。

なお、ここでは 主キー (＝jobs.job_id) と それを参照する外部キー (＝characters.job_id) を結合条件として用いましたが、必ずしもそのような関係にあるカラムでなければならないわけではありません。

4.1.1 定着確認

4.2 内部結合のSQL構文

内部結合は SQL の FROM 句において、INNER JOIN と ON というキーワードを用いて指定します。たとえば、x_characters と x_jobs を「job_id」で内部結合する場合、次のような SQL を記述します。

SELECT
  character_id,
  x_characters.name, -- ◀ 注目
  level,
  x_jobs.job_id, -- ◀ 注目
  x_jobs.name, -- ◀ 注目
  attack_gain
FROM
  x_characters
  INNER JOIN x_jobs ON x_characters.job_id = x_jobs.job_id; -- ◀ 注目

ここで、注意してほしいのが SELECT 句の記述です。name や job_id のように両方のテーブルに「同名のカラム」が存在している場合は、「どちらのテーブルのカラムであるか」を明示する必要があります。もし、第03行目 で name だけを指定すると、次のようなエラーが発生します。

psql:<stdin>:XX: ERROR:  列参照"name"は曖昧です

一方で、level のように、結合対象のテーブル間でカラム名が重複していない場合は、そのまま指定することができます。ただし、将来的にカラムが追加される可能性があることや、可読性・識別性の観点から テーブル名を明示する書き方が推奨 されます。その際、実務では次のセクションで説明するように、テーブルの別名 (エイリアス) が用いられます。

ここでは 2つのテーブルを結合していますが、実務では 数個から十数個のテーブルを同時に結合すること もあります。このような状況でテーブル名を省略してカラムを指定すると SQL の可読性や保守性が著しく低下します。

なお、SQL標準 (PostgreSQL を含む) では、INNER JOIN を JOIN のように省略表記することもできます。

4.2.1 定着確認

4.3 内部結合のSQL構文 (エイリアス使用)

さきほどの select-join_01.sql は、PostgreSQLでは、次のように記述することができます。本科目では保守性と可読性の関係から、原則として INNER JOIN は省略形である JOIN、テーブルには1から2文字のエイリアスを使用してください。

SELECT
  c.character_id,
  c.name,
  c.level,
  j.job_id,
  j.name,
  j.attack_gain
FROM
  x_characters AS c
  JOIN x_jobs AS j ON c.job_id = j.job_id;

ここでは AS c や AS j のように、テーブルに「1文字のエイリアス (別名)」を指定していますが エイリアスは必ずしも1文字である必要はありません。ただ、結合条件やカラム指定の際にエイリアスを何度も記述するため、実務でも1文字のエイリアスがよく用いられます。

4.3.1 SQLドリル💻

create-x_db.sql および insert-x_db_01.sql で初期化した x_items、x_jobs、x_characters、x_guilds、x_guild_characters、x_character_items、x_gold_transfers を対象として、次の要求を満たす SQL を記述せよ。

 guild_id |  name  | owner_name | owner_level 
----------+--------+------------+-------------
        1 | Yamato | Marvin     |          35
        2 | D.D.D  | Ellen      |          51
        3 | hameln | Bob        |          33
 character_id |  name   | item_id | qty 
--------------+---------+---------+-----
            1 | Marvin  |       1 |   2
            1 | Marvin  |       7 |   1
            1 | Marvin  |      10 |   1
            2 | Zach    |       3 |   1
            2 | Zach    |      11 |   2
 ～以下略～
 character_id |  name   |  job   | deleted_at 
--------------+---------+--------+------------
            1 | Marvin  | Priest |
            3 | Charlie | Wizard |
            6 | Alice   | Priest |
           11 | Ellen   | Wizard |
           13 | Mallet  | Wizard |
           18 | Carol   | Priest |
           19 | Jack    | Wizard |
 item_id |     name      | 所持総数 
---------+---------------+----------
       1 | Potion        |       16
       5 | Mana Potion   |        9
       7 | Antidote      |        8
       9 | Angel Feather |        6
      10 | Chimera Wing  |        7
      11 | Torch         |        9
 job_id |   job   | count 
--------+---------+-------
      1 | Fighter |     4
      2 | Monk    |     2
      3 | Ninja   |     2
      4 | Samurai |     2
      5 | Priest  |     3
      6 | Wizard  |     4
 job_id |   job   | count 
--------+---------+-------
      1 | Fighter |     4
      2 | Monk    |     2
      3 | Ninja   |     2
      4 | Samurai |     2
      5 | Priest  |     3
      6 | Wizard  |     4
        | (Total) |    17

4.4 3つ以上のテーブルの内部結合

内部結合 (INNER JOIN) では、3つ以上のテーブルを対象に連結することもできます。例として、次に示す論理ER図のような関係をもつ x_characters、x_character_items、x_items を結合することが考えます。

img

ここで x_character_items は、キャラクタとアイテムの関係を表す「中間テーブル」となっています。これらの3つのテーブルを結合し、キャラクタ名、アイテム名、所持数の一覧を得るには、以下のような SQL を記述します。

SELECT
  c.character_id,
  c.name AS "character",
  i.item_id,
  i.name AS "item",
  ci.qty
FROM
  x_characters AS c
  JOIN x_character_items AS ci ON c.character_id = ci.character_id
  JOIN x_items AS i ON i.item_id = ci.item_id
ORDER BY
  c.character_id,
  i.item_id;

実行結果は、次のようになります。実際に試してみてください。

 character_id | character | item_id |       item       | qty 
--------------+-----------+---------+------------------+-----
            1 | Marvin    |       1 | Potion           |   2
            1 | Marvin    |       7 | Antidote         |   1
            1 | Marvin    |      10 | Chimera Wing     |   1
            2 | Zach      |       3 | Mega Potion      |   1
            2 | Zach      |      11 | Torch            |   2
            3 | Charlie   |       6 | High Mana Potion |   1
            3 | Charlie   |       9 | Angel Feather    |   2
            4 | Tom       |       1 | Potion           |   1
 ～以下略～

先に述べたように、内部結合は「どの組み合わせで結合するか (結合条件)」が同じであれば、FROM 句の書き方をある程度入れ替えても、結果セットの正味の内容は同じものとなります。たとえば、 第08行目 から 第10行目 を以下のように書き換えても同じ結果セットとなります。

x_character_items AS ci
JOIN x_items AS i ON ci.item_id = i.item_id
JOIN x_characters AS c ON ci.character_id = c.character_id

ただし、 ORDER BY を指定していない場合は、行の並び順は変わる可能性があります。

4.4.1 SQLドリル💻

create-x_db.sql および insert-x_db_01.sql で初期化した x_items、x_jobs、x_characters、x_guilds、x_guild_characters、x_character_items、x_gold_transfers を対象として、次の要求を満たす SQL を記述せよ。

 guild_id | guild  | member | level | is_owner 
----------+--------+--------+-------+----------
        1 | Yamato | Marvin |    35 | *
        1 | Yamato | Dave   |    68 |
        1 | Yamato | Trudy  |    48 |
        1 | Yamato | Alice  |    42 |
        2 | D.D.D  | Ellen  |    51 | *
        2 | D.D.D  | Mallet |    64 |
        2 | D.D.D  | Zach   |    62 |
        2 | D.D.D  | Wendy  |    56 |
        2 | D.D.D  | Eve    |    46 |
        2 | D.D.D  | Alice  |    42 |
        3 | hameln | Bob    |    33 | *
        3 | hameln | Steve  |    70 |
        3 | hameln | Dave   |    68 |
        3 | hameln | Jack   |    61 |
        3 | hameln | Oscar  |    44 |
(15 行)
 guild_id |  name  | owner  | member_count | avg_level 
----------+--------+--------+--------------+-----------
        2 | D.D.D  | Ellen  |            6 |      53.5
        3 | hameln | Bob    |            5 |      55.2
        1 | Yamato | Marvin |            4 |      48.3
(3 行)
 guild_id | guild  |   job   | count 
----------+--------+---------+-------
        1 | Yamato | Fighter |     1
        1 | Yamato | Samurai |     1
        1 | Yamato | Priest  |     2
        2 | D.D.D  | Fighter |     1
        2 | D.D.D  | Ninja   |     2
        2 | D.D.D  | Priest  |     1
        2 | D.D.D  | Wizard  |     2
        3 | hameln | Fighter |     1
        3 | hameln | Monk    |     1
        3 | hameln | Samurai |     2
        3 | hameln | Wizard  |     1
(11 行)

なお、0人のジョブも表示したい場合、次回講義で扱う 外部結合 を使用します。

5 授業時間外学習の指示

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