CONNECT BY PRIOR - Hierarchical Queries - Oracle to MariaDB Migration

In Oracle, you can use CONNECT BY PRIOR clause of the SELECT statement to build hierarchical queries. MariaDB allows you to use Recursive Commom Table Expressions (CTE) to get the same functionality.

One of the simplest use of CONNECT BY is to generate an arbitrary number of rows. For example, the following query generates 5 rows:

Oracle:

  SELECT level
  FROM dual
  CONNECT BY level <= 5;

Result:

In MariaDB you can use the following recursive common table expression to get the same result:

MariaDB:

  WITH RECURSIVE cte_connect_by AS (
     SELECT 1 AS level
     UNION ALL
     SELECT level + 1 AS level FROM cte_connect_by WHERE level <= 4
  )
  SELECT level
  FROM cte_connect_by;

Result:

Additionally, Oracle provides the SYS_CONNECT_BY_PATH function that allows you to build the path from root node:

Oracle:

  SELECT level, SYS_CONNECT_BY_PATH('a', '/') AS path
  FROM dual
  CONNECT BY level <= 5;

Result:

The following recursive CTE implements CONNECT BY with SYS_CONNECT_BY_PATH in MariaDB:

  WITH RECURSIVE cte_connect_by AS (
     SELECT 1 AS level, CAST(CONCAT('/', 'a') AS VARCHAR(4000)) AS connect_by_path
     UNION ALL
     SELECT level + 1 AS level, CONCAT(connect_by_path, '/', 'a') AS connect_by_path 
       FROM cte_connect_by WHERE level <= 4
  )
  SELECT level, connect_by_path
  FROM cte_connect_by;

Now let's consider the conversion of hierarchical queries. Assume with the have the following employees table:

   CREATE TABLE employees (
      id       INT,
      name  VARCHAR(10),
      mng_id INT
   );
 
  INSERT INTO employees VALUES(1, 'John', NULL);
  INSERT INTO employees VALUES(2, 'Paul', 1);
  INSERT INTO employees VALUES(3, 'Nancy', 1);
  INSERT INTO employees VALUES(4, 'Sarah', 3);
  COMMIT;

The following query shows all levels of hierarchy between managers and employees:

Oracle:

  SELECT id, name, mng_id, level
  FROM employees
  CONNECT BY PRIOR id = mng_id
  ORDER BY id;

Result:

You can use the following recursive CTE query in MariaDB:

MariaDB:

  WITH RECURSIVE cte_connect_by AS (
     SELECT 1 AS level, s.* FROM employees s
     UNION ALL
     SELECT level + 1 AS level, s.* 
        FROM cte_connect_by r INNER JOIN employees s ON  r.id = s.mng_id
  )
  SELECT id, name, mng_id, level
  FROM cte_connect_by
  ORDER BY id;

Result:

The hierarchical queries can have the START WITH clause to define the root node as well as SYS_CONNECT_BY_PATH function to build the hierarchy path:

Oracle:

  SELECT id, name, mng_id, level, SYS_CONNECT_BY_PATH(name, '/') path
  FROM employees
  START WITH id = 1 CONNECT BY PRIOR id = mng_id
  ORDER BY id;

Result:

In MariaDB, it corresponds to the following recursive query:

MariaDB:

  WITH RECURSIVE cte_connect_by AS (
     SELECT 1 AS level, CAST(CONCAT('/', name) AS VARCHAR(4000)) AS connect_by_path, s.* 
       FROM employees s WHERE id = 1
     UNION ALL
     SELECT level + 1 AS level, CONCAT(connect_by_path, '/', s.name) AS connect_by_path, s.* 
       FROM cte_connect_by r INNER JOIN employees s ON  r.id = s.mng_id
  )
  SELECT id, name, mng_id, level, connect_by_path path
  FROM cte_connect_by
  ORDER BY id;

Result:

For more information, see Oracle to MariaDB Migration.