Mastering Oracle SQL

Self Joins

There are situations in which one row of a table is related to another row of the same table. The EMPLOYEE table is a good example. The manager of one employee is also an employee. The rows for both are in the same EMPLOYEE table. This relationship is indicated in the MANAGER_EMP_ID column:

CREATE TABLE EMPLOYEE (
EMP_ID          NUMBER (4) NOT NULL PRIMARY KEY,
FNAME           VARCHAR2 (15), 
LNAME           VARCHAR2 (15), 
DEPT_ID         NUMBER (2),
MANAGER_EMP_ID  NUMBER (4) REFERENCES EMPLOYEE(EMP_ID),
SALARY          NUMBER (7,2),
HIRE_DATE       DATE, 
JOB_ID          NUMBER (3));

To get information about an employee and his manager, you have to join the EMPLOYEE table with itself. This is achieved by specifying the EMPLOYEE table twice in the FROM clause and using two different table aliases, thereby treating EMPLOYEE as if it were two separate tables. The following example lists the name of each employee and his manager:

            SELECT E.LNAME EMPLOYEE, M.NAME MANAGER
            FROM EMPLOYEE E, EMPLOYEE M
            WHERE E.MANAGER_EMP_ID = M.EMP_ID;

EMPLOYEE   MANAGER
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK

13 rows selected.

Notice the use of the EMPLOYEE table twice in the FROM clause with two different aliases. Also notice the join condition that reads as: “Where the employee’s MANAGER_EMP_ID is the same as his manager’s EMP_ID.”

Self Outer Joins

Even though the EMPLOYEE table has 14 rows, the previous query returned ...