ASA SQL User's Guide
  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.emp_fname, a.emp_lname,
      b.emp_fname, b.emp_lname
FROM employee AS a CROSS JOIN employee AS b

Since the employee table has 75 rows, this join contains 75 x 75 = 5 625 rows. It includes, as well, rows that list each employee with themselves. For example, it contains the row

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.emp_fname, a.emp_lname,
      b.emp_fname, b.emp_lname
FROM employee AS a CROSS JOIN employee AS b
WHERE a.emp_id != b.emp_id

Without these duplicate rows, the join contains 75 x 74 = 5 550 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.

If the order of the names is not important, you can produce a list of the (75 x 74)/2 = 2 775 unique pairs.

SELECT a.emp_fname, a.emp_lname,
      b.emp_fname, b.emp_lname
FROM employee AS a CROSS JOIN employee AS b
WHERE a.emp_id < b.emp_id

This statement eliminates duplicate lines by selecting only those rows in which the emp_id 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 employee table, and creates a list of employees and their managers.

SELECT report.emp_fname, report.emp_lname,
   manager.emp_fname, manager.emp_lname
FROM employee AS report JOIN employee AS manager
   ON (report.manager_id = manager.emp_id)
ORDER BY report.emp_lname, report.emp_fname

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.