Using LATERAL and Recursive CTEs in MySQL to Count Direct and Indirect Employee Reports

Using LATERAL , a JOIN can include a second table – a derived table based on a subquery – whose definition depends on column values from the first table, allowing the derived table to be recomputed for each row of the first table.

Typical example:

SELECT ... FROM t1, LATERAL (SELECT ... FROM t2 ... ) AS derived;

In the derived table, t1.col is a “lateral external reference” to the first table t1 . The referenced table is placed next to the derived table, i.e., both are part of the same FROM clause.

When implementing this LATERAL feature, an additional capability was added: support for non‑lateral external references inside derived tables.

Hierarchical data example (employees table):

CREATE TABLE employees (
  id INT PRIMARY KEY NOT NULL,
  name VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
  FOREIGN KEY (manager_id) REFERENCES employees (id)
);

INSERT INTO employees VALUES
  (333, "Yasmina", NULL),   -- CEO
  (198, "John", 333),
  (692, "Tarek", 333),
  (29, "Pedro", 198),
  (4610, "Sarah", 29),
  (72, "Pierre", 29),
  (123, "Adil", 692);

Query to count how many direct and indirect reports each employee has (uses MySQL recursive CTE):

SELECT emp.*,
  (
    WITH RECURSIVE reports AS (
      SELECT emp.id
      UNION ALL
      SELECT e.id
      FROM reports AS rep
      JOIN employees AS e ON rep.id = e.manager_id
    )
    SELECT COUNT(*)-1 FROM reports   -- subtract one to exclude the employee itself
  ) AS count_of_all_reports
FROM employees AS emp;

Explanation of the steps:

Evaluate the scalar subquery (lines 2‑12) that builds the CTE reports by recursively finding all direct and indirect reports of the current employee.

Count the rows produced by the CTE (line 13) and subtract one to exclude the employee row itself.

Return the count as count_of_all_reports for each employee.

CTE stands for Common Table Expression and is commonly used to construct complex queries.

Result set:

+------+---------+------------+----------------------+
| id   | name    | manager_id | count_of_all_reports |
+------+---------+------------+----------------------+
|  29 | Pedro   |        198 |                    2 |
|  72 | Pierre  |         29 |                    0 |
| 123 | Adil    |        692 |                    0 |
| 198 | John    |        333 |                    3 |
| 333 | Yasmina |       NULL |                    6 |
| 692 | Tarek   |        333 |                    1 |
|4610 | Sarah   |         29 |                    0 |
+------+---------+------------+----------------------+
7 rows in set (0.02 sec)

The CTE works by starting with SELECT emp.id (the current employee) and recursively joining the employees table on manager_id to walk up the hierarchy. The arrow from the outer reference to the inner scalar subquery shows that the subquery depends on the outer row, which is why it is not a simple lateral reference.

Before MySQL 8.0.14 this behavior was impossible because the parser could not resolve emp.id inside the CTE definition. Starting with MySQL 8.0.14 the engine detects the reference and recomputes the scalar subquery (and its CTE) for each row of emp .

EXPLAIN output for the query demonstrates that MySQL treats the scalar subquery as a DEPENDENT SUBQUERY and that the UNION inside the CTE is marked as UNCACHEABLE, meaning it must be evaluated for every outer row:

+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
| id | select_type        | table      | partitions | type | possible_keys | key        | key_len | ref    | rows | filtered | Extra                  |
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+
|  1 | PRIMARY            | emp        | NULL       | ALL  | NULL          | NULL       | NULL    | NULL   |    7 |   100.00 | NULL                   |
|  2 | DEPENDENT SUBQUERY |            | NULL       | ALL  | NULL          | NULL       | NULL    | NULL   |    3 |   100.00 | NULL                   |
|  3 | DEPENDENT DERIVED  | NULL       | NULL       | NULL | NULL          | NULL       | NULL    | NULL   | NULL |     NULL | No tables used         |
|  4 | UNCACHEABLE UNION  | rep        | NULL       | ALL  | NULL          | NULL       | NULL    | NULL   |    2 |   100.00 | Recursive; Using where |
|  4 | UNCACHEABLE UNION  | e          | NULL       | ref  | manager_id    | manager_id | 5       | rep.id |    1 |   100.00 | Using index            |
+----+--------------------+------------+------------+------+---------------+------------+---------+--------+------+----------+------------------------+

MySQL now accepts non‑lateral external references by default when parsing derived table definitions, and if the LATERAL keyword is added it also accepts lateral external references, effectively searching the FROM clause for both kinds of references.

Alternative solutions exist, such as building a single large result set with a recursive CTE that lists every employee‑manager pair and then aggregating, but the approach shown here generates smaller intermediate sets per hierarchy level, making it easier to read.