Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » Joins: Retrieving data from several tables » Specialized joins

 

Self-joins

In a self-join, a table is joined to itself by referring to the same table using a different correlation name.

Example 1

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.

Example 2

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-hand 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
... ... ... ...