SQL Serverを使ってデータベースを管理する際、複雑なデータ操作が必要になることがあります。その中でも「自己結合 (SELF JOIN)」は、同じテーブル内のデータを結合して特定の条件を満たす情報を取得するための強力な手法です。
自己結合は、一見すると複雑に見えるかもしれませんが、理解して使いこなすことでデータ処理の幅が大きく広がります。本記事では、自己結合の基礎から使いどころ、実際のSQLクエリ例までを丁寧に解説します。
自己結合とは？
自己結合 (SELF JOIN) とは、同じテーブルを2回参照し、その間に結合を行う操作です。通常の結合では2つの異なるテーブルを結合しますが、自己結合では1つのテーブルを仮想的に複製し、そのコピーを結合することで、特定の条件に応じたデータを取得できます。
自己結合を使う場面としては、以下のようなケースがあります：
- 親子関係を表現するデータの検索
- 同じ属性を持つレコード間の比較
- 階層構造のデータを扱う際の処理
自己結合を使う際、テーブルにエイリアス (別名) を付けることで、元のテーブルと区別して利用します。
自己結合の基本構文
自己結合の構文は、通常のINNER JOINやLEFT JOINとほとんど同じです。違いは、結合する2つのテーブルが同じテーブルである点です。
以下は、自己結合の基本的な構文例です：
SELECT
A.列名1,
B.列名2
FROM
テーブル名 A
INNER JOIN
テーブル名 B
ON
A.条件列 = B.条件列
WHERE
その他の条件;ここでのポイント！
- エイリアス (A, B)：1つのテーブルを2回参照するため、エイリアスを使って区別します。
- ON句：自己結合する際の条件を指定します。
- WHERE句：追加の条件を指定できます。
自己結合の具体例
以下に、自己結合を使った実際の例をいくつか挙げます。
上司と部下の関係を取得する例
従業員 (Employees) テーブルがあり、各従業員には上司が紐付いているとします。このようなデータを自己結合を使って、従業員とその上司のペアを取得します。
テーブル構造 (Employees)
| EmployeeID | EmployeeName | ManagerID |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | David | 2 |
このテーブルを基に、従業員とその上司を取得するクエリを記述します。
SELECT
E1.EmployeeName AS Employee,
E2.EmployeeName AS Manager
FROM
Employees E1
LEFT JOIN
Employees E2
ON
E1.ManagerID = E2.EmployeeID;結果
| Employee | Manager |
|---|---|
| Alice | NULL |
| Bob | Alice |
| Carol | Alice |
| David | Bob |
同じテーブル内で日付の比較を行う例
売上データ (Sales) テーブルがあり、同じ商品の最新とその前の売上データを比較したい場合、自己結合を利用できます。
テーブル構造 (Sales)
| SaleID | ProductID | SaleDate | SaleAmount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 500 |
| 2 | 101 | 2023-01-05 | 600 |
| 3 | 102 | 2023-02-01 | 700 |
| 4 | 102 | 2023-02-10 | 800 |
以下は、同じ商品の最新売上とその直前の売上を比較するクエリです。
SELECT
S1.ProductID,
S1.SaleDate AS CurrentSaleDate,
S1.SaleAmount AS CurrentSaleAmount,
S2.SaleDate AS PreviousSaleDate,
S2.SaleAmount AS PreviousSaleAmount
FROM
Sales S1
LEFT JOIN
Sales S2
ON
S1.ProductID = S2.ProductID
AND S1.SaleDate > S2.SaleDate
WHERE
S2.SaleDate IS NOT NULL
ORDER BY
S1.ProductID, S1.SaleDate;結果
| ProductID | CurrentSaleDate | CurrentSaleAmount | PreviousSaleDate | PreviousSaleAmount |
|---|---|---|---|---|
| 101 | 2023-01-05 | 600 | 2023-01-01 | 500 |
| 102 | 2023-02-10 | 800 | 2023-02-01 | 700 |
自己結合の注意点
自己結合を使用する際に注意すべきポイントを挙げます：
- パフォーマンス：大規模なデータセットでは自己結合がパフォーマンスに影響を与えることがあります。必要に応じてインデックスを追加しましょう。
- エイリアスの利用：エイリアスを正しく設定しないと、クエリが読みにくくなり、誤解を招く可能性があります。
- NULL値の考慮：結合条件にNULL値が含まれる場合、意図しない結果になる可能性があるため注意が必要です。
演習問題
問題1: 親子関係の検索
以下のCategoriesテーブルを用いて、自己結合を使用して各カテゴリとその親カテゴリのペアを取得するSQLを作成してください。
テーブル構造 (Categories)
| CategoryID | CategoryName | ParentCategoryID |
|---|---|---|
| 1 | Electronics | NULL |
| 2 | Computers | 1 |
| 3 | Laptops | 2 |
| 4 | Smartphones | 1 |
問題2: データ比較
以下のOrdersテーブルを基に、同じ顧客の注文間で、最新の注文とその前の注文を比較するSQLを作成してください。
テーブル構造 (Orders)
| OrderID | CustomerID | OrderDate | OrderAmount |
|---|---|---|---|
| 1 | 1 | 2023-01-01 | 100 |
| 2 | 1 | 2023-01-05 | 200 |
| 3 | 2 | 2023-02-01 | 300 |
| 4 | 2 | 2023-02-15 | 400 |
解答例
解答1: 親子関係の検索
SELECT
C1.CategoryName AS Category,
C2.CategoryName AS ParentCategory
FROM
Categories C1
LEFT JOIN
Categories C2
ON
C1.ParentCategoryID = C2.CategoryID;解答2: データ比較
SELECT
O1.CustomerID,
O1.OrderDate AS CurrentOrderDate,
O1.OrderAmount AS CurrentOrderAmount,
O2.OrderDate AS PreviousOrderDate,
O2.OrderAmount AS PreviousOrderAmount
FROM
Orders O1
LEFT JOIN
Orders O2
ON
O1.CustomerID = O2.CustomerID
AND O1.OrderDate > O2.OrderDate
WHERE
O2.OrderDate IS NOT NULL
ORDER BY
O1.CustomerID, O1.OrderDate;まとめ
SQL Serverの自己結合は、同じテーブル内のデータを結合することで、データの関係性や階層構造を明確にするのに役立ちます。
本記事で紹介した基本構文や実例、演習問題を通じて、自己結合の使い方をマスターしてください！