{"id":4249,"date":"2024-01-15T12:34:48","date_gmt":"2024-01-15T11:34:48","guid":{"rendered":"https:\/\/www.skillup.cloud\/cte-ricorsive-leleganza-della-semplicita-in-sql-3\/"},"modified":"2024-02-12T09:38:18","modified_gmt":"2024-02-12T08:38:18","slug":"recursive-cte-the-elegance-of-simplicity-in-sql-3","status":"publish","type":"post","link":"https:\/\/www.skillup.cloud\/en\/recursive-cte-the-elegance-of-simplicity-in-sql-3\/","title":{"rendered":"Recursive CTE: The Elegance of Simplicity in SQL \u2013 3"},"content":{"rendered":"\n<p class=\"has-large-font-size\"><strong>Introduction<\/strong><\/p>\n\n\n\n<p>As we have already seen in other Articles, Common Table Expressions (CTE) in SQL on IBM iSeries (AS\/400), but also present in many other versions on other non-IBM systems, represent a powerful and flexible tool for creating temporary queries and reusable, simplifying and improving the readability of complex SQL queries.<\/p>\n\n\n\n<p>Below are the articles in the CTE series (including this current one):<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <\/span><a href=\"https:\/\/www.skillup.cloud\/en\/iseries-sql-cte-common-table-expressions-definitions-and-usage\/\" target=\"_blank\" rel=\"noreferrer noopener\">iSeries Sql &#8211; CTE Common Table Expressions &#8211; definitions and usage<\/a><span style=\"text-decoration: underline\"> <\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <\/span><a href=\"https:\/\/www.skillup.cloud\/en\/recursive-cte-the-elegance-of-simplicity-in-sql-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">Recursive CTE: The Elegance of Simplicity in SQL &#8211; 1<\/a><span style=\"text-decoration: underline\"> <\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <\/span><a href=\"https:\/\/www.skillup.cloud\/en\/recursive-cte-the-elegance-of-simplicity-in-sql-2\/\" target=\"_blank\" rel=\"noreferrer noopener\">Recursive CTE: The Elegance of Simplicity in SQL \u2013 2<\/a><span style=\"text-decoration: underline\"> <\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <\/span><a href=\"https:\/\/www.skillup.cloud\/en\/recursive-cte-the-elegance-of-simplicity-in-sql-3\/\" target=\"_blank\" rel=\"noreferrer noopener\">Recursive CTE: The Elegance of Simplicity in SQL \u2013 3<\/a><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <\/span><a href=\"https:\/\/www.skillup.cloud\/en\/recursive-cte-how-to-concatenate-the-results-onto-a-single-line\/\" target=\"_blank\" rel=\"noreferrer noopener\">Recursive CTE how to concatenate the results onto a single line<\/a><span style=\"text-decoration: underline\"> <\/span><\/li>\n<\/ul>\n\n\n\n<p>In particular, <strong>Recursive<\/strong> Common Table Expressions (CTE) are a powerful tool in SQL that allows you <strong>to&nbsp;perform operations which, in the absence of this functionality, would require the development of specific programs or the use of complex loops<\/strong>.<\/p>\n\n\n\n<p>In this Article we will see a further extension of the example <strong><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/it\/cte-ricorsive-leleganza-della-semplicita-in-sql-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">CTE Ricorsive: L\u2019Eleganza della Semplicit\u00e0 in SQL \u2013 1<\/a> <\/span><\/strong>and we will see how a SQL command using Recursive CTE can give results that would normally require many lines of code if solved with a traditional programmatic approach.<\/p>\n\n\n\n<p>It must be said that the use of recursive CTE is not trivial and must be approached carefully but if it is understood the approach can provide concise and elegant solutions to solve even complex problems.<\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Description Scenario and objective<\/strong><\/p>\n\n\n\n<p>Let&#8217;s imagine a network diagram organized in a tree structure, where both the nodes and the dependency relationships that exist between them are illustrated.<\/p>\n\n\n\n<p>These nodes can symbolize various elements, such as the departments of a company, the employees of an organization, or the nodes of an electronic circuit, where the dependency relationships between different processors are delineated, and so on.<\/p>\n\n\n\n<p>Let&#8217;s assume we want to compile a list of all the unique paths that start from the root and reach the leaves in the node tree, also including those &#8216;isolated&#8217; nodes, i.e. those without dependencies or connections with other nodes.<\/p>\n\n\n\n<p>Below is a diagram of the tree structure:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large is-resized\"><img decoding=\"async\" src=\"https:\/\/www.skillup.cloud\/wp-content\/uploads\/2024\/01\/tree_structure.png\" alt=\"\" style=\"width:656px;height:auto\"\/><\/figure><\/div>\n\n\n<p>In this structure there are &#8216;isolated&#8217; nodes, i.e. without ties, and nodes with one or more ties.<\/p>\n\n\n\n<p>The result we want to obtain is the list of all unique paths (that are not included in other paths) that start from a root (node \u200b\u200bwithout a &#8216;father&#8217;) and reach the terminal leaves (nodes without &#8216;children&#8217;) in the tree of nodes, also including those &#8216;isolated&#8217; nodes, i.e. without dependencies or connections with other nodes.<\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Query<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH -- starting data\n     TreeStructure (NodeID, Description, ParentNodeID) AS (\n         VALUES  ('I011', 'Description_011',  NULL) , ('I012', 'Description_012',  NULL)\n               , ('I013', 'Description_013',  NULL) , ('I021', 'Description_021', 'I013')\n               , ('I022', 'Description_022', 'I013'), ('I023', 'Description_023', 'I013')\n               , ('I024', 'Description_024', 'I013'), ('I031', 'Description_031', 'I022')\n               , ('I032', 'Description_032', 'I022'), ('I041', 'Description_041', 'I031')\n               , ('I042', 'Description_042', 'I031'), ('I043', 'Description_043', 'I031')\n               , ('I044', 'Description_044', 'I032'), ('I045', 'Description_045', 'I032')\n               , ('I046', 'Description_046', 'I032'), ('I051', 'Description_051', 'I042')\n               , ('I052', 'Description_052', 'I046'), ('I061', 'Description_061', 'I051')\n               , ('I062', 'Description_062', 'I051'), ('I063', 'Description_063', 'I051')\n               , ('I064', 'Description_064', 'I052'), ('I065', 'Description_065', 'I052')\n               , ('I066', 'Description_066', 'I052'), ('I033', 'Description_033', 'I023')\n               , ('I034', 'Description_034', 'I024'), ('I035', 'Description_035', 'I024')\n)\n, Routes(NodeID, Path) AS (\n    -- Parte ancorata: seleziona i nodi radice\n    SELECT NodeID, CAST(NodeID AS VARCHAR(1024))\n    FROM TreeStructure\n    WHERE ParentNodeID IS NULL\n    UNION ALL\n    -- Parte ricorsiva: aggiungi il nodo corrente al Path\n    SELECT nd.NodeID, p.Path concat '-&gt;' concat nd.NodeID\n    FROM TreeStructure nd\n    JOIN Routes p ON nd.ParentNodeID = p.NodeID\n)\n, Routes2(NodeID, Path) AS (\n    SELECT NodeID, SUBSTR(Path, 1, length(Path)-6)\n      FROM Routes\n      WHERE LENGTH(Path) &gt; 6\n    UNION ALL \n    SELECT NodeID, Path\n      FROM Routes\n      WHERE LENGTH(Path) &lt;= 6\n)\n, Routes3(NodeID, Path) AS (\n    SELECT * FROM Routes P1\n     WHERE NOT EXISTS (\n           SELECT 1 from Routes2 P2\n            WHERE P2.Path = P1.Path)\n      ORDER BY 2\n) --  SELECT * FROM Routes3;\n SELECT * FROM Routes3\n  UNION ALL\n SELECT NodeID, NodeID\n   FROM TreeStructure N1\n  WHERE NOT EXISTS (\n            SELECT 1 FROM TreeStructure N2\n              WHERE N1.NodeID = N2.ParentNodeID)\n    AND N1.ParentNodeID IS NULL\n   ORDER BY 2;<\/pre>\n\n\n\n<p class=\"has-large-font-size\"><strong>Query result<\/strong><\/p>\n\n\n\n<div class=\"wp-block-columns\">\n<div class=\"wp-block-column\">\n<figure class=\"wp-block-table\"><table><tbody><tr><td>NODEID<\/td><td>PATH<\/td><\/tr><tr><td>I011<\/td><td>I011<\/td><\/tr><tr><td>I012<\/td><td>I012<\/td><\/tr><tr><td>I021<\/td><td>I013-&gt;I021<\/td><\/tr><tr><td>I041<\/td><td>I013-&gt;I022-&gt;I031-&gt;I041<\/td><\/tr><tr><td>I061<\/td><td>I013-&gt;I022-&gt;I031-&gt;I042-&gt;I051-&gt;I061<\/td><\/tr><tr><td>I062<\/td><td>I013-&gt;I022-&gt;I031-&gt;I042-&gt;I051-&gt;I062<\/td><\/tr><tr><td>I063<\/td><td>I013-&gt;I022-&gt;I031-&gt;I042-&gt;I051-&gt;I063<\/td><\/tr><tr><td>I043<\/td><td>I013-&gt;I022-&gt;I031-&gt;I043<\/td><\/tr><tr><td>I044<\/td><td>I013-&gt;I022-&gt;I032-&gt;I044<\/td><\/tr><tr><td>I045<\/td><td>I013-&gt;I022-&gt;I032-&gt;I045<\/td><\/tr><tr><td>I064<\/td><td>I013-&gt;I022-&gt;I032-&gt;I046-&gt;I052-&gt;I064<\/td><\/tr><tr><td>I065<\/td><td>I013-&gt;I022-&gt;I032-&gt;I046-&gt;I052-&gt;I065<\/td><\/tr><tr><td>I066<\/td><td>I013-&gt;I022-&gt;I032-&gt;I046-&gt;I052-&gt;I066<\/td><\/tr><tr><td>I033<\/td><td>I013-&gt;I023-&gt;I033<\/td><\/tr><tr><td>I034<\/td><td>I013-&gt;I024-&gt;I034<\/td><\/tr><tr><td>I035<\/td><td>I013-&gt;I024-&gt;I035<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<div class=\"wp-block-column\"><\/div>\n<\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Detailed description of the query<\/strong><\/p>\n\n\n\n<p>The SQL query described is an example of a recursive Common Table Expression (CTE) used to process a tree structure. The query is split into multiple parts, each of which builds on the previous one to arrive at the final result.<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>1) Definition of the Tree Structure:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This initial CTE, <strong>TreeStructure<\/strong>, defines the starting data, representing the tree of nodes. Each node is defined by a unique <strong>NodeID<\/strong>, a <strong>Description<\/strong>, and a <strong>ParentNodeID<\/strong> indicating the parent node (if it exists; if <strong>NULL<\/strong>, the node is a root).<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>2) Generation of Routes:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <strong>Routes<\/strong> CTE starts with the &#8216;anchored&#8217; part that selects all root nodes (nodes without a parent node).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Then, through the &#8216;recursive&#8217; part, the query expands by progressively adding the child nodes to the paths, concatenating the current <strong>NodeID<\/strong> to the <strong>Path<\/strong> of the parent node.<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>3) Cleaning of Routes (Routes2)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <strong>Routes2<\/strong> CTE is an intermediate table that is used to identify any routes that are included in other routes (sub-routes). In practice, in order to identify the sub-routes, this intermediate table is created as a copy of the Routes table in which the Path column is modified by removing the last node (thus creating sub-routes to compare them).<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>4) <strong>Selection of Single Routes (Routes3)<\/strong> <\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Routes3<\/strong> selects the final routes by excluding those that are sub-routes of other existing routes, ensuring that each route is unique and is not included in another longer route.<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>5) <strong>Final results<\/strong><\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Finally, the query selects all unique routes from the latest CTE (<strong>Routes3<\/strong>).<\/li>\n\n\n\n<li>It also adds &#8216;isolated&#8217; nodes, which are root nodes with no children, using a <strong>UNION<\/strong> <strong>ALL<\/strong> to join these nodes to the path list. Isolated nodes are identified by comparing <strong>TreeStructure<\/strong> with itself to find nodes that are not parents of other nodes (i.e., have no children) and that do not have a parent node (they are root nodes).<\/li>\n\n\n\n<li>The final part of the query sorts all the results based on the Path column to present the list in a readable order.<\/li>\n<\/ul>\n\n\n\n<p>In summary, the query is a sophisticated way of transforming a tree structure represented in a database table into a list of unique paths from every root node to every leaf node, including isolated nodes.<\/p>\n\n\n\n<p>This technique is often used in databases that support recursive CTE to manipulate hierarchical data or derive information from complex tree structures.<\/p>\n\n\n\n<p>To run the script you need to make sure that the specific syntax (<strong>concat<\/strong>, <strong>SUBSTR<\/strong>, <strong>LENGTH<\/strong>) is supported by the DBMS.<\/p>\n\n\n\n<p>IBM iSeries uses a slightly different syntax than other SQL systems for some functions. As always, keep in mind that actual implementation and functionality may vary depending on the version of the iSeries operating system and DB2 database. It is always good practice to refer to the official IBM documentation for more precise and detailed information.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction As we have already seen in other Articles, Common Table Expressions (CTE) in SQL on IBM iSeries (AS\/400), but also present in many other versions on other non-IBM systems, represent a powerful and flexible tool for creating temporary queries&#8230;<\/p>\n","protected":false},"author":3,"featured_media":4186,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[137,141,143],"tags":[46,161,162,231],"class_list":["post-4249","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-as400-architecture-en","category-manage-as400-with-sql","category-more-in-depth","tag-sql","tag-cte-common-table-expressions-en","tag-recursive-cte","tag-tree-structure-en"],"_links":{"self":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/comments?post=4249"}],"version-history":[{"count":6,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4249\/revisions"}],"predecessor-version":[{"id":4390,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/posts\/4249\/revisions\/4390"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/media\/4186"}],"wp:attachment":[{"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/media?parent=4249"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/categories?post=4249"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skillup.cloud\/en\/wp-json\/wp\/v2\/tags?post=4249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}