{"id":55,"date":"2025-09-06T08:57:00","date_gmt":"2025-09-06T06:57:00","guid":{"rendered":"https:\/\/extendsclass.com\/blog\/?p=55"},"modified":"2023-05-05T21:41:42","modified_gmt":"2023-05-05T19:41:42","slug":"recursive-sql-with-cte","status":"publish","type":"post","link":"https:\/\/extendsclass.com\/blog\/recursive-sql-with-cte","title":{"rendered":"Recursive SQL with CTE (common table expressions)"},"content":{"rendered":"\n<p>A <strong>recursive common table expression <\/strong>is a powerful SQL construct that allows you to perform complex data manipulation and retrieval. It is particularly useful for working with <strong>hierarchical <\/strong>or<strong> tree-structured data<\/strong>.<\/p>\n\n\n\n<p>With recursive CTE, the query can refer to its own output.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_47_1 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"ez-toc-toggle-icon-1\"><label for=\"item-69daafdc14d26\" aria-label=\"Table of Content\"><span style=\"display: flex;align-items: center;width: 35px;height: 30px;justify-content: center;direction:ltr;\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/label><input  type=\"checkbox\" id=\"item-69daafdc14d26\"><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/extendsclass.com\/blog\/recursive-sql-with-cte\/#The_basis_CTE_non_recursive\" title=\"The basis: CTE (non recursive)\">The basis: CTE (non recursive)<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/extendsclass.com\/blog\/recursive-sql-with-cte\/#CTE_recursive_%E2%80%93_Template\" title=\"CTE recursive &#8211; Template\">CTE recursive &#8211; Template<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/extendsclass.com\/blog\/recursive-sql-with-cte\/#Basic_example\" title=\"Basic example\">Basic example<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/extendsclass.com\/blog\/recursive-sql-with-cte\/#More_complex_example\" title=\"More complex example\">More complex example<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/extendsclass.com\/blog\/recursive-sql-with-cte\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"The_basis_CTE_non_recursive\"><\/span>The basis: CTE (non recursive)<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH my_cte AS (\n    SELECT column1, column2\n    FROM my_table\n)\nSELECT *\nFROM my_cte;<\/code><\/pre>\n\n\n\n<p>The WITH clause allows the creation of <strong>auxiliary statements<\/strong>, to be used within a larger query. <\/p>\n\n\n\n<p>These CTEs essentially define <strong>temporary tables<\/strong> that are only valid for the duration of a single query. <\/p>\n\n\n\n<p>The WITH clause can contain one or more auxiliary statements, which can be a SELECT, INSERT, UPDATE, or DELETE statement. <\/p>\n\n\n\n<p>The WITH clause is then attached to a primary statement, which can also be a SELECT, INSERT, UPDATE, or DELETE statement.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"CTE_recursive_%E2%80%93_Template\"><\/span>CTE recursive &#8211; Template<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH RECURSIVE expression_name (column_list)\nAS\n(\n    -- Anchor member\n    initial query  \n    UNION ALL\n    -- Recursive member \n    recursive query  \n)\nSELECT *\nFROM   expression_name<\/code><\/pre>\n\n\n\n<p>A recursive CTE consists of two parts: <\/p>\n\n\n\n<ul>\n<li>An anchor member:<br>The base result set of the CTE<\/li>\n\n\n\n<li>A recursive member:<br>References the CTE itself and builds upon the result set of the anchor member.<br>The recursive member is executed repeatedly until it returns an empty result set. At each iteration, the result set of the previous iteration is used as input for the next iteration.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Basic_example\"><\/span>Basic example<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The sum of the first 10 numbers:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH RECURSIVE nb (n) AS (\n    VALUES (1)\n  UNION \n    SELECT n+1 FROM nb WHERE n &lt; 10\n)\nSELECT sum(n) FROM nb;<\/code><\/pre>\n\n\n\n<p>Explanations:<\/p>\n\n\n\n<ul>\n<li><strong>nb <\/strong>: CTE name<\/li>\n\n\n\n<li><strong>VALUES (1)<\/strong><br>The base result set, contains only the value 1.<\/li>\n\n\n\n<li><strong>SELECT n+1 FROM nb WHERE n &lt; 10<\/strong><br>The recursive query, which references the CTE itself.<br>At the first iteration, the result set contains the values 1 and 2.<br>At the second iteration, the result set contains the values 1, 2 and 3.<br>&#8230;<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"More_complex_example\"><\/span>More complex example<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We have a &#8220;relationship&#8221; table which contains the relationship parent \/ child.<\/p>\n\n\n\n<p>Get all ancestors of &#8220;andr\u00e9&#8221;:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH RECURSIVE descent (child) AS (\n    select child from relationship where parent = 'andr\u00e9'\n  UNION \n    SELECT relationship.child FROM descent join relationship on descent.child = relationship.parent \n)\nSELECT * FROM descent;<\/code><\/pre>\n\n\n\n<p>Explanations:<\/p>\n\n\n\n<ul>\n<li><strong>select child from relationship where parent = &#8216;andr\u00e9&#8217;<\/strong>:<br>Select Andr\u00e9&#8217;s children<\/li>\n\n\n\n<li><strong>SELECT relationship.child FROM descent join relationship on descent.child = relationship.parent <\/strong><br>Recursively selects the children of the selection.<\/li>\n<\/ul>\n\n\n\n<p>You can use our MySQL sandbox to test yourself: <a href=\"https:\/\/extendsclass.com\/mysql\/ddb8ac2\" title=\"\">Mysql Playground<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p><strong>Recursive CTEs<\/strong> are quite<strong> easy to write<\/strong>, yet they can yield <strong>powerful queries<\/strong>. They allow you to avoid having to write recursive code that executes <strong>multiple SQL queries<\/strong>, instead of just writing a simple,<strong> readable SQL query<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recursive CTEs are powerful auxiliary statements that allow for traversing hierarchical or tree-structured data.<\/p>\n","protected":false},"author":1,"featured_media":70,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_sitemap_exclude":false,"_sitemap_priority":"","_sitemap_frequency":""},"categories":[2],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts\/55"}],"collection":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/comments?post=55"}],"version-history":[{"count":10,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts\/55\/revisions"}],"predecessor-version":[{"id":68,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/posts\/55\/revisions\/68"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/media\/70"}],"wp:attachment":[{"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/media?parent=55"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/categories?post=55"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/extendsclass.com\/blog\/wp-json\/wp\/v2\/tags?post=55"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}