How to Use Reflexive Joins

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:

  1. 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.
  2. Let's construct a reflexive join to list the manager names of each employee in the Employees table. Execute the following select:
    Display employees and department name with inner join
    Note that I have used the SQL*Plus col command 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 Employees table on either side of the inner join keywords. The first reference treats the table as an employees table and second reference treats the table as a managers table. The on clause matches an employee with his or her manager.
Author: Stephen Withrow

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.