【SQL入門】最適なテーブル結合について

11994 ワード
テーブル結合 サブクエリ SQL SQL テキストリンク

この記事では、《テーブル結合》について、
業務を通して学習した内容を、備忘録としてまとめています。

  • テーブル結合のロジック
  • 最適な結合方法

こういった内容についてまとめています。

※本記事は、自分で学習したことのまとめ用として書いています。
尚、解説で誤った点があれば、スローして頂ければ喜んでキャッチしますのでお願い致します。

テーブル結合のロジック

まずは・・・

"どう言う流れでテーブルが結合がされるか"

を理解する必要があります。

そして、流れは下記の通りです。

  1.  ON句の条件で、テーブルを結合する
  2.  WHERE句の条件で、抽出する

--- 具体例（内部結合） ---

『内部結合』は・・・

それぞれのテーブルで、ON句で指定したカラムの値が一致するものだけを結合します。

『内部結合』_の命令には、INNER JOINを使います。

person テーブル

id name job_id
1 A 1
2 B 2
3 C 3

job テーブル

id job_name
1 engineer
2 designer


では・・・

上記２つのテーブルを例に、『テーブル結合のロジック』について見ていきましょう。

パターン①： ON句のみ

SELECT
    id,
    person.name,
    job.job_name
FROM person
    INNER JOIN job
        ON person.job_id = job.id

-- job.idはjobのPK
結果
id name job
1 A engineer
2 B designer

ON句で指定したperson.job_id = job.idという条件で、テーブル結合しています。

また・・・

job テーブルに id = 3のレコードがないため、Cのレコードは削除されています。

パターン②： ON句とWHERE句 

SELECT
    id,
    person.name,
    job.job_name
FROM person
    INNER JOIN job
        ON person.job_id = job.id
WHERE 
    job.id = '1';

-- job.idはjobのPK
結果
id name job
1 A engineer

パターン①の条件に加え、WHERE句でjob.id = '1'を指定しているので…

Bのレコードが削除されています。

LEFT OUTER JOINで気をつけること

--- 処理の概要 ---

LEFT OUTER JOINでは・・・

（LEFT OUTER JOINの）左側のテーブルの行を全て抽出します。

そのため・・・

右側のテーブルに存在しない行については、NULLが埋められています。

ちなみに・・・

先ほどのコードを、LEFT OUTER JOINで書き換えると…

SELECT
    id,
    person.name AS name,
    job.job_name AS job
FROM person -- ←左側
    LEFT OUTER JOIN job　-- ←右側
        ON person.job_id = job.id

-- job.idはjobのPK

となります。

その結果・・・

例えば、下記のようなデータが取れる可能性もあります。

id name job
1 A engineer
2 B NULL
3 C NULL

繰り返しになりますが、結合処理のロジックは…

  • ON句の条件で、テーブルを結合する

であるので、この後に、左側のテーブルの行を全て呼び出します。

その結果・・・

" 取得する必要ない行にNULLが埋められて、呼び出される "

という訳です。

--- NULL対策 ---

WHERE句の条件で、抽出する

SELECT
    id,
    person.name AS name,
    job.job_name AS job
FROM person
    LEFT OUTER JOIN job
        ON person.job_id = job.id
WHERE 
    job.id = '1';

-- job.idはjobのPK

上記のように・・・

WHERE句の条件で、抽出することでNULLを回避することができます。

id name job
1 A engineer

最適な結合方法

しかし、先ほどのコードだと・・・

仮に…

" 100万件を超える大量のデータを持つテーブル同士を結合させる "

といった場合、処理が遅くなってしまいます。

100万件のデータを結合した後に、WHERE句の条件で抽出するためです。

なので、大量のデータの場合は…

" 結合する前に、必要なデータのみを抽出しておく "

のがオススメです。

WITH variation AS(
    SELECT *
    FROM
        person
    WHERE
        person.job_id = '1'
)

SELECT
    id,
    person.name AS name,
    job.job_name AS job
FROM variation
    LEFT OUTER JOIN job
        ON person.job_id = job.id

上記のSQLでは・・・

  1.  サブクエリの処理
    ➡︎ この時点で、person.job_id = '1'を抽出する
  2.  ON句で結合

といった処理の流れとなります。

" サブクエリを使って、あらかじめ必要なデータのみを抽出して、結合する件数を減らす。 "
ということをしています。

これにより・・・

無駄なテーブル結合を削減できるうえ、左側（person）のテーブルはサブクエリによって抽出済みのため、

仮に、LEFT OUTER JOINによって全行呼び出されたとしても…

最終的に取得できる結果は、`person.job_id = '1'のレコードのみになります。

id name job
1 A engineer