from the Archive
in defense of connect by
something on the near-term todo list in the mysql manual is oracle-like
. every once in a while, someone drops a comment there to say that there’s no way that connect by prior ...
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.)
Comments
Add a comment
Sorry, comments on this post are closed.
Having worked with both Oracle and DB2 before, I can definitely say that the CONNECT BY...PRIOR syntax works beautifully with hierachical data. I don't know how I could do an EXPLAIN PLAN without it.