SQL Serverの自己結合 (SELF JOIN)：同じテーブル内での結合の使いどころ

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)
EmployeeIDEmployeeNameManagerID
1AliceNULL
2Bob1
3Carol1
4David2

このテーブルを基に、従業員とその上司を取得するクエリを記述します。

SELECT 
    E1.EmployeeName AS Employee,
    E2.EmployeeName AS Manager
FROM 
    Employees E1
LEFT JOIN 
    Employees E2
ON 
    E1.ManagerID = E2.EmployeeID;
結果
EmployeeManager
AliceNULL
BobAlice
CarolAlice
DavidBob

同じテーブル内で日付の比較を行う例

売上データ (Sales) テーブルがあり、同じ商品の最新とその前の売上データを比較したい場合、自己結合を利用できます。

テーブル構造 (Sales)
SaleIDProductIDSaleDateSaleAmount
11012023-01-01500
21012023-01-05600
31022023-02-01700
41022023-02-10800

以下は、同じ商品の最新売上とその直前の売上を比較するクエリです。

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;
結果
ProductIDCurrentSaleDateCurrentSaleAmountPreviousSaleDatePreviousSaleAmount
1012023-01-056002023-01-01500
1022023-02-108002023-02-01700

自己結合の注意点

自己結合を使用する際に注意すべきポイントを挙げます：

  1. パフォーマンス：大規模なデータセットでは自己結合がパフォーマンスに影響を与えることがあります。必要に応じてインデックスを追加しましょう。
  2. エイリアスの利用：エイリアスを正しく設定しないと、クエリが読みにくくなり、誤解を招く可能性があります。
  3. NULL値の考慮：結合条件にNULL値が含まれる場合、意図しない結果になる可能性があるため注意が必要です。

演習問題

問題1: 親子関係の検索

以下のCategoriesテーブルを用いて、自己結合を使用して各カテゴリとその親カテゴリのペアを取得するSQLを作成してください。

テーブル構造 (Categories)
CategoryIDCategoryNameParentCategoryID
1ElectronicsNULL
2Computers1
3Laptops2
4Smartphones1

問題2: データ比較

以下のOrdersテーブルを基に、同じ顧客の注文間で、最新の注文とその前の注文を比較するSQLを作成してください。

テーブル構造 (Orders)
OrderIDCustomerIDOrderDateOrderAmount
112023-01-01100
212023-01-05200
322023-02-01300
422023-02-15400

解答例

解答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の自己結合は、同じテーブル内のデータを結合することで、データの関係性や階層構造を明確にするのに役立ちます。

本記事で紹介した基本構文や実例、演習問題を通じて、自己結合の使い方をマスターしてください！