Accessing Multiple Tables › Joining a Table to Itself

Joining a Table to Itself

You join a table to itself when two rows in a table contain information that you want to combine together. This is called a reflexive join.

How It's Done

The Human Resources department needs to identify Catherine William's manager. The EMPLOYEE table contains the employee ID and the manager ID. To find the name of Catherine William's manager, first you find Catherine William's employee ID (3411) in the EMP_ID column. The row containing Catherine William's employee ID also contains her manager's employee ID (2894) in the MANAGER_ID column. Now you look in the EMPLOYEE table again to find the manager's ID (2894) in the employee ID column. You'll find the manager's name in this row:

                      EMPLOYEE
                   ┌────────┬───────────┬────────────┐
                   │EMP_ID  │EMP_LNAME  │MANAGER_ID  │
                   ├────────┼───────────┼────────────┤
                   │2096    │CARLSON    │4321        │
                   │        │           │            │
                   │3411    │WILLIAMS   │2894        │
                   │        │           │            │
                   │2894    │GRIFFIN    │1003        │
                   └────────┴─────┬─────┴────────────┘
                                  │
                                  │
                                  │
                                  ▼
┌────────┬───────────┬────────────┬────────┬───────────┬────────────┐
│EMP_ID  │EMP_LNAME  │MANAGER_ID  │EMP_ID  │EMP_LNAME  │MANAGER_ID  │
├────────┼───────────┼────────────┼────────┼───────────┼────────────┤
│3411    │WILLIAMS   │2894        │2894    │GRIFFIN    │1003        │
└────────┴───────────┴────────────┴────────┴───────────┴────────────┘

To access this information, enter:

select mgr.emp_lname as "Manager",
       sub.emp_lname as "Subordinate"
       from employee mgr, employee sub
       where mgr.emp_id = sub.manager_id
         and sub.emp_id = 3411;

The result looks like this:

Manager               Subordinate
-------               -----------
Griffin               Williams

1 row processed

Things to Remember about a Reflexive Join

• You assign aliases so that the manager's employee ID can be distinguished from the subordinate's employee ID
• You name the table twice in the FROM clause and give each reference an alias
• You compare the two columns that share the same type of information in the WHERE clause (MGR.EMP_ID = SUB.MANAGER_ID)