in defense of connect by
something on the near-term todo list in the mysql manual is “oracle-like connect by prior ...
”. every once in a while, someone drops a comment there to say that there’s no way that connect by
should be implemented, because the sql standard specifies another syntax for recursive queries, known as with
.
as it turns out, ibm’s db2 implements the with
syntax, and here’s a nice article on the difference between the two syntaxes.
i can’t see how anyone can look at that article and clamor for the with
syntax instead of connect by
. i look at the statement using connect by
and the results it gives, and can think of several ways i could apply it in applications i’ve built or want to build. i look at the with
syntax and get dizzy. the syntax of with
just looks incredibly un-natural, even for sql syntax, in a way that connect by
does not.
there are undoubtedly things you can do using the with
syntax that you couldn’t with connect by
, but nobody has been able to point them out to me. and as far as i can tell from the article on ibm’s site, getting the type of results i’m interested in requires a stored procedure and query that is at least four times as verbose as oracle’s syntax.
(disclaimer: i work for mysql ab, but am not part of the development team, have no special insights into when either syntax will get implemented. i suspect that both will eventually be implemented: connect by
as an aid to people transitioning away from oracle and because it is something a lot of people ask for, and with
as part of our commitment to supporting the sql standards.)