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 ...
Get Mastering Oracle SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.