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, we 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.NAME EMPLOYEE, M.NAME MANAGER

FROM EMPLOYEE E, EMPLOYEE M

WHERE E.MANAGER_EMP_ID = M.EMP_ID;