Does MySQL Support CONNECT BY? Understanding Hierarchical Queries

MySQL does not have a CONNECT BY clause, but it can handle hierarchical data with other methods

CONNECT BY is a feature in Oracle Database that lets you query data organized in a tree or hierarchy — like an organizational chart where each employee has a manager, or a category system where each item belongs to a parent category. MySQL does not include this exact syntax. If you are moving a database from Oracle to MySQL, or writing queries that need to work across both systems, you will need to use different approaches.

The good news is that MySQL can do hierarchical queries. You just write them differently. The method you choose depends on how your data is structured and what version of MySQL you are running.

Key Takeaways

CONNECT BY is an Oracle feature that MySQL does not support directly, so queries written for Oracle will not work unchanged in MySQL.
MySQL 8.0 and later support Common Table Expressions (CTEs) with the WITH clause, which is the modern way to handle hierarchical queries.
Older MySQL versions can use self-joins or stored procedures to walk up or down a hierarchy, though these are slower and more complex to write.
The best approach depends on your MySQL version and whether your hierarchy is stored as nested sets, adjacency lists, or path strings.

Using Common Table Expressions (CTEs) in MySQL 8.0 and later

If you are running MySQL 8.0 or newer, Common Table Expressions (CTEs) with the RECURSIVE keyword are the standard way to replace CONNECT BY. A CTE is a temporary result set that you define at the start of your query and then reference below. The RECURSIVE keyword lets the CTE call itself, which is how you walk through a hierarchy.

Here is a straightforward example. Suppose you have an employees table with an id, name, and manager_id column. To find all employees under a specific manager, you would write:

WITH RECURSIVE employee_hierarchy AS ( SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, eh.level + 1 FROM employees e INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id ) SELECT * FROM employee_hierarchy;

This query starts by finding the top-level employees (those with no manager). Then it repeatedly joins the employees table to itself, finding everyone who reports to someone already in the result set. Each pass adds one to the level counter. The result is a complete tree of the organization.

Self-joins for MySQL versions before 8.0

If you are stuck with MySQL 5.7 or earlier, you cannot use recursive CTEs. Instead, you can use self-joins — joining a table to itself multiple times to walk down the hierarchy. This works but becomes unwieldy quickly, especially if your tree is deep.

For the same employees example, finding all direct reports of a specific manager would look like:

SELECT e1.id, e1.name, e1.manager_id FROM employees e1 WHERE e1.manager_id = (SELECT id FROM employees WHERE name = 'John');

To find employees two levels down, you add another join:

SELECT e2.id, e2.name, e2.manager_id FROM employees e1 INNER JOIN employees e2 ON e1.id = e2.manager_id WHERE e1.manager_id = (SELECT id FROM employees WHERE name = 'John');

Each additional level requires another join. If you do not know how deep the tree goes, you have to write a stored procedure that loops through joins until no new rows appear. This is slow and hard to maintain.

Stored procedures for complex hierarchies

For older MySQL versions handling deep or unpredictable hierarchies, a stored procedure is often the only practical choice. A stored procedure is a block of SQL code that MySQL stores and runs on demand. You can write one that loops through a hierarchy, building up a result set one level at a time.

Stored procedures are more work to write and debug than a single query, and they run slower than a well-designed CTE. But they let you handle cases where the depth is unknown or where the hierarchy is complex enough that self-joins become unmanageable. If you need this approach, your database administrator or a developer familiar with MySQL stored procedures should write it for your specific data structure.

Nested sets and path-based storage

Some databases store hierarchical data differently than the straightforward parent-id approach. A nested set model assigns each node a left and right boundary number that encodes the entire tree structure. A path-based model stores the full path from root to node as a string or array.

These approaches can make certain queries faster because you do not need to walk the tree at all — the hierarchy is already encoded in the data. However, they make inserts and updates more complex because you have to recalculate all the numbers or paths. Most new projects use the straightforward parent-id approach with a recursive CTE, which balances simplicity with performance.

If you are inheriting a database that uses nested sets or paths, the queries look completely different from CONNECT BY. Your database documentation or administrator should explain how to query that specific structure.

Converting CONNECT BY queries from Oracle

If you have working CONNECT BY queries in Oracle and need to move them to MySQL 8.0 or later, the conversion usually follows a pattern. The CONNECT BY clause becomes a recursive CTE. The START WITH condition becomes the base query (the part before UNION ALL). The CONNECT BY condition becomes the join in the recursive part.

An Oracle query like:

SELECT * FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR id = manager_id;

becomes:

WITH RECURSIVE org AS ( SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN org ON e.manager_id = org.id ) SELECT * FROM org;

The logic is the same, but the syntax is different. If you have many queries to convert, a developer familiar with both systems can usually do it systematically.

Frequently Asked Questions

Will MySQL ever add CONNECT BY support?

MySQL has not announced plans to add CONNECT BY. The MySQL team chose to implement recursive CTEs instead, which is the SQL standard way to handle hierarchies. Recursive CTEs are more flexible and work the same way across different databases, so they are the direction the industry moved.

What if I upgrade from MySQL 5.7 to 8.0 — do my old hierarchy queries still work?

Your old self-join queries will still work, but you should rewrite them as recursive CTEs. CTEs are faster, easier to read, and easier to maintain. The upgrade itself does not change your queries, but it gives you the option to improve them.

Is a recursive CTE slower than CONNECT BY in Oracle?

Performance depends on your specific data and query, but recursive CTEs in MySQL 8.0 are generally comparable to CONNECT BY in Oracle. Both walk the hierarchy one level at a time. If you have a very large tree and need maximum speed, a nested set model can be faster, but it requires more complex data management.

Can I use a recursive CTE to find the path from a child back to the root?

Yes. Instead of starting with the root and working down, you start with a specific employee and work up by joining on manager_id repeatedly. The recursive CTE will keep going until it reaches someone with no manager, building the complete chain from that person to the top.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.