A reflexive join (also known as a self-join) is the join of a table to itself. For example, typically employees and managers are stored in the same table since all of the individuals are ultimately employees. The manager ID is stored as a foreign key in each employee row and references the same table.
To learn how to use reflexive joins, follow these steps:
- You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 in this topic before proceeding to the next step.
- Let's construct a reflexive join to list the manager names of each employee in the
Employeestable. Execute the following select:
Note that I have used the SQL*Plus
colcommand to limit column width to make the report more readable. Also note that I have displayed the first part of the output that contains 106 employees overall. I have highlighted the reflexive join that references the
Employeestable on either side of the
inner joinkeywords. The first reference treats the table as an employees table and second reference treats the table as a managers table. The
onclause matches an employee with his or her manager.