Joining a Table to Itself

In some situations, you may find it necessary to join a table to itself, as though you were joining two separate tables. This is referred to as a self join. In the self join, the combined result consists of two rows from the same table.

For example, suppose that within the EMPLOYEE table, personnel are assigned a RANK of "STAFF", "DHEAD", and so on. To obtain a list of employees that includes employee name and the name of his or her department head requires the use of a self join.

To join a table to itself, the table name appears twice in the FROM clause. To distinguish between the appearance of the same table name, a temporary name, called an alias or a correlation name, is assigned to each mention of the table name in the FROM clause. The form of the FROM clause with an alias is:

FROM   table-name [alias1] [,table-name [alias2] ] ...

To help clarify the meaning of the query, the alias can be used as a qualifier, in the same way that the table name serves as a qualifier, in SELECT and WHERE clauses.

Example: As part of an analysis of Acme's salary structure, you want to identify the names of any regular staff who are earning more than a department head.

Enter this query:

SELECT   STAFF.ENAME, STAFF.SALARY
     FROM     EMPLOYEE DHEAD, EMPLOYEE STAFF
     WHERE    DHEAD.RANK = 'DHEAD' AND STAFF.RANK = 'STAFF'
       AND    STAFF.SALARY > DHEAD.SALARY ;

Using a sampling of information from the EMPLOYEE table, we would get these results:

ENAME               SALARY

  Sanchez, Carla      35000

In this query, the EMPLOYEE table, using the alias feature, is treated as two separate tables named DHEAD and STAFF, as shown here (in shortened form):

DHEAD Table                         STAFF Table
  EID ENAME   DEPT RANK  BEN  SALARY  EID ENAME   DEPT RANK  BEN  SALARY
  101 Aster   MKT  STAFF FULL 32000   101 Aster   MKT  STAFF FULL 32000
  109 Brown   MKT  DHEAD FULL 37500   109 Brown   MKT  DHEAD FULL 37500
  103 Chapman LIB  STAFF PART 22000   103 Chapman LIB  STAFF PART 22000
  110 Krinski LIB  DHEAD FULL 32500   110 Krinski LIB  DHEAD FULL 32500
  106 Sanchez MKT  STAFF FULL 35000   106 Sanchez MKT  STAFF FULL 35000

Now the join operation can be made use of, as if there were two separate tables, evaluated as follows.

First, using the following compound condition:

DHEAD.RANK = 'DHEAD' AND STAFF.RANK = 'STAFF'

each department head record (Brown, Krinski) in the DHEAD table is joined with each staff record (Aster, Chapman, Sanchez) from the STAFF table to form the following intermediate result:

DHEAD Table                         STAFF Table
  EID ENAME   DEPT RANK  BEN  SALARY  EID ENAME   DEPT RANK  BEN  SALARY
  109 Brown   MKT  DHEAD FULL 37500   101 Aster   MKT  STAFF FULL 32000
  109 Brown   MKT  DHEAD FULL 37500   103 Chapman LIB  STAFF PART 22000
  109 Brown   MKT  DHEAD FULL 37500   106 Sanchez MKT  STAFF FULL 35000
  110 Krinski LIB  DHEAD FULL 32500   101 Aster   MKT  STAFF FULL 32000
  110 Krinski LIB  DHEAD FULL 32500   103 Chapman LIB  STAFF PART 22000
  110 Krinski LIB  DHEAD FULL 32500   106 Sanchez MKT  STAFF FULL 35000

Notice that every department head row is combined with each staff record.

Next, using the condition:

STAFF.SALARY > DHEAD.SALARY

for each row of the joined table, the salary value from the STAFF portion is compared with the corresponding salary value from the DHEAD portion. If STAFF.SALARY is greater than DHEAD.SALARY, then STAFF.ENAME and STAFF.SALARY are retrieved in the final table.

The only row in the joined table satisfying this condition of staff salary being greater than department head salary is the last one, where Carla Sanchez from Marketing, at a salary of $35,000, is earning more than Wanda Krinski, as department head for the Corporate Library, at a salary of $32,500.