CONNECT BY or hierarchical queries in a non-Oracle database management system

Question

Oracle comes with a very handy feature. You can create hierarchical queries (recursive behavior) using the following sentence:

CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition] 

As described here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm

I am wondering if there are any other RDBMS that support equivalent or similar syntax? Or maybe there is a recursive behavior like this, generally modeled using regular SQL?

A good example I would like to simulate is this (taken from Oracle documentation):

  SELECT LPAD(' ', 2 * (LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; 

Result:

 ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Greenberg 108 101 FI_MGR Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG 

The LEVEL pseudo-column and the indent achieved with it are not so important to me

Answer 1

There is an developerWorks Port CONNECT BY article for DB2 that does a nice conversion. Also an interesting article about Explain Extended (Quassnoi blog), which shows some difference between CONNECT BY and recursive CTE: Adjacency list versus nested sets: Oracle , row-based and set-based. He also has a good article on "SQL Server: Are Recursive CTEs Really Customized?" It seems that "recursive CTE in Oracle is also not set based." Hope this helps with conversion, recursion in JOOQ and understanding the difference between implementations of recursion in SQL.

Regards, JJ.

Answer 2

SQL Server uses common table expressions (WITH statement) to achieve the same (see Recursive queries using common table expressions ).

This kind of query can also be used in Oracle (starting with 11g, if I'm not mistaken).

The received request is more complex:

 WITH emp(employee_id, manager_id, job_id, last_name, lvl) AS ( SELECT e.employee_id, e.manager_id, e.job_id, e.last_name, 1 lvl FROM employees e WHERE job_id = 'AD_VP' UNION ALL SELECT e.employee_id, e.manager_id, e.job_id, e.last_name, r.lvl + 1 lvl FROM employees e JOIN emp r ON r.employee_id = e.manager_id ) SELECT LPAD(' ', 2 * (lvl-1)) || last_name org_chart, employee_id, manager_id, job_id FROM emp; 

Answer 3

Traffic via SO showed the following questions and answers regarding hierarchical queries across various databases. The last one relates to the MySql resource , which gives a general SQL approach.

Creating a dependency graph for a table with a recursive query

Recursive selection in SQL

SQL recursive query

Depth-based tree generation from hierarchical data in MySQL (without CTE)