View source for Oracle:Recursive Join

== Recursive Queries ==

Recursive queries are needed where a relation has a recursive relationship associated with it (a pig’s ear on an ER diagram). In our sample database the manager is represented by the MGR attribute, which is a recursive "manages" relationship on Emp. These queries require a table to be compared against itself as seen above.

To compare a table against itself, you have to create two “versions” of the table and use them as if they were two different tables. The join condition in this case will compare an employee’s manager number (MGR) against their manager’s employee number (EMPNO).

To list employees whose salary exceeds their manager's:

SELECT E.ENAME AS WORKER, E.SAL, M.ENAME AS MANAGER, M.SAL
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO
AND E.SAL > M.SAL;