Description\n-----------\n\nCommon Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. They are defined using the WITH clause and can be referenced like tables within the main query. CTEs first appeared in the SQL standard in 1999, and the first implementations began appearing in 2007.\n\nMariaDB supports two types of CTEs:\n\n Non-recursive;\n Recursive, which this article covers.\n\nRecursive CTE\n\nA recursive CTE will repeatedly execute subsets of the data until it obtains the complete result set. This makes it particularly useful for handling hierarchical or tree-structured data, such as organizational charts, category trees, or parent-child relationships. max_recursive_iterations avoids infinite loops. CTEs permit a query to reference itself. SQL is generally poor at recursive structures.\n\n!trees_and_graphs\n\nRecursive CTE Syntax\n\nWITH RECURSIVE signifies a recursive CTE. It is given a name, followed by a body (the main query). The body consists of two parts:\n\n An anchor member (the initial query)\n A recursive member (which references the CTE itself).\n\n!rcte_syntax\n\nComputation\n\nGiven the following structure:\n\n!rcte_computation\n\n1. First execute the anchor part of the query:\n\n!rcte1\n\n2. Next, execute the recursive part of the query:\n\n!rcte_computation_2\n\n![](../../../../../.gitbook/assets/rcte_computation_2b.png)\n\n![](../../../../../.gitbook/assets/rcte_computation_3.png)\n\n![](../../../../../.gitbook/assets/rcte_computation_3b.png)\n\n![](../../../../../.gitbook/assets/rcte_computation_4.png)\n\nSummary\n\n``sql\nWITH RECURSIVE R AS (\n  SELECT anchor_data\n  UNION [all]\n  SELECT recursive_part\n  FROM R, ...\n)\nSELECT ...\n`\n\n1. Compute anchor_data.\n2. Compute recursive_part to get the new data.\n3. If (new data is non-empty) go to 2.\n\nRecursive CTE Examples\n\nTransitive closure - determining bus destinations\n\nSample data:\n\n!tc_1\n\n`sql\nCREATE TABLE bus_routes (origin VARCHAR(50), dst VARCHAR(50));\nINSERT INTO bus_routes VALUES \n  ('New York', 'Boston'), \n  ('Boston', 'New York'), \n  ('New York', 'Washington'), \n  ('Washington', 'Boston'), \n  ('Washington', 'Raleigh');\n`\n\nNow, we want to return the bus destinations with New York as the origin:\n\n`sql\nWITH RECURSIVE bus_dst as ( \n    SELECT origin as dst FROM bus_routes WHERE origin='New York' \n  UNION\n    SELECT bus_routes.dst FROM bus_routes JOIN bus_dst ON bus_dst.dst= bus_routes.origin \n) \nSELECT  FROM bus_dst;\n+------------+\n| dst        |\n+------------+\n| New York   |\n| Boston     |\n| Washington |\n| Raleigh    |\n+------------+\n`\n\nThe above example is computed as follows:\n\nFirst, the anchor data is calculated:\n\n Starting from New York.\n Boston and Washington are added.\n\nNext, the recursive part:\n\n Starting from Boston and then Washington.\n Raleigh is added.\n UNION excludes nodes that are already present.\n\nComputing paths - determining bus routes\n\nThis time, we are trying to get bus routes such as “New York -> Washington -> Raleigh”.\n\nUsing the same sample data as the previous example:\n\n`sql\nWITH RECURSIVE paths (cur_path, cur_dest) AS (\n    SELECT origin, origin FROM bus_routes WHERE origin='New York' \n  UNION\n    SELECT CONCAT(paths.cur_path, ',', bus_routes.dst), bus_routes.dst \n     FROM paths\n     JOIN bus_routes \n       ON paths.cur_dest = bus_routes.origin AND \n         NOT FIND_IN_SET(bus_routes.dst, paths.cur_path)\n) \nSELECT * FROM paths;\n+-----------------------------+------------+\n| cur_path                    | cur_dest   |\n+-----------------------------+------------+\n| New York                    | New York   |\n| New York,Boston             | Boston     |\n| New York,Washington         | Washington |\n| New York,Washington,Boston  | Boston     |\n| New York,Washington,Raleigh | Raleigh    |\n+-----------------------------+------------+\n``\n\nType Resolution and Data Truncation\n\nIn previous versions of MariaDB, the data type of a Recursive CTE column was entirely determined by the Anchor (non-recursive) part. If the Recursive part carried out calculations that generated a greater value, the data was silently truncated, leading to incorrect results.\n\nStarting with the versions listed below, MariaDB prevents silent data loss. The server now validates that the data generated by the recursive part matches the column types given by the anchor part. If a mismatch that leads to truncation is detected, the query will now fail with an error. See MDEV-12325 for more information.\n\n<table><thead><tr><th width="296">MariaDB Series </th><th>Fixed Version</th></tr></thead><tbody><tr><td>10.3</td><td>10.3.36 </td></tr><tr><td>10.4</td><td>10.4.26</td></tr><tr><td>10.5 </td><td>10.5.17</td></tr><tr><td>10.6</td><td>10.6.9</td></tr><tr><td>10.7</td><td>10.7.5</td></tr></tbody></table>\n\nExamples\n--------\n\nCREATE TABLE t1\n(\n  id INT,     -- employee ID\n  mid INT,    -- manager ID, or NULL if top level employee\n  name TEXT   -- employee name\n);\nINSERT INTO t1 VALUES (0,NULL, 'Name');\nINSERT INTO t1 VALUES (1,0,    'Name1');\nINSERT INTO t1 VALUES (2,0,    'Name2');\nINSERT INTO t1 VALUES (11,1,   'Name11');\nINSERT INTO t1 VALUES (12,1,   'Name12');\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/recursive-common-table-expressions-overview