SQL 入門 | 結合の仕組みを完全に理解する！

技術ブログの初回記事として、SQL の以下の結合 (JOIN) について、図やサンプルコードを入れながら解説をしていきます。

内部結合
外部結合
クロス結合

種類も多くてややこしい結合ですが、実用的な SQL では欠かせないテクニックになります。
結合の仕組みを理解することで、迷わず使い分けられるようになりましょう！

(※) 本記事のサンプルコードでは BigQuery を利用します。

結合の気持ち
結合の詳細
まとめ

結合の気持ち

仮想 Web サイトのアクセスログ (閲覧履歴) と、サイトの会員マスタのデータを利用します¹。

会員マスタ

アクセスログ

(※) 会員 ID が NULL のアクセスログは非会員によるアクセスを想定

アクセスログにある会員の情報は user_id のみで、アクセスを行った会員の名前は分かりません。
ですが user_id をキーに会員マスタのレコードを紐づけると、下表のようにアクセスログのレコードに会員の名前を紐づけられます。

この記事で扱うテーブル結合は上記のように、所定のルール (上の例だと会員 ID が同じレコード) でレコードを紐づた、新たなレコードたち (=表) を作成する操作です。

結合の詳細

この記事で扱う「レコード間の紐づけ」を行うタイプの結合は、大きく分けて 3 種類あります。
以降では SQL のサンプルコードも入れつつ、上記 3 種類の結合を詳しく見ていきます。

内部結合

アクセスログのレコードで user_id が NULL のレコードは、会員マスタ側に紐づけるべきレコードが存在しません。
このような紐づけるべき相手がいないレコードを、出力から除外する結合が内部結合です。

アクセスログに会員マスタを user_id をキーとして内部結合すると、以下の結果となります。

この結果を得る SQL は以下のようなコードになります。

SELECT
  access_log.access_at,  -- アクセスログのアクセス日時
  access_log.user_id,    -- アクセスログの会員ID
  access_log.url,        -- アクセスログのURL
  user.name              -- 会員マスタの会員の名前
FROM
  nccc_sql_practice.access_log
INNER JOIN
  nccc_sql_practice.user
  ON
    access_log.user_id = user.user_id

SELECT

access_log.access_at, -- アクセスログのアクセス日時

access_log.user_id, -- アクセスログの会員ID

access_log.url, -- アクセスログのURL

user.name -- 会員マスタの会員の名前

FROM

nccc_sql_practice.access_log

INNER JOIN

nccc_sql_practice.user

access_log.user_id = user.user_id

抑えるべきポイントは 2 点で、1 点目は内部結合の定義方法です。
内部結合は以下の形で「結合するテーブル」と「レコードを紐づける条件 (結合条件)」を定義します。
「レコードを紐づける条件」には TRUE / FALSE を返す条件式を入れます。

INNER JOIN
  [結合するテーブル]
  ON
    [レコードを紐づける条件]

INNER JOIN

[結合するテーブル]

[レコードを紐づける条件]

2 点目はカラムの参照方法です。
カラムを参照するには [テーブル名].[カラム名] の形で、どのテーブルのどのカラムを参照するのかを指定します。
なお、テーブルに渡ってカラム名に重複が無ければ [テーブル名] は省略可能です。

外部結合

内部結合では、紐づけるべき相手がいないレコードは結果から除外されましたが、
外部結合は紐づけるべき相手がいないレコードも残す結合です。
どのテーブルのレコードを残すかで、BigQuery では 3 種類の外部結合があります。

左外部結合　：左側の (SQL で先に現れる) テーブルのレコードを残す
右外部結合　：右側の (SQL で後に現れる) テーブルのレコードを残す
完全外部結合：左右両方のテーブルのレコードを残す

紐づける相手がいないレコードがあるテーブルのカラムの値は NULL になります。
以下に結合処理のイメージと、SQL コードをまとめます。

左外部結合

アクセスログに会員マスタを user_id をキーとして左外部結合すると、以下の結果となります。
結合結果の黄背景のセルは、紐づける相手がいないために値が NULL になります。

SQL は内部結合とほぼ同じで、INNER JOIN を LEFT OUTER JOIN に変更するのみです。

SELECT
  access_log.access_at,
  access_log.user_id,
  access_log.url,
  user.name
FROM
  nccc_sql_practice.access_log
LEFT OUTER JOIN
  nccc_sql_practice.user
  ON
    access_log.user_id = user.user_id

SELECT

access_log.access_at,

access_log.user_id,

access_log.url,

user.name

FROM

nccc_sql_practice.access_log

LEFT OUTER JOIN

nccc_sql_practice.user

access_log.user_id = user.user_id

右外部結合

左外部結合と同じく、アクセスログに会員マスタを user_id をキーとして右外部結合すると、以下の結果となります。

SQL も同じく、LEFT OUTER JOIN を RIGHT OUTER JOIN に変更するのみです。

SELECT
  access_log.access_at,
  access_log.user_id,
  access_log.url,
  user.name
FROM
  nccc_sql_practice.access_log
RIGHT OUTER JOIN
  nccc_sql_practice.user
  ON
    access_log.user_id = user.user_id

SELECT

access_log.access_at,

access_log.user_id,

access_log.url,

user.name

FROM

nccc_sql_practice.access_log

RIGHT OUTER JOIN

nccc_sql_practice.user

access_log.user_id = user.user_id

右外部結合は、テーブルの順序を入れ替えると左外部結合で同じ挙動を作れます。

左外部結合による右外部結合の再現 SQL

SELECT
  access_log.access_at,
  access_log.user_id,
  access_log.url,
  user.name
FROM
  nccc_sql_practice.user
LEFT OUTER JOIN
  nccc_sql_practice.access_log
  ON
    access_log.user_id = user.user_id

SELECT

access_log.access_at,

access_log.user_id,

access_log.url,

user.name

FROM

nccc_sql_practice.user

LEFT OUTER JOIN

nccc_sql_practice.access_log

access_log.user_id = user.user_id

完全外部結合

こちらも同じく、アクセスログに会員マスタを user_id をキーとして完全外部結合すると、以下の結果となります。

SQL も同じく、LEFT OUTER JOIN を FULL OUTER JOIN に変更するのみです。

SELECT
  access_log.access_at,
  access_log.user_id,
  access_log.url,
  user.name
FROM
  nccc_sql_practice.access_log
FULL OUTER JOIN
  nccc_sql_practice.user
  ON
    access_log.user_id = user.user_id

SELECT

access_log.access_at,

access_log.user_id,

access_log.url,

user.name

FROM

nccc_sql_practice.access_log

FULL OUTER JOIN

nccc_sql_practice.user

access_log.user_id = user.user_id

クロス結合

クロス結合はこれまでの結合と多少毛色が異なり、全てのレコードを無条件に紐づける結合です。
会員マスタに会員マスタ自身をクロス結合すると、以下の結果となります。

この結果を得る SQL は以下のようなコードになります。

SELECT
  user1.user_id AS user_id_1,
  user1.name    AS name_1,
  user2.user_id AS user_id_2,
  user2.name    AS name_2,
FROM
  nccc_sql_practice.user AS user1
CROSS JOIN
  nccc_sql_practice.user AS user2

SELECT

user1.user_id AS user_id_1,

user1.name AS name_1,

user2.user_id AS user_id_2,

user2.name AS name_2,

FROM

nccc_sql_practice.user AS user1

CROSS JOIN

nccc_sql_practice.user AS user2

クロス結合は無条件にレコードの紐づけを行う結合のため、以下のように「結合するテーブル」のみを定義します。

CROSS JOIN
  [結合するテーブル]

1 2	CROSS JOIN [結合するテーブル]

補足として、BigQuery を含めて、 ON 句により条件式を指定するとエラーになる場合が多いです。
そのため、クロス結合の結果から特定の組合せのみを除外したい場合は、以下のような対処が必要となります。
(1) クロス結合の後、WHERE 句で特定の組合せを除外する
(2) 内部結合等で置き換える

参考までに、先のクロス結合の SQL から、会員 ID が同じものを除外する SQL のサンプルをまとめておきます。

1. WHERE 句で除外する場合

SELECT
  user1.user_id AS user_id_1,
  user1.name    AS name_1,
  user2.user_id AS user_id_2,
  user2.name    AS name_2,
FROM
  nccc_sql_practice.user AS user1
CROSS JOIN
  nccc_sql_practice.user AS user2

SELECT

user1.user_id AS user_id_1,

user1.name AS name_1,

user2.user_id AS user_id_2,

user2.name AS name_2,

FROM

nccc_sql_practice.user AS user1

CROSS JOIN

nccc_sql_practice.user AS user2

2. 内部結合で置き換える

SELECT
  user1.user_id AS user_id_1,
  user1.name    AS name_1,
  user2.user_id AS user_id_2,
  user2.name    AS name_2,
FROM
  nccc_sql_practice.user AS user1
INNER JOIN
  nccc_sql_practice.user AS user2
  ON
    user1.user_id <> user2.user_id

SELECT

user1.user_id AS user_id_1,

user1.name AS name_1,

user2.user_id AS user_id_2,

user2.name AS name_2,

FROM

nccc_sql_practice.user AS user1

INNER JOIN

nccc_sql_practice.user AS user2

user1.user_id <> user2.user_id

まとめ

この記事では、レコードを紐づけるタイプのテーブル結合についての説明をしました。

レコードの紐づける際、条件をつける場合が内部結合 / 外部結合で、条件をつけない場合がクロス結合でした。
内部結合と外部結合は、レコードを紐づける相手をいないレコードを残すかどうかに違いがありました。さらに、このようなレコードを残す外部結合でも、どのテーブルのレコードを残すかで 3 種類の外部結合がありました。

この記事がややこしい SQL の結合の理解の助けになれば幸いです！

上記データを view として作成する BigQuery SQL はこちらにアップロードしています。 ↩︎