Oracle:Recursive Join

Main Page >> Oracle and SQL >> Workbook >> Joins >> Recursive joins

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 show all employees and who their manager is:

SELECT E.EMPNO, E.ENAME AS WORKER, M.EMPNO, M.ENAME AS MANAGER
FROM EMP E, EMP M
WHERE E.MGR = M.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;

Next Step

Outer joins.