SQL Academy · レッスン

自己結合とは

Q: 「自己結合とは」レッスンは無料ですか？

はい。「自己結合とは」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

Q: 「自己結合とは」で何を学びますか？

エイリアスを使ってテーブルを自身に結合します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Q: SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。

Q: 「自己結合とは」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

Q: このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

エイリアスを使ってテーブルを自身に結合します。

「自己結合とは」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

テーブルの自己結合

自己結合とは、テーブルを自分自身に結合することです。最初は特殊に聞こえますが、1つのテーブル内の行が、同じテーブル内の別の行と関連している場合に使う強力な手法です。

最も一般的な例は、各従業員の行に、その従業員のマネージャーのIDも保存されているemployeesテーブルです。そのマネージャーも、同じテーブル内の従業員です。

employeesテーブル

各行にid、name、同じテーブル内の別の行を指すmanager_idがあるemployeesテーブルを考えてみましょう。

この構造は、自己参照関係、または再帰的な関係と呼ばれます。以下のクエリを実行して、テーブルを作成し、データを登録してください。

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

通常の結合ではうまくいかない理由

各従業員とそのマネージャーの名前を並べて表示したい場合、どちらのデータも同じテーブルにあります。通常の結合では、2つの異なるテーブル（少なくとも異なる2つのデータソース）を接続します。

同じテーブルから、従業員用とマネージャー用に2回読み取るには、テーブルのエイリアスと自己結合を組み合わせる必要があります。

エイリアスの導入

自己結合を行うには、FROM / JOIN句に同じテーブル名を2回記述し、それぞれに異なるエイリアスを付けます。エイリアスによって、SQLはそれぞれを論理的に別のテーブルとして扱えます。

エイリアスには、employeeを表すeや、managerを表すmのような、短く説明的な名前を使うのが一般的です。

-- Conceptual structure (not yet a full query)
-- FROM employees AS e
-- JOIN employees AS m ON e.manager_id = m.id

初めての自己結合

以下は、すべての従業員をマネージャーの名前とともに一覧表示する、完全な自己結合の例です。ON条件で、従業員のmanager_idとマネージャーのidを結び付けています。

Aliceにはマネージャーがいないため、マネージャー側に一致する行がなく、INNER JOINによって結果から除外されます。

SELECT
  e.name   AS employee,
  m.name   AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id;

マネージャーがいない行も含める

INNER JOINを使うと、一致する行がない行は除外されます。Aliceのmanager_id = NULLであるため、結果から消えてしまいます。

LEFT JOINに切り替えると、すべての従業員を残し、マネージャーがいない場合はNULL（またはラベル）を表示できます。

SELECT
  e.name             AS employee,
  COALESCE(m.name, 'No Manager') AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

自己結合の結果を絞り込む

他のクエリと同じように、自己結合にもWHERE句を追加できます。以下の例では、直属のマネージャーがAlice（id = 1）である従業員だけを取得します。

SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id
WHERE m.name = 'Alice';

直属の部下を数える

自己結合は、集約関数と自然に組み合わせられます。以下のクエリでは、マネージャー側のエイリアスでグループ化し、各マネージャーの直属の部下の人数を数えています。

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id
GROUP BY m.name
ORDER BY direct_reports DESC;

階層構造ではないデータへの自己結合

自己結合は階層構造に限られません。テーブル内の行が同じテーブル内の別の行と関連している場合は、いつでも自己結合が役立ちます。

たとえば、同じマネージャーを持つ従業員の組み合わせをすべて見つける場合です。manager_idの値が一致する行同士を自己結合し、従業員自身同士が組み合わされないようにします。

SELECT
  a.name AS employee_1,
  b.name AS employee_2,
  a.manager_id
FROM employees AS a
INNER JOIN employees AS b
  ON a.manager_id = b.manager_id
  AND a.id < b.id;

エイリアスの役割

自己結合ではエイリアスが必須です。エイリアスがないと、データベースは各列がテーブルのどちらのコピーを参照しているのか判断できず、あいまいさエラーが発生します。

エイリアス名には任意の名前を使えます。重要なのは、すべての列参照に正しいエイリアスを付け、SQLが参照対象のテーブルのインスタンスを判断できるようにすることです。

-- This will fail: column 'name' is ambiguous
-- SELECT name FROM employees JOIN employees ON manager_id = id;

-- This works: aliases remove the ambiguity
SELECT e.name, m.name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

実際の利用例

自己結合は、実際のデータベースで頻繁に使われます。一般的な利用例には、次のようなものがあります。

組織図 — 従業員とそのマネージャー
カテゴリーツリー — 親カテゴリーと子カテゴリー
部品表 — 他の部品から構成される部品
ソーシャルネットワーク — 1つのつながりテーブルから友人の友人を見つける

スキーマ内に自己参照外部キーがあることに気付くのが、自己結合が必要かもしれないと判断する最初の手がかりです。

理解度チェック

次に進む前に、自己結合についての理解度を確認しましょう。

レッスンのまとめ

このレッスンでは、自己結合とは何か、いつ使うのかを学びました。

自己結合では、異なるエイリアスを付けて同じテーブルを2回参照し、テーブルを自分自身に結合します
一致しない行（最上位のマネージャーなど）を除外するにはINNER JOINを使い、残すにはLEFT JOINを使います
他の結合と同じように、WHERE、GROUP BY、集約関数を追加できます
同じテーブル内の行を比較するとき、ON句でa.id < b.idを使うと、重複する組み合わせを避けられます
自己結合は、階層構造、カテゴリーツリー、その他の自己参照関係に適した基本的な手法です

次は、再帰CTEを使って複数レベルの階層を扱う方法を見ていきます。

よくある質問