🐡

【SQL】複数テーブルの結合 まとめ

に公開
SQL
初心者
RDB
join
fk
tech

はじめに

『スッキリわかるSQL入門 第2版』を読んで、問題に取り組みつつ、メモとしてまとめました。
なお、一部SQL文の例などを引用させていただいています。

リレーショナルの意味

データベース → データを安全・確実・高速に取り扱うために生まれた
※表計算ソフトにはないさまざまな機能を備えている

外部キーとリレーションシップ

リレーションシップ(relationship)

複数のテーブルに分かれているデータを、お互いに結びつける関係性のこと。

たとえば、「顧客」テーブルと「注文」テーブルがあったとします。
一人の顧客は複数の注文をする可能性があります。このとき、これらのテーブルは「一対多」のリレーションシップで結びついています。

リレーショナルデータベースでは、このようにデータを複数のテーブルに分割して管理することで、データの重複をなくし、効率的で整合性の取れたデータ管理を実現しています。

外部キー(foreign key)

外部キーは、このリレーションシップを物理的に実現するための「仕組み」です。

  • 役割: 他のテーブルの主キーを参照する列のこと
  • 目的: 参照先のテーブルに存在する値しか格納できないようにすることで、データの参照整合性を保つ

他のテーブルのある列（主キー列など）の値を格納することによって、「その行が他テーブルのどの行と関連しているか」を明らかにする役割がある。

複数テーブルに分けるメリット

人間にとっては読みづらくても、コンピュータにとっては、テーブルが分割されている方がデータを安全・確実・高速に取り扱いやすい

同じような情報をいろいろな場所で数多く保存していると、そのうちの1つだけを更新したり、参照したりしたい場合でも、分散している同じ種類のデータすべてについて、漏れなく検索して拾い上げる必要がある
→ その分の工数が増える

テーブルを分けることによってできること

  • 処理を減らすことができる
    条件に合致する行に対してのみ処理を実行すればいいことになる。
    仮にテーブルを分けないと、全行に対して1行ずつ条件に合致するかを調べることになるため、データが多いと、その分処理コストが増大する。

  • データの重複（冗長性）を取り除くことができる
    ・データの重複をなくすことで、データベースが占めるディスク容量を削減できる
    ・データが1箇所にまとまっているため、データの追加、更新、削除がより効率的に行えるようになる

◎1つのテーブルにさまざまなデータを詰め込むことは、データの管理を難しくしてしまう
◎複数のテーブルに分けてデータを管理する方が、管理に適している

デメリットの克服

課題
複数のテーブルに分けてデータを格納した方が管理には適しているものの、人間にとっては理解しづらい

解決策
管理に適した形態の複数テーブルから、人間が理解しやすい形態の1つの結果表を得るための結合(JOIN)という機能を備えている

リレーショナルデータベース(RDB)
データを複数テーブルで安全・確実に管理しながら、必要に応じて人間にわかりやすい表に結合することができる

テーブルの結合

結合の基本的な使い方

テーブルAとテーブルBの結合
SELECT 選択列リスト
  FROM テーブルA
  JOIN テーブルB
    ON 両テーブルの結合条件
例
SELECT 日付, 名前 AS 費目, メモ
  FROM 家計簿
  JOIN 費目                    -- 結合する他の表を指定
    ON 家計簿.費目ID = 費目.ID   -- 結合条件を指定

1行目に指定している「名前」という列は家計簿テーブルに存在しないのにエラーにならない理由

（通常、テーブルに存在しない列をSELECT分の選択列リストに記述するとエラーになる）

JOIN句によって家計簿テーブルに費目テーブルが結合され、「ID」「名前」という列も参照可能になるため
DBMSはまず2つのテーブルを結合した上で、列の絞り込み（選択列リストの指示による）や行の絞り込み（WHERE句の指定による）を行なっていく

結合によって両方のテーブルの列が参照可能になる

例の動作手順を見てみよう

1. FROM句とJOIN句の評価 (仮想テーブルの作成)
最初に、DBMSはFROM句とJOIN句に注目します。

  • FROM 家計簿
    最初に家計簿テーブルを選択

  • JOIN 費目
    次に、費目テーブルを結合
    JOINは、条件に合致する行を絞り込みながら、その行の列すべてを結合する処理
    (1) 結合条件の評価: ON句に指定された家計簿.費目ID = 費目.IDという条件が評価される
    (2) 行の絞り込み: この条件に一致する家計簿テーブルの行と費目テーブルの行がペアになる
    (3) 列の結合: ペアになった2つの行のすべての列を横に並べて、1つの新しい行を生成する

  • ON 家計簿.費目ID = 費目.ID
    このON句の条件に基づいて、両方のテーブルから一致する行を結合する。
    具体的には、家計簿テーブルの各行に対して、費目IDが費目テーブルのIDと一致する行を探し、それらを横に結合して1つの行にします。

この結果、両方のテーブルのすべての列を持つ一時的な仮想テーブルがメモリ上に作成されます。この仮想テーブルには、家計簿テーブルの日付やメモだけでなく、費目テーブルのIDや名前なども含まれています。

2. SELECT句の評価 (列の選択)
次に、DBMSはSELECT句に指定された列を、1. で作成した仮想テーブルから選択します。

  • 日付: 仮想テーブルから日付列を選ぶ
  • 名前 AS 費目: 仮想テーブルから名前列を選び、その列名を費目に変更する
  • メモ: 仮想テーブルからメモ列を選ぶ

この段階で、DBMSは不要な列を破棄し、指定された列のみで構成される新しい結果セットを作成します。

3. 結果の返却
最後に、DBMSは2. で作成された結果セットをユーザーに返却します。

結合の動作イメージ

DBMSがどのように結合処理をしていくか、頭の中にしっかりイメージを描きながら、定着させること

結合とは、左右に並んだ2つのテーブルを単純にくっつけるような処理ではない
結合に関係する2つのテーブルは対等な関係ではない

DBMSは1行ずつ「どの行を右に繋ぐべきか」を探しながら結合する
あくまでもFROM句で指定したテーブルが主役で、それにJOIN句で指定したテーブルを必要に応じて繋いでいく
DBMSはFROM句で指定したテーブルを1行ずつ処理していきながら「この行に繋ぐべき、JOIN句で指定したテーブルの行はどれか」と探しながら、行と行を繋いでいく

結合の際にDBMSが内部で繰り返し実行している処理
SELECT * 
FROM 右表 
WHERE 結合条件の式

このSQLによって得られた行を、現在注目している左表の行に繋ぐ

◎結合とは、テーブルを丸ごと繋ぐことではなく、結合条件が満たされた行を1つ1つ繋ぐこと

結合条件の取り扱い

結合相手が複数行の場合

右表の結合条件列に重複がある場合、DBMSは左表の行を複製して結合する
結果表の行数は、元の左表の行数より増える

つまり、左表の対象行が1行でも、右表に該当するデータが3つあれば、左表の行が2つ追加され、合計3行になるということ

結合相手の行がない場合

右表に結合相手の行がない場合や、左表の結合条件の列が NULL の場合、結合結果から消滅する
結合によって結果表の行数が減るということ
結合相手の行がないので、処理を諦める

左外部結合(left outer join)

左側のテーブルのすべてのレコードと、右側のテーブルの結合条件に一致するレコードを組み合わせます。
結合条件に一致するレコードが右側のテーブルにない場合でも、左側のテーブルのレコードは結果に含まれ、右側のテーブルの該当する列はNULLになります。
「左表については結合相手が見つからなくても、NULLがあっても必ず出力せよ」

基本構文
SELECT *
FROM テーブルA  -- 左表の名前
LEFT JOIN テーブルB  -- 右表の名前
  ON テーブルA.列 = テーブルB.列;  -- 結合条件

-- または

SELECT *
FROM テーブルA
LEFT OUTER JOIN テーブルB 
  ON テーブルA.列 = テーブルB.列;

結合相手の行がない場合や左表の結合条件列が NULL の場合、選択列リストに抽出される右表の列はすべて NULL になる。
右表の中に結合すべき行がない場合、すべての値がNULLである行を新たに生み出して結合してくれる。

右外部結合(right outer join) と 完全外部結合(full outer join)

基本構文
-- 右外部結合: 右表の全行を必ず出力する
SELECT *
  FROM テーブルA  -- 左表の名前
 RIGHT JOIN テーブルB  -- 右表の名前
    ON テーブルA.列 = テーブルB.列;  -- 結合条件

-- 完全外部結合: 左右の表の全行を必ず出力する
SELECT *
  FROM テーブルA
FULL JOIN テーブルB 
    ON テーブルA.列 = テーブルB.列;

※ RIGHT JOIN や FULL JOIN は、RIGHT OUTER JOIN や FULL OUTER JOIN と記述してもよい

右外部結合を使用すると、右表のすべての行が必ず結果表に出力される
左表で使われていない列が右表にあった場合でも、その行の情報が失われることはない

外部結合 ( OUTER JOIN )
結合条件に一致しないレコードも結果に含めます。
基準となるテーブルのすべてのレコードが結果に含まれ、もう一方のテーブルに一致するレコードがない場合は、該当する列がNULLになります。
つまり、本来結果表から消滅してしまう行も強制的に出力する効果がある。

内部結合 ( INNER JOIN )
結合条件に一致する両方のテーブルのレコードのみを結果として返します。どちらかのテーブルに一致するレコードが存在しない場合、そのレコードは結果セットから除外されます。これは最も一般的な結合方法です。
つまり、結合すべき相手の行が見つからない場合に行が消滅してしまう結合。

結合を理解するポイントは、「結合条件に合わないデータをどう扱うか」という点にあり

FULL JOIN を UNION で代用する

MySQLやMariaDBなど、FULL JOINを利用できないDBMSもある。
この場合、集合演算子 UNION を使って同等の処理を実現できる。

UNIONで代用
    SELECT 選択列リスト
      FROM 左表の名前
 LEFT JOIN 右表の名前
        ON 左表の結合条件列 = 右表の結合条件列
     UNION 
    SELECT 選択列リスト
      FROM 左表の名前
RIGHT JOIN 右表の名前
        ON 左表の結合条件列 = 右表の結合条件列

結合に関するさまざまな構文

テーブル名の指定

左右の表に共通する列名がある場合、DBMSが識別できるよう列名指定の前に「テーブル名.」という表記を加え、どのテーブルに属する列であるかを明示的に指定する

例
SELECT 日付, 家計簿.メモ, 費目.メモ
  FROM 家計簿
  JOIN 費目
    ON 家計簿.費目ID = 費目.ID

テーブル名が長い場合、ASで別名をつけておくと列指定や結合条件の記述が簡潔になる

別名を使用
SELECT 日付, K.メモ, H.メモ
  FROM 家計簿 AS K
  JOIN 費目 AS H
    ON K.費目ID = H.ID

3テーブル以上の結合

JOIN ~ ON ~ を繰り返すことで、3つ以上のテーブルを結合することもできる

※ 一度に3つのテーブルが結合されるわけではなく、前から順に1つずつ結合処理が行われていく

3つのテーブルを結合する
SELECT 日付, 費目.名前, 経費区分.名称
  FROM 家計簿   -- 家計簿テーブルに対して
  JOIN 費目     -- 費目を結合
    ON 家計簿.費目ID = 費目.ID
  JOIN 経費区分  -- その結果にさらに経費区分を結合
    ON 費目.経費区分ID = 経費区分.ID

副問い合わせの結果との結合

JOIN句のすぐ後ろに記述できるのは、テーブルだけでなく、「表形式のデータに化ける副問い合わせ」も記述することができる

副問い合わせの結果がテーブルの代わりとして利用されることを除けば、通常の結合と違いはない。
ただし、選択列リストや結合条件の指定のために、副問い合わせに別名を指定することが必要になる。

副問い合わせの結果と結合する
SELECT 日付, 費目.名前, 費目.経費区分ID
  FROM 家計簿
  JOIN ( SELECT *
           FROM 費目
          WHERE 経費区分 = 1
       ) AS 費目
    ON 家計簿.費目ID = 費目.ID

同じテーブル同士を結合

自己結合(self join)・再帰結合(recursive join)
同一テーブル同士を結合すること

自己結合を行なう場合、選択列リストや条件式を記述するために、同じテーブルに別の名前を付けることになる

自己完結
   SELECT A.日付, A.メモ, A.関連日付, B.メモ
     FROM 家計簿 AS A
LEFT JOIN 家計簿 AS B
       ON A.関連日付 = B.日付

非等価結合(non-equi join)

結合の条件には等価記号(=)を用いた結合条件を指定することがほとんど
※ = 以外の演算子を用いた条件式を記述することもでき、動作の仕組みは通常の結合と同じだが、DBMSにかかる負荷が多くなる点に注意

参考文献

『スッキリわかるSQL入門 第2版』

Discussion