3

Take the data from the Oracle scott database. I have modified it to have multiple levels of management from the original,

select * from scott.emp order by mgr desc;

empno       ename  job        mgr   hiredate   sal   comm   deptno

7839    KING    PRESIDENT       11-17-1981  5000        10
7369    SMITH   CLERK   7902    12-17-1980  800     20
7900    JAMES   CLERK   7844    12-03-1981  950     30
7654    MARTIN  SALESMAN    7844    09-28-1981  1250    1400    30
7698    BLAKE   MANAGER 7839    05-01-1981  2850        30
7566    JONES   MANAGER 7839    04-02-1981  2975        20
7782    CLARK   MANAGER 7839    06-09-1981  2450        10
7876    ADAMS   CLERK   7788    05-23-1987  1100        20
7934    MILLER  CLERK   7782    01-23-1982  1300        10
7521    WARD    SALESMAN    7698    02-22-1981  1250    500 30
7844    TURNER  SALESMAN    7698    09-08-1981  1500    0   30
7499    ALLEN   SALESMAN    7698    02-20-1981  1600    300 30
7788    SCOTT   ANALYST 7654    04-19-1987  3000        20
7902    FORD    ANALYST 7566    12-03-1981  3000        20

If I want to know who is whose manager,

select 
  E.EMPNO, E.ENAME, E.job, E.MGR,
   M.EMPNO, M.ENAME, M.job, M.MGR
from SCOTT.EMP E, SCOTT.EMP M
where E.MGR = M.EMPNO
order by M.mgr desc;

7698    BLAKE   MANAGER 7839    7839    KING    PRESIDENT   
7566    JONES   MANAGER 7839    7839    KING    PRESIDENT   
7782    CLARK   MANAGER 7839    7839    KING    PRESIDENT   
7788    SCOTT   ANALYST 7654    7654    MARTIN  SALESMAN    7844
7499    ALLEN   SALESMAN    7698    7698    BLAKE   MANAGER 7839
7521    WARD    SALESMAN    7698    7698    BLAKE   MANAGER 7839
7844    TURNER  SALESMAN    7698    7698    BLAKE   MANAGER 7839
7902    FORD    ANALYST 7566    7566    JONES   MANAGER 7839
7934    MILLER  CLERK   7782    7782    CLARK   MANAGER 7839
7900    JAMES   CLERK   7844    7844    TURNER  SALESMAN    7698
7654    MARTIN  SALESMAN    7844    7844    TURNER  SALESMAN    7698
7876    ADAMS   CLERK   7788    7788    SCOTT   ANALYST 7654
7369    SMITH   CLERK   7902    7902    FORD    ANALYST 7566

I can see that,

73609 (smith) has a manager 7902 (ford) who has a manager 7566 (jones) who has a manager 7839 (president, null mgr).

Why would I need to use recursion here as I see so many do with Common Table Expressions? Is it just for display reasons?

4
  • 4
    Try writing a query that only returns employees that are beneath JONES (not just those who are immediately below him.)
    – user4234
    Aug 27 '15 at 17:19
  • So it is when you can't just list them all.
    – johnny
    Aug 27 '15 at 17:25
  • Given the mention of an oracle database, the way to do a recursive call is connect by prior. This type of query isn't implemented in all databases.
    – user40980
    Aug 27 '15 at 18:08
  • I'm using 11gR2, so I can use the With statement CTE style.
    – johnny
    Aug 27 '15 at 20:17
7

If you only care about direct reports, you do not need to use recursion. Every employee (except the CEO) reports to exactly one manager.

If you want to know direction and indirect reports, you need recursion. This would answer the question "who are all the reports of this director, including managers, supervisors, and peons." It might not be at the same granularity of department, which is in your table. You need to look for the direct reports of the director, then query the direct reports of those employees, and recurse until nobody reports to the employees you just fetched.


SQL is inherently not a recursive language. Standard SQL did not have recursion for many years because it is poorly modeled by the underlying relational calculus. However, Common Table Expressions (CTE) were added in SQL:1999. While not generally useful, they are very helpful in certain niches.

Pretty much all of the Enterprise-class databases such as SQL Server and Oracle have added recursive queries, but some smaller databases such as MySQL lag behind the standard.

3
  • CTEs have been part of the SQL standard as of SQL:1999, no?
    – Erik Eidt
    Aug 27 '15 at 18:04
  • @ErikEidt yes, but to this day not all databases implement it. MySQL for example still doesn't. You're left with things like preorder trees for such.
    – user40980
    Aug 27 '15 at 18:12
  • Thanks @MichaelT. I did not realize these were added in SQL:1999. ANSI SQL is largely irrelevant outside of the most basic DML/DDL due to different vendors having tons of their own extensions and special ways of doing things (caused in part by crappy earlier versions of the SQL standard). If the vendors don't care to read the standard and update their DB engines, why should I care to read it? It could do more harm than good.
    – user22815
    Aug 31 '15 at 3:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.