Self‑Join
Definition
A self‑join combines the information from two or more rows of the same table into a single result row, effectively joining the table with itself.
Example
For example, the following query asks the names of employees who have more years of experience than their managers:
SELECT workers.name, workers.yrs_exp, workers.dept_no,
managers.name, managers.yrsexp
FROM employee AS workers, employee AS managers
WHERE managers.dept_no = workers.dept_no
AND managers.job_title IN ('Manager', 'Vice Pres')
AND workers.yrs_exp > managers.yrs_exp;
The operation treats the employee table as if it were two tables; one named Workers, the other named Managers. This is accomplished by using table name aliases in the FROM clause.
ANSI calls table aliases correlation names. They are also referred to as range variables.
Because each of these fictitious tables has the same columns (name, yrs_exp, and dept_no) each column name must be qualified with the alias name of its table as defined in the FROM clause (for example, “workers.dept_no”).
The WHERE clause establishes the following things:
A possible result of this self‑join is as follows:
name
----
|
yrs_exp
-------
|
dept_no
-------
|
name
----
|
yrs_exp
------
|
Greene W
|
15
|
100
|
Jones M
|
13
|
Carter J
|
20
|
500
|
Watson L
|
8
|
Smith T
|
10
|
700
|
Watson L
|
8
|
Aguilar J
|
11
|
600
|
Regan R
|
10
|
Leidner P
|
13
|
300
|
Phan A
|
12
|
Russell S
|
25
|
300
|
Phan A
|
12
|