MariaDB starting with 10.2.2

Recursive Common Table Expressions have been supported since MariaDB 10.2.2.

Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. CTEs first appeared in the SQL standard in 1999, and the first implementations began appearing in 2007.

There are two kinds of CTEs:

SQL is generally poor at recursive structures.


CTEs permit a query to reference itself. A recursive CTE will repeatedly execute subsets of the data until it obtains the complete result set. This makes it particularly useful for handing hierarchical or tree-structured data.

Syntax example

WITH RECURSIVE signifies a recursive CTE. It is given a name, followed by a body (the main query) as follows:




Given the following structure: rcte_computation

First execute the anchor part of the query: rcte1

Next, execute the recursive part of the query: rcte_computation_2





Summary so far

with recursive R as (
  select anchor_data
  union [all]
  select recursive_part
  from R, ...
select ...
  1. Compute anchor_data
  2. Compute recursive_part to get the new data
  3. if (new data is non-empty) goto 2;


Transitive closure - determining bus destinations

Sample data:


CREATE TABLE bus_routes (origin varchar(50), dst varchar(50));
  ('New York', 'Boston'), 
  ('Boston', 'New York'), 
  ('New York', 'Washington'), 
  ('Washington', 'Boston'), 
  ('Washington', 'Raleigh');

Now, we want to return the bus destinations with New York as the origin:

WITH RECURSIVE bus_dst as ( 
    SELECT origin as dst FROM bus_routes WHERE origin='New York' 
    SELECT bus_routes.dst FROM bus_routes, bus_dst WHERE bus_dst.dst= bus_routes.origin 
SELECT * FROM bus_dst;
| dst        |
| New York   |
| Boston     |
| Washington |
| Raleigh    |

The above example is computed as follows:

First, the anchor data is calculated:

  • Starting from New York
  • Boston and Washington are added

Next, the recursive part:

  • Starting from Boston and then Washington
  • Raleigh is added
  • UNION excludes nodes that are already present.

Computing paths - determining bus routes

This time, we are trying to get bus routes such as “New York -> Washington -> Raleigh”.

Using the same sample data as the previous example:

WITH RECURSIVE paths (cur_path, cur_dest) AS (
    SELECT origin, origin FROM bus_routes WHERE origin='New York' 
    SELECT CONCAT(paths.cur_path, ',', bus_routes.dst), bus_routes.dst 
      FROM paths, bus_routes 
      WHERE paths.cur_dest = bus_routes.origin AND 
      LOCATE(bus_routes.dst, paths.cur_path)=0 
SELECT * FROM paths;
| cur_path                    | cur_dest   |
| New York                    | New York   |
| New York,Boston             | Boston     |
| New York,Washington         | Washington |
| New York,Washington,Boston  | Boston     |
| New York,Washington,Raleigh | Raleigh    |


Comments loading...