SQL CTE (Common Table Expressions) with recursion example in MariaDB/MySQL

cte_example.sql

USE test;
-- we use recursive CTE to find a tree of managers and count their place in hierarchy tree from top to bottom
-- remarks:
-- 1) you must add recursive word when using recursion in MariaDB
-- 2) you must SELECT from table, NOT FROM cte inside recursive part
-- https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
WITH RECURSIVE cte_employee AS
(
   -- anchor part where we look for CEO who is on top of hierarchy
   SELECT employee.*, 0 AS hierarchy_level FROM employee
   WHERE manager_id = 0
   UNION ALL 
   SELECT e.*,  cte_employee.hierarchy_level + 1 AS hierarchy_level 
   FROM employee AS e
   -- here is where recursive part happens
   -- you go down the hierarchy looking for eployees who's manager is the current person
   -- so CEO (anchor) -> Marketing Directors (employee.manager_id = 7) -> Marketer (employee.manager_id = 8 OR employee.manager_id = 9) ...
   INNER JOIN cte_employee ON (cte_employee.id = e.manager_id)
)
SELECT * FROM cte_employee;

example_table.sql

DROP TABLE IF EXISTS `test`.`employee`;

CREATE TABLE `test`.`employee` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `manager_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`));
 
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev1', '10');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev2', '10');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev3', '11');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev4', '12');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Marketer1', '9');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Marketer2', '9');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('CEO', '0');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Managers Director', '7');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Marketing Director', '7');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Manager1', '8');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Manager2', '8');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Manager3', '8');

expected_result.csv