セルフジョイン

「セルフジョイン」では、異なる相関名を使用して同一テーブルを参照することによって、テーブルがそれ自体にジョインされます。

例 1

次のセルフジョインは従業員のペアのリストを作成します。各従業員の名前が全従業員の名前との組み合わせで表示されます。

SELECT a.GivenName, a.Surname,
      b.GivenName, b.Surname
FROM Employees AS a CROSS JOIN Employees AS b;

GivenName	Surname	GivenName	Surname
Fran	Whitney	Fran	Whitney
Fran	Whitney	Matthew	Cobb
Fran	Whitney	Philip	Chin
Fran	Whitney	Julie	Jordan
...	...	...	...

Employees テーブルには 75 個のローがあるので、このジョインには 75 × 75 = 5625 のローがあります。これには、従業員が自分自身をリストしたローも含まれます。たとえば、次のようなローです。

GivenName	Surname	GivenName	Surname
Fran	Whitney	Fran	Whitney

同じ名前を 2 回含むローを除外する場合は、互いの従業員 ID は同じではならないというジョイン条件を追加します。

SELECT a.GivenName, a.Surname,
      b.GivenName, b.Surname
FROM Employees AS a CROSS JOIN Employees AS b
WHERE a.EmployeeID != b.EmployeeID;

この重複するローを除くと、ジョインは 75 × 74 = 5550 ローで構成されます。

この新しいジョインは各従業員が自分以外の従業員とペアになったローで構成されます。しかし、各ペアの名前の表示には 2 通りの順番があるので、各ペアは 2 度表示されます。たとえば、前述のジョインには次の 2 つのローがあります。

GivenName	Surname	GivenName	Surname
Matthew	Cobb	Fran	Whitney
Fran	Whitney	Matthew	Cobb

名前の順番が重要でない場合は、同一ペアの表示が一度だけになる (75 × 74) / 2 = 2775 ローのリストを作成できます。

SELECT a.GivenName, a.Surname,
      b.GivenName, b.Surname
FROM Employees AS a CROSS JOIN Employees AS b
WHERE a.EmployeeID < b.EmployeeID;

この文は、従業員 a の EmployeeID が従業員 b の EmployeeID より小さいローのみを選択して、重複する行を削除します。

例 2

次のセルフジョインは相関名 report と manager を使用して、Employees テーブルの 2 つのインスタンスを区別し、従業員とその管理者のリストを作成します。

SELECT report.GivenName, report.Surname,
   manager.GivenName, manager.Surname
FROM Employees AS report JOIN Employees AS manager
   ON (report.ManagerID = manager.EmployeeID)
ORDER BY report.Surname, report.GivenName;

この文から、次に一部を示すテーブルが作成されます。従業員名は左側の 2 つのカラムに、マネージャー名は右側に表示されます。

GivenName	Surname	GivenName	Surname
Alex	Ahmed	Scott	Evans
Joseph	Barker	Jose	Martinez
Irene	Barletta	Scott	Evans
Jeannette	Bertrand	Jose	Martinez
...	...	...	...

例 3

次のセルフジョインによって、2 つのレベルのレポートを持つすべてのマネージャーと 2 番目のレベルのレポート数のリストが生成されます。

SELECT higher.managerID, count(*) second_level_reports
FROM employees lower JOIN employees higher
   ON ( lower.managerID = higher.employeeID )
GROUP BY higher.managerID
ORDER BY higher.managerID DESC;

上記のクエリの結果には、次のローが含まれています。

ManagerID	second_level_reports
1293	30
902	23
501	22

DocCommentXchange で意見交換できます