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.)