にほんブログ村
こんにちは、わくほこ(@wakuhoko)です。
最近SQLの勉強が楽しくて、色々さわっています。
SQLのあらゆる面でつまずきまくったわたしが、今回はテーブルの自己結合についてご紹介します！
- テーブルの自己結合が何かいまいちわからない
- 使いどころがわからない
テーブルの自己結合とは？
テーブルの自己結合（self-join）は、同じテーブル同士を結合する操作のことです。
ざっくり言うと、1つのテーブルを別のテーブルのように扱い、それぞれを結合させるイメージです。
テーブルの自己結合でも通常のテーブル結合と同様に、条件に基づいて行を結合します。
普通のテーブル結合と違うところは、自己結合では同じテーブル内の異なる行同士を結合することです。
…と、言葉で言われてもよくわかりませんよね。
それぞれ例にして見てみましょう！
2つのテーブルを結合する場合
一般的なテーブル結合では、別のテーブル同士を結合して1つのテーブルのように使います。
例えば以下のようなパターンです。
- EmployeesテーブルとDepartmentsテーブル
- Employeesテーブルには従業員のIDと名前、所属部署のID、役職データ
- Departmentsテーブルには部署IDと部署名のデータ
まずはテーブルとデータを作ります。
-- Employeesテーブルの定義
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentID INT,
Position VARCHAR(100)
);
-- Departmentsテーブルの定義
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
-- サンプルデータの挿入
INSERT INTO Employees (EmployeeID, Name, DepartmentID, Position) VALUES
(1, 'John', 1, 'Manager'),
(2, 'Alice', 1, 'Supervisor'),
(3, 'Bob', 2, 'Clerk');
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Sales'),
(2, 'Finance');
Employeesテーブルのデータは以下のようになります。
| EmployeeID | Name | DepartmentID | Position |
|---|---|---|---|
| 1 | John | 1 | Manager |
| 2 | Alice | 1 | Supervisor |
| 3 | Bob | 2 | Clerk |
Departmentsテーブルのデータは以下のようになります。
| DepartmentID | DepartmentName |
|---|---|
| 1 | Sales |
| 2 | Finance |
それではEmployeesテーブルとDepartmentsテーブルを結合して従業員の名前、所属する部署名、役職を取得してみましょう。
SELECT e.Name AS EmployeeName, d.DepartmentName, e.Position
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
上記のSQLを実行すると得られる結果は以下の通りです。
| EmployeeName | DepartmentName | Position |
|---|---|---|
| John | Sales | Manager |
| Alice | Sales | Supervisor |
| Bob | Finance | Clerk |
Employeesテーブルの各行に対して、DepartmentIDが一致する部署テーブルの行を結合します。
- EmployeesテーブルのDepartmentIDが1=DepartmentsテーブルのDepartmentIDが1の行を結合
- 結合したテーブルからSELECT句で選択した値を取得
その結果、従業員の名前、所属する部署の名前、従業員の役職が取得できます。
1つのテーブルを結合する場合（自己結合）
それでは次に自己結合を見てみましょう。
例として以下のようなテーブルを作ります。
- Employeesテーブル
- Employeesテーブルには従業員のIDと名前、役職、上司のID（SupervisorID）のデータ
- 最上位の上司のSupervisorIDはNULL（上司がいないため）
サンプルを作ります。
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(50),
Position VARCHAR(50),
SupervisorID INT
);
INSERT INTO Employees (EmployeeID, Name, Position, SupervisorID)
VALUES
(1, 'John', 'Manager', NULL),
(2, 'Alice', 'Supervisor', 1),
(3, 'Bob', 'Clerk', 2),
(4, 'Carol', 'Clerk', 2),
(5, 'David', 'Clerk', 2),
(6, 'Emily', 'Supervisor', 1),
(7, 'Frank', 'Clerk', 6);
このテーブルを使って、従業員とその上司の情報を取得してみます。
SELECT e.EmployeeID, e.Name AS EmployeeName, e.SupervisorID, s.Name AS SupervisorName
FROM Employees e
LEFT JOIN Employees s ON e.SupervisorID = s.EmployeeID;
結合するテーブルが同じであるため、それぞれのテーブルにエイリアス（別名）を設定して区別します。今回の例では、eとsがエイリアスに該当します。
結果は以下のようになります。
| EmployeeID | EmployeeName | SupervisorID | SupervisorName |
|---|---|---|---|
| 1 | John | NULL | NULL |
| 2 | Alice | 1 | John |
| 3 | Bob | 2 | Alice |
| 4 | Carol | 2 | Alice |
| 5 | David | 2 | Alice |
| 6 | Emily | 1 | John |
| 7 | Frank | 6 | Emily |
SQLの内容を順番に見ていきましょう。
- 従業員テーブルを2つのエイリアス（eとs）で参照し、従業員（e）とその上司（s）の情報を関連付ける
- SELECT句では、各従業員のEmployeeID（従業員ID）、名前（EmployeeName）、SupervisorID（上司のEmployeeID）、その上司の名前（SupervisorName）を選択
最終的な結果は、各従業員の情報とその上司の情報が組み合わさった形で返されます。
上司が存在しない従業員の場合は、SupervisorIDとSupervisorNameがNULLになります。
ここで使用されているLEFT JOINは、FROM句の左側にある従業員テーブル（e）のすべての行を返します。
それぞれの従業員の上司が存在する場合には、その上司の情報（従業員IDと名前）を関連付けて表示します。
例えば、以下のテーブルの赤いセルを見てください。
e.SupervisorID = s.EmployeeIDが一致していますよね。
| EmployeeID | EmployeeName | SupervisorID | SupervisorName |
|---|---|---|---|
| 1 | John | NULL | NULL |
| 2 | Alice | 1 | John |
| 3 | Bob | 2 | Alice |
| 4 | Carol | 2 | Alice |
| 5 | David | 2 | Alice |
| 6 | Emily | 1 | John |
| 7 | Frank | 6 | Emily |
一致したeの行のうちEmployeeID（従業員ID）、名前（EmployeeName）、SupervisorID（上司のEmployeeID）を、一致したsの行のEmployeeName、すなわちSupervisorName（上司の名前）を結果として返しているんです。
e=子（従業員自身） s=親（従業員の上司）
と関連づけられています。
LEFT JOINを使っているので、
- すべての従業員が結果に含まれる
- 各従業員の上司が存在する場合にはその情報も取得される
というわけです。
自分のテーブルの列を外部キーにしてテーブルの構造を組み直しているイメージですね。
物理的には1つのテーブルですが、それをバラして、別のテーブル同士を結合し直していると考えたほうがわかりやすいかもしれません。
テーブルの自己結合の使いどころは？
さて、ここまでテーブルの自己結合について見てきましたが、
結局、テーブルの自己結合の使いどころはいつ？
という疑問を持つ人もいるのではないでしょうか。
というか、わたしがそう思いました。
以下にいくつか使用例を挙げてみます。
- 組織チャートや階層構造の表現:
組織内の従業員や部門の階層構造を表現する際に自己結合が利用されます。
各従業員が上司や部門長といった管理者に関連付けられる階層的な構造をデータベースで表現する際に自己結合が活用されます。 - ソーシャルネットワークの関係表現:
ソーシャルネットワークサイトでの友人関係やフォロー関係など、ユーザー間の関係を表現する際に自己結合が利用されます。
各ユーザーが他のユーザーとの関係を持ち、ユーザー間の接続を表現する際に自己結合が有用です。 - 従属関係や依存関係の表現:
製品の構造や部品の依存関係、ファイルシステムのディレクトリ構造など、従属関係や依存関係を表現する際に自己結合が利用されます。
各要素が他の要素に依存し、階層的な関係を持つ場合に自己結合が有用です。 - ワークフローやプロセス管理:
ワークフロー管理システムやプロセス管理システムでのステップ間の関係を表現する際に自己結合が利用されます。
各ステップが前のステップと後続のステップとの関係を持ち、ワークフローやプロセスのフローを表現する際に自己結合が有用です。
正直、例を挙げられてもピンと来ませんよねw
- 同じテーブル内の別の行同士を関連付けたい
- 高度なデータ処理を行いたい
ときに使うと思っておけばOKです。
実は自己結合を使って
- 重複順列
- 順列
- 組み合わせ
- 重複行の削除
- 部分的に不一致なキーの検索
といったこともできるのですが、話がややこしくなるのでここでは紹介しません。
気になる人は以下の本を読んでみてください。
より深く自己結合が理解できますよ♫
終わりに
今回はテーブルの自己結合についてご紹介しました。
動きがわかりにくく、理解するのが難しいですよね。
でも自己結合を使いこなせれば、SQLの理解も深まり、一歩上へ行けるはずです！
わたしもまだまだ勉強中なので、一緒に頑張りましょう^^
ではでは〜
にほんブログ村