Question: If I use Oracle there is the `connect by` keyword which can be used to create hierarchical queries. Currently I'm using MySQL on a project and I would like to know whether there is an alternative for `connect by` in MySQL?

I tried google but for no avail so far. What I'm trying to achieve is to fetch a tree from the database with one query. There are two tables involved:

`areas` and `area_to_parent_join`. The latter contains two ids one is `area_id` and the other is `parent_id`. So it is basically a self-join and I can create graphs using that model. In fact it is currently only used to create trees but this might change in the future. But in either case what I would like to have is just a spanning tree.

**Edit:** `areas` might have more than 1.000.000 records which makes most of the space-intensive options unfeasible.

Answer: 
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](http://www.slideshare.net/billkarwin/models-for-hierarchical-data)** from Bill Karwin (**[HERE](https://stackoverflow.com/a/192462/998759)** 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)

```
In my application, I rarely need to ask for the entire subtree. So, to get away from the big issue of o2, I use 3-deep association / closure table lookups - populating the table with ancestor - descendent only for child, parent, and grandparents. Just recognize that you get what you put in - i.e. don't query for the entire tree without a stored procedure.