SQLにおける自己結合とは
SQLにおける自己結合とは、複雑なデータ構造を管理および分析するための強力なテクニックです。自己結合では、テーブルを自分自身に結合し、データ間に自己参照的な関係を作成します。これにより、階層的なデータを扱う場合に効率的かつ包括的なデータ分析が可能になります。
自己結合では、テーブルに2つのエイリアスまたは名前が与えられ、同じテーブルを参照するために使用されます。最初のエイリアスは元のテーブルを表し、2番目のエイリアスは元のテーブルのコピーを表します。これら2つのエイリアスを共通の列で結合することで、同じテーブル内のデータを2つの別々のテーブルのように比較および対照することができます。
自己結合は、内部結合、左外部結合、右外部結合、完全外部結合など、異なる種類の結合操作を使用して実行できます。それぞれの操作には独自の特性があり、異なる目的に使用されます。
自己結合の例
以下に、自己結合の例をサンプルテーブル、コード、および出力とともに示します。
階層的データ管理
従業員のID、名前、およびマネージャーのIDが含まれる従業員テーブルがある場合を考えてみます。全ての従業員の名前とそれぞれのマネージャーの名前を表示するレポートを生成したい場合、自己結合を使用できます。
Employee ID | Employee Name | Manager ID |
---|---|---|
1 | John | 2 |
2 | Mary | 3 |
3 | Peter | NULL |
4 | Sarah | 3 |
5 | Tom | 2 |
SELECT e.Employee_Name, m.Employee_Name as Manager_Name
FROM Employee e
INNER JOIN Employee m ON e.Manager_ID = m.Employee_ID
出力は次のようになります。
Employee Name | Manager Name |
---|---|
John | Mary |
Sarah | Peter |
Tom | Mary |
Mary | Peter |
この出力では、各従業員の名前とそれぞれのマネージャーの名前が表示されています。
データ間の関係性の検出
顧客テーブルに顧客ID、名前、紹介者IDの列がある場合を考えてみます。全ての顧客の名前と、それらを紹介した顧客の名前を表示するレポートを生成したい場合、自己結合を使用することができます。
Customer ID | Customer Name | Referral ID |
---|---|---|
1 | John | NULL |
2 | Mary | 1 |
3 | Peter | 2 |
4 | Sarah | 3 |
5 | Tom | 1 |
SELECT c.Customer_Name, r.Customer_Name as Referral_Name
FROM Customer c
LEFT JOIN Customer r ON c.Referral_ID = r.Customer_ID
出力は次のようになります。
Customer Name | Referral Name |
---|---|
John | NULL |
Mary | John |
Peter | Mary |
Sarah | Peter |
Tom | John |
この出力は、紹介者がいる場合は、各顧客の名前と紹介者の名前を表示しています。
複雑なデータ構造の解析
売上テーブルに売上ID、日付、商品ID、顧客IDの列がある場合を考えてみます。各顧客と商品ごとの売上総数を表示するレポートを生成したい場合、自己結合を使用することができます。
Sales ID | Date | Product ID | Customer ID |
---|---|---|---|
1 | 2022-01-01 | 1 | 1 |
2 | 2022-01-01 | 1 | 2 |
3 | 2022-01-01 | 2 | 1 |
4 | 2022-01-01 | 2 | 2 |
5 | 2022-01-01 | 1 | 3 |
SELECT c.Customer_ID, p.Product_ID, COUNT(*) as Total_Sales
FROM Sales s
INNER JOIN Customer c ON s.Customer_ID = c.Customer_ID
INNER JOIN Sales s2 ON s.Product_ID = s2.Product_ID AND s.Customer_ID = s2.Customer_ID
INNER JOIN Product p ON s.Product_ID = p.Product_ID
GROUP BY c.Customer_ID, p.Product_ID
出力は次のようになります。
Customer ID | Product ID | Total Sales |
---|---|---|
1 | 1 | 1 |
1 | 2 | 1 |
2 | 1 | 1 |
2 | 2 | 1 |
3 | 1 | 1 |
この出力は、各顧客と各製品ごとの総販売数を示しています。自己結合を使用することで、同じテーブル内のデータを比較・対比することができ、複雑なデータ構造を分析するのがより簡単になります。