Connect by Prior Equivalent For MySQL

Is there a `connect by` alternative in MySQL?

As said in comments, there isn't a short way with mysql.

BUT!

If you have the possibility to change the database structure, you can deploy a better design to handle tree-like hierarchies.

If you follow THIS TUTORIAL from Bill Karwin (HERE is the original answer which references that slideshow tutorial), you can find 4 methods used to model an hierarchical structure:

1. Adiacency List
2. Path Enumeration
3. Nested sets
4. Closure table

Now, the best model possible is the 4th one (I leave descriptions of the other 3 models to the reader), which basically needs 2 tables: one for the elements and one for the paths. In the paths table (the closure table itself), you'll store every path from each node to every descendant (not just the direct childs!).

It's suggested to save also the path length for each row, because it makes easier to query for immediate childrens in the tree.

Even if this solution requires more space, it has the best overall performance and it's really easy to use: it doesn't rely on recursive queries at all AND it will grants referential integrity for the whole dataset!

For example, to get every child of the node #4:

select a.*
from nodes a
join paths b
on a.node_id = b.descendant
where b.ancestor = 4

Another example: get all the ancestors of the node #11

select a.*
from nodes a
join paths b
on a.node_id = b.ancestor
where b.descendant = 11

need to delete the subtree of the node #6

delete from paths where descendant in
(select descendant from paths where ancestor = 6)

Hierarchical Query in MySQL. (connect by equivalent for MySQL)

There is no native hierarchical query support in MySQL.

For a finite number of levels to be traversed, we can write queries that get result for each level, and combine the results with a UNION ALL operator.

Or, we can write a MySQL stored program (procedure) for a more recursive approach.

As an example of approach using a native SQL query:

 SELECT t0.comp_code
   FROM tb_corp t0
  WHERE t0.mgr_emp_no = 111

 UNION ALL

SELECT t1.comp_code
  FROM tb_corp t0
  JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
 WHERE t0.mgr_emp_no = 111

 UNION ALL

SELECT t2.comp_code
  FROM tb_corp t0
  JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
  JOIN tb_corp t2 ON t2.incharge_comp_code = t1.comp_code
 WHERE t0.mgr_emp_no = 111

 UNION ALL

SELECT t3.comp_code
  FROM tb_corp t0
  JOIN tb_corp t1 ON t1.incharge_comp_code = t0.comp_code
  JOIN tb_corp t2 ON t2.incharge_comp_code = t1.comp_code
  JOIN tb_corp t3 ON t3.incharge_comp_code = t2.comp_code
 WHERE t0.mgr_emp_no = 111

etc. This approach can be extended to t4, t5, t6, ... down to some (reasonable) finite number of levels.

For a more recursive approach, a MySQL stored program (PROCEDURE) can be written.

OR operator in CONNECT BY PRIOR

You are missing the PRIOR operator applying to the second occurrence of DATAID:

SELECT * FROM DTREE  start with DATAID=111
connect by prior dataid=parentid OR   prior  /* <-- MISSING!!! */  dataid=ownerid;

Alternatively:

connect by prior dataid in (parented, ownerid);

Converting oracle's connect by prior to mariaDB

I do not use MariaDB, but as I see in documentation it supports recursive common table expressions. So you can write something like:

with recursive rcte as (
  select num, id, parentid, data1 from table_name where parentid = 0 and num = 1048
  union all 
  select t.num, t.id, t.parentid, t.data1 
    from table_name t join rcte on t.parentid = rcte.id and t.num = rcte.num)
select * from rcte

^{MariaDB dbfiddle}

^{Oracle dbfiddle}

If you want to reproduce Oracle order by siblings you need to create hierarchy column and use it for ordering:

with recursive rcte as (
  select cast(id as char) AS hierarchy, num, id, parentid, data1 
    from table_name where parentid = 0 and num = 1048
  union all 
  select concat(rcte.hierarchy, '-', t.id), t.num, t.id, t.parentid, t.data1 
    from table_name t join rcte on t.parentid = rcte.id and t.num = rcte.num)
select * 
   from rcte
   order by hierarchy

What is the standard SQL query equivalent to Oracle's 'start with...connect by', but not DBMS specific

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.

Standard alternative to CONNECT BY?

In Oracle 11gR2 they support recursion in Common Table Expressions (what most Oracle people know as sub-querying factoring, i.e. the WITH clause). As this is the ANSI way of doing things it ought to be more portable.

Tom Kyte writes about Recursive Subquery Factoring in the November 09 edition of Oracle magazine.

CONNECT BY or hierarchical queries in RDBMS other than Oracle

There is an article on the developerworks site Port CONNECT BY to DB2 that does a nice conversion.
Also an interesting article on Explain Extended (Quassnoi's blog) that shows some difference between CONNECT BY and recursive CTE: Adjacency list vs. nested sets: Oracle, being row-based and set-based. He has also a nice article about "SQL Server: are the recursive CTE’s really set-based?". It seems that the "recursive CTE in Oracle is also not set based". I hope this helps with the conversion, recursion in JOOQ and understanding the difference of both implementations of recursion in SQL.

Regards,
JJ.

---

Related Topics