The meaning of a mysql query

I’m working with hierarchical data in my cms. My menu table is this:

id || name || lft || rgt

And my left and right columns are:

To retrieving a full tree and it’s depth, I’ve found this query:

SELECT node.id, node.name, (COUNT(parent.name) - 1) AS depth
                                FROM menu AS node,
                                menu AS parent
                                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                                GROUP BY node.name
                                ORDER BY node.lft;

There is a join, table with itself. How dose it retrieve whole tree?

Thank you very much.

Answer

If you want to do hierarchical queries in a tree with SQL, you need a special data model.

Your example uses the Nested set model.