2

I would like to know if there is a generic SQL equivalent to Oracle's hierarchical syntax start with...connect by. I need something which can be used on any database. Here is the sort of query I mean (using Oracle's example EMP table): 

SELECT empno , ename , job , mgr ,hiredate ,level 
FROM emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr.

Recursive Common Table Expressions work for many database implementations but not for MySQL.

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
Zeeshan Khan
  • 23
  • 4
  • Can you translate your Oracle-ese into what you the behavior you'd like to see, ideally broken down as a series of steps or bullet points? At the very least give a link to what "start with" means for context. – tadman Nov 09 '12 at 03:52
  • S E L E C T e m p n o ,e n a m e , j o b ,m g r ,h i r e d a t e ,l e v e l F R O M e m p S T A R T W I T H m g r I S N U L L C O N N E C T B Y P R I O R e m p n o = m g r. This represents the hierarchial relations in the output table. The emp table is the sample emp table found in oracle database – Zeeshan Khan Nov 09 '12 at 04:25
  • 1
    Please edit your question rather than putting additional info in the comments. Also, FYI, we can use formatting in comments, which is a lot less annoying than using spaces. – APC Nov 09 '12 at 04:59
  • The nearest thing to a generic equivalent is the recursive CTE. That is not supported everywhere, though. – Jonathan Leffler Nov 09 '12 at 05:51

1 Answers1

6

There is no way of doing this in MySQL. There are some nasty hacks listed in an article by Mike Hillyer which could be used in other databases as well. But using something as inelegant as the Nested Set model in Oracle just so the same code will run on MySQL seems perverse.

The generic way would be CTE, as they are specified in SQL-99, and most flavours of RDBMS support it (even Oracle added recursiveness to its CTEs in 11gR2). The lack of support for CTE in MySQL was raised as a bug in 2006. Perhaps now Oracle owns MySQL they will get around to implementing it.

However, it really depends on your business reasons for wanting a generic solution and which database versions you really need to cover. It is a truism of writing database applications which can run on any RDBMS that they run well on none of them.

APC
  • 144,005
  • 19
  • 170
  • 281
  • It might be worth noting that nearly all other DBMS (including open source DBMS like PostgreSQL, Firebird, H2, HSQLDB) support recursive CTEs –  Nov 09 '12 at 07:47