Well, I'll say connect by has been around for some time....


Anyone care to guess what version/year?


Version 2, 1977.


so, yes, connect by is an oldie but goodie. (and recursive queries in DB2 - they don't have the 1436 or NOCYCLE, they can be infinite loops according to the link you provided - careful with that...)


I don't know about you - but I find the understandability of:

1 SELECT name
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid

in Oracle versus:

1 WITH n(empid, name) AS
2 (SELECT empid, name
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT nplus1.empid, nplus1.name
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT name FROM n;

in DB2 et.al. to be night and day difference. I'm not sure what the motivation to introduce this syntax would be entirely. Given that the databases are different and what works on DB2 doesn't necessarily work on SQLSserver and what works on SQLServer doesn't necessarily work on Oracle and in fact:

What works on Database X
does not necessarily work
on Database Y

is true.... I am not aware of any move to provide this syntax (especially given the fact that the HIERARCHY is busted totally with this "recursive" approach, the Oracle query returns:

1 SELECT level, rpad('*',2*level,'*')||name name
2 FROM emp
3 START WITH name = 'Goyal'
4* CONNECT BY PRIOR empid = mgrid
ops$tkyte@ORA10GR2> /

LEVEL NAME
---------- --------------------
1 **Goyal
2 ****Zander
3 ******McKeough
3 ******Barnes
2 ****Henry
3 ******O'Neil
3 ******Smith
3 ******Shoeman
2 ****Scott

9 rows selected.


whereas the db2 query produces this:

1 WITH n(level, empid, name) AS
2 (SELECT 1, empid, name
3 FROM emp
4 WHERE name = 'Goyal'
5 UNION ALL
6 SELECT n.level + 1, nplus1.empid, nplus1.name
7 FROM emp as nplus1, n
8 WHERE n.empid = nplus1.mgrid)
9 SELECT level, name FROM n;

LEVEL NAME
----------- ----------
1 Goyal
2 Zander
2 Henry
2 Scott
3 McKeough
3 Barnes
3 O'Neil
3 Smith
3 Shoeman

9 record(s) selected


I see we can simulate it on a "case by case basis, sometimes" by writing a UDF connect_by_pos function written in C, we could code this:


1 WITH source(empid, name, salary, mgrid, rownum) AS
2 (SELECT empid, name, salary, mgrid,
3 ROW_NUMBER() OVER(ORDER BY salary)
4 FROM emp),
5 n(empid, name, salary, level, pos) AS
6 (SELECT empid, name, salary, 1,
7 CONNECT_BY_POS('', 0, rownum)
8 FROM source
9 WHERE name = 'Goyal'
10 UNION ALL
11 SELECT nplus1.empid, nplus1.name,
12 nplus1.salary, level + 1,
13 CONNECT_BY_POS(n.pos, level, rownum)
14 FROM source AS nplus1, n
15 WHERE n.empid = nplus1.mgrid)
16 SELECT name, level, salary
17 FROM n
18 ORDER BY pos;

NAME LEVEL SALARY
---------- ----------- -----------
Goyal 1 80000.00
Henry 2 51000.00
Shoeman 3 33000.00
Smith 3 34000.00
O'Neil 3 36000.00
Zander 2 52000.00
Barnes 3 41000.00
McKeough 3 42000.00
Scott 2 53000.00

9 record(s) selected


I'll stick with connect by for now if you don't mind.

Addenda: June 2019

Note that recursive WITH was added in 11.2 of the Oracle database engine, so now you can use CONNECT BY *or* recursive WITH.