{"id":1193,"date":"2020-04-08T17:00:07","date_gmt":"2020-04-08T05:00:07","guid":{"rendered":"https:\/\/www.ronella.xyz\/?p=1193"},"modified":"2020-04-08T17:23:42","modified_gmt":"2020-04-08T05:23:42","slug":"with-clause","status":"publish","type":"post","link":"https:\/\/www.ronella.xyz\/?p=1193","title":{"rendered":"Common Table Expression (CTE) &#8211; With Clause"},"content":{"rendered":"<p>The <strong>with clause<\/strong> is also known as<strong> common table expression (CTE)<\/strong> and <strong>subquery refactory<\/strong>. It is a temporary named result set.<\/p>\n<blockquote><p>SQL:1999 added the with clause to define \"statement scoped views\". They are not stored in the database scheme: instead, they are only valid in the query they belong to. This makes it possible to improve the structure of a statement without polluting the global namespace.<\/p><\/blockquote>\n<h1>Syntax<\/h1>\n<pre style=\"white-space: pre;\">with &lt;QUERY_NAME_1&gt; (&lt;COLUMN_1&gt;[, &lt;COLUMN_2&gt;][, &lt;COLUMN_N&gt;]) as\r\n     (&lt;INNER_SELECT_STATEMENT&gt;)\r\n[,&lt;QUERY_NAME_2&gt; (&lt;COLUMN_1&gt;[, &lt;COLUMN_2&gt;][, &lt;COLUMN_N&gt;]) as\r\n     (&lt;INNER_SELECT_STATEMENT&gt;)]\r\n&lt;SELECT_STATEMENT&gt;\r\n<\/pre>\n<h1>Non-Recursive Example<\/h1>\n<pre style=\"white-space: pre;\">with sales_tbl as (\r\nselect sales.*\r\n\tfrom (VALUES\r\n\t\t('Spiderman',1,19750),\r\n\t\t('Batman',1,19746),\r\n\t\t('Superman',1,9227),\r\n\t\t('Iron Man',1,9227),\r\n\t\t('Wonder Woman',2,16243),\r\n\t\t('Kikkoman',2,17233),\r\n\t\t('Cat Woman',2,8308),\r\n\t\t('Ant Man',3,19427),\r\n\t\t('Aquaman',3,16369),\r\n\t\t('Iceman',3,9309)\r\n\t) sales (emp_name,dealer_id,sales)\r\n)\r\nselect ROW_NUMBER() over (order by dealer_id) as rownumber, *\r\nfrom sales_tbl\r\n<\/pre>\n<h1>Recursive Example<\/h1>\n<pre style=\"white-space: pre;\">WITH [counter] AS (\r\n\r\n   SELECT 1 AS n  -- Executes first and only once.\r\n\r\n   UNION ALL      -- UNION ALL must be used.\r\n\r\n   SELECT n + 1   -- The portion that will be executed \r\n   FROM [counter] -- repeatedly until there's no row \r\n                  -- to return.\r\n\r\n   WHERE  n &lt; 50  -- Ensures that the query stops.\r\n)\r\nSELECT n FROM [counter]\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The with clause is also known as common table expression (CTE) and subquery refactory. It is a temporary named result set. SQL:1999 added the with clause to define &#8220;statement scoped views&#8221;. They are not stored in the database scheme: instead, they are only valid in the query they belong to. This makes it possible to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[58,46],"tags":[],"_links":{"self":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts\/1193"}],"collection":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1193"}],"version-history":[{"count":4,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts\/1193\/revisions"}],"predecessor-version":[{"id":1197,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=\/wp\/v2\/posts\/1193\/revisions\/1197"}],"wp:attachment":[{"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ronella.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}