In a self-join, a table is joined to itself by referring to the same table using a different correlation name.
The following self-join produces a list of pairs of employees. Each employee name appears in combination with every employee name.
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 |
... | ... | ... | ... |
Since the Employees table has 75 rows, this join contains 75 x 75 = 5625 rows. It includes, as well, rows that list each employee with themselves. For example, it contains the row
GivenName | Surname | GivenName | Surname |
---|---|---|---|
Fran | Whitney | Fran | Whitney |
If you want to exclude rows that contain the same name twice, add the join condition that the employee IDs should not be equal to each other.
SELECT a.GivenName, a.Surname, b.GivenName, b.Surname FROM Employees AS a CROSS JOIN Employees AS b WHERE a.EmployeeID != b.EmployeeID; |
Without these duplicate rows, the join contains 75 x 74 = 5550 rows.
This new join contains rows that pair each employee with every other employee, but because each pair of names can appear in two possible orders, each pair appears twice. For example, the result of the above join contains the following two rows.
GivenName | Surname | GivenName | Surname |
---|---|---|---|
Matthew | Cobb | Fran | Whitney |
Fran | Whitney | Matthew | Cobb |
If the order of the names is not important, you can produce a list of the (75 x 74)/2 = 2775 unique pairs.
SELECT a.GivenName, a.Surname, b.GivenName, b.Surname FROM Employees AS a CROSS JOIN Employees AS b WHERE a.EmployeeID < b.EmployeeID; |
This statement eliminates duplicate lines by selecting only those rows in which the EmployeeID of employee a is less than that of employee b.
The following self-join uses the correlation names report and manager to distinguish two instances of the Employees table, and creates a list of employees and their managers.
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; |
This statement produces the result shown partially below. The employee names appear in the two left columns, and the names of their managers are on the right.
GivenName | Surname | GivenName | Surname |
---|---|---|---|
Alex | Ahmed | Scott | Evans |
Joseph | Barker | Jose | Martinez |
Irene | Barletta | Scott | Evans |
Jeannette | Bertrand | Jose | Martinez |
... | ... | ... | ... |
The following self-join produces a list of all managers who have two levels of reports, and the number of second-level reports they have.
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; |
The result of the above query contains the following rows:
ManagerID | second_level_reports |
---|---|
1293 | 30 |
902 | 23 |
501 | 22 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |