If a join is defined as a request in which rows are retrieved from more than one table, a self-join can loosely be defined as a request in which rows are retrieved from a single table that is redefined as two tables by using correlation names to appear as if there are 2 copies of the same table under different names.
Suppose you want to determine all two-way pairings of employees who live in the same country. To determine this, you would use a self-join of the employee table.
The rows from employee might look something like this:
To determine the two-way pairings of employees living in the same country, you would submit the following self-join request:
SELECT e.emp_num, e.name, f.emp_num, f.name, e.country FROM employee AS e, employee AS f WHERE e.country = f.country AND e.emp_num < f.emp_num ORDER BY e.emp_num, f.emp_num;
By specifying two different correlation names for employee, you are able to join the table to itself. Note that the condition e.country = f.country pairs only those employees who live in the same country, while the condition e.emp_num < f.emp_num pairs only employees who have different employee numbers.
The request produces the following result table:
|employee after self-join on country|