CONNECT BY or hierarchical queries in RDBMS other than Oracle

Question

Oracle ships with a very handy feature. You can create hierarchical queries (recursive behaviour) using the following clause:

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

As documented here:

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

I'm wondering, are there any other established RDBMS that support an equivalent or similar syntax? Or can recursive behaviour like this be generically simulated using regular SQL?

A good example that I'd like to be able to simulate is this (taken from the 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; 

Resulting in:

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 indentation achieved with it is not so important to me

Lotti

Answer 1

There is an article on the developerworks site Port CONNECT BY to DB2 that does a nice conversion. Also an interesting article on Explain Extended (Quassnoi's blog) that shows some difference between CONNECT BY and recursive CTE: Adjacency list vs. nested sets: Oracle, being row-based and set-based. He has also a nice article about "SQL Server: are the recursive CTE’s really set-based?". It seems that the "recursive CTE in Oracle is also not set based". I hope this helps with the conversion, recursion in JOOQ and understanding the difference of both implementations of recursion in SQL.

Regards, JJ.

Wexler

That's a very nice resource by developerworks! I like the way it explains all involved keywords and how they would be applied in a recursive CTE. With this resource, I might finally tackle the simulation in jooq.org! – Lotti

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 resulting query 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;

Overwork

Aah, that's very interesting. I have never thought about the WITH clause that way. So in principle, this approach is a bit less powerful (because much more verbose), but more general? Chances are high that this is also supported (in the future) by other RDBMS supporting a WITH clause... – Lotti

Very very nice. Thanks for the example! – Lotti

To complete the picture: recursive CTE is available in Firebird, PostgreSQL, DB2, Teradata and Sybase (and I think H2 as well) – Murrain

Hmm @Codo, I was wondering again about these things. In jooq.org, I support Oracle's CONNECT BY syntax already: sourceforge.net/apps/trac/jooq/wiki/Manual/ADVANCED/CONNECTBY. Now, I would like to simulate Oracle's CONNECT BY syntax for other RDBMS (in addition to supporting the WITH clause itself). Do you think a formal transformation from the simpler CONNECT BY clause (with or without STARTS WITH) into a CTE can be achieved? That would be an awesome feature for jOOQ...! – Lotti

Answer 3

A trawl through SO showed the following questions and answers that deal with hierarchical queries over a variety of databases. The last of these refers to a MySql resource, that gives a generic SQL approach.

Building a Table Dependency Graph With A Recursive Query

Recursive select in SQL

SQL recursive query

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

Rectangle

Thanks for your hints. That's a nice resource from MySQL – Lotti