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.
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”).
- A key to both tables (dept_no)
- Which employees belong in the managers table (first AND)
- Which workers and managers should be listed in the tables (second AND)
A possible result of this self-join is as follows:
|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|