{"id":10086,"date":"2023-01-06T07:00:00","date_gmt":"2023-01-06T01:30:00","guid":{"rendered":"https:\/\/selfimagination.in\/tips\/?p=10086"},"modified":"2023-01-05T20:14:20","modified_gmt":"2023-01-05T14:44:20","slug":"cte-common-table-expression-loop-in-sql","status":"publish","type":"post","link":"https:\/\/selfimagination.in\/tips\/cte-common-table-expression-loop-in-sql\/","title":{"rendered":"CTE &#8211; Common Table Expression (Loop in SQL)"},"content":{"rendered":"\n<p>common table expression, sql cte common table expression, common table expressions, sql cte (common table expression), sql common table expression, common table expression in sql, mysql common table expression, common table expression in mysql, common table expression syntax, common table expression in database, how to use common table expressions<\/p>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table of Contents<\/h2><nav><ul><li><a href=\"#what-is-cte-\u0915\u094d\u092f\u093e-\u0939\u0948\u0902\">What is CTE \u0915\u094d\u092f\u093e \u0939\u0948\u0902 ? <\/a><\/li><li><a href=\"#cte-\u0915\u0948\u0938\u0947-\u0915\u093e\u0930\u094d\u092f-\u0915\u0930\u0924\u093e-\u0939\u0948\u0902\">CTE \u0915\u0948\u0938\u0947 \u0915\u093e\u0930\u094d\u092f \u0915\u0930\u0924\u093e \u0939\u0948\u0902 ? <\/a><\/li><li><a href=\"#cte-\u092e\u0947-with-recursive-clause-\u0915\u094d\u092f\u093e-\u0939\u094b\u0924\u093e-\u0939\u0948\u0902\">CTE \u092e\u0947 With Recursive Clause \u0915\u094d\u092f\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902?<\/a><\/li><li><a href=\"#cte-with-recursive-example\">CTE With Recursive Example<\/a><\/li><li><a href=\"#strong-\u0939\u092e\u093e\u0930\u0947-\u0905\u0928\u094d\u092f-\u0906\u0930\u094d\u091f\u093f\u0915\u0932-strong\">\u0939\u092e\u093e\u0930\u0947 \u0905\u0928\u094d\u092f \u0906\u0930\u094d\u091f\u093f\u0915\u0932<\/a><\/li><\/ul><\/nav><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-is-cte-\u0915\u094d\u092f\u093e-\u0939\u0948\u0902\">What is CTE \u0915\u094d\u092f\u093e \u0939\u0948\u0902 ? <\/h2>\n\n\n\n<p>CTE \u0915\u093e \u092a\u0942\u0930\u093e \u0928\u093e\u092e Common Table Expression \u0939\u0948\u0902, \u092f\u0939 SQL \u0915\u093e \u090f\u0915 \u092c\u0939\u0941\u0924 \u0939\u0940 \u092c\u095d\u093f\u092f\u093e Construct \u0939\u0948\u0902, \u091c\u094b \u0915\u0940 Virtual Table \u0915\u0940 \u0924\u0930\u0939 \u0915\u093e\u0930\u094d\u092f \u0915\u0930\u0924\u093e \u0939\u0948\u0902, \u092f\u0939 \u0915\u093e\u0930\u094d\u092f \u0915\u094b \u092c\u0939\u0941\u0924 \u0939\u0940 \u0906\u0938\u093e\u0928 \u092c\u0928\u093e \u0926\u0947\u0924\u093e \u0939\u0948\u0902 \u0964 \u0907\u0938\u0915\u093e \u0909\u092a\u092f\u094b\u0917 MySQL \u092e\u0947 WITH Clause \u0915\u0947 \u0938\u093e\u0925 \u0915\u093f\u092f\u093e \u091c\u093e\u0924\u093e \u0939\u0948\u0902\u0964 <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"cte-\u0915\u0948\u0938\u0947-\u0915\u093e\u0930\u094d\u092f-\u0915\u0930\u0924\u093e-\u0939\u0948\u0902\">CTE \u0915\u0948\u0938\u0947 \u0915\u093e\u0930\u094d\u092f \u0915\u0930\u0924\u093e \u0939\u0948\u0902 ? <\/h2>\n\n\n\n<p>SQL \u0932\u093f\u0916\u0928\u0947 \u0915\u0947 \u092a\u0939\u0932\u0947 \u0939\u092e With Clause \u0915\u0947 \u0938\u093e\u0925 \u0907\u0938\u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930\u0924\u0947 \u0939\u0948\u0902, \u092f\u0939 SQL \u091a\u0932\u0928\u0947 \u0915\u0947 \u092a\u0939\u0932\u0947 Execute \u0939\u094b\u0924\u093e \u0939\u0948\u0902, \u0907\u0938\u0915\u0947 Result \u0915\u094b \u0939\u092e Query \u092e\u0947 \u0909\u092a\u092f\u094b\u0917 \u0915\u0930\u0924\u0947 \u0939\u0948\u0902, Query \u0915\u093e Result \u0906\u0928\u0947 \u092a\u0930 \u092f\u0939 \u0938\u094d\u0935\u0924\u0903 \u0939\u0940 End \u0939\u094b \u091c\u093e\u0924\u093e \u0939\u0948\u0902\u0964 \u092f\u0939 Virtual Table \u0915\u0908 \u0924\u0930\u0939 Records \u0915\u094b Hold \u0915\u0930 \u0932\u0947\u0924\u093e \u0939\u0948\u0902\u0964 <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"cte-\u092e\u0947-with-recursive-clause-\u0915\u094d\u092f\u093e-\u0939\u094b\u0924\u093e-\u0939\u0948\u0902\">CTE \u092e\u0947 With Recursive Clause \u0915\u094d\u092f\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902?<\/h2>\n\n\n\n<p>\u091c\u092c \u092d\u0940 \u0939\u092e\u0947 Looping \u0915\u093e \u0915\u093e\u0930\u094d\u092f \u0915\u0930\u0928\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0924\u092c \u0939\u092e With Recursive \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930\u0924\u0947 \u0939\u0948\u0902\u0964 Recursive \u0915\u093e \u092e\u0924\u0932\u092c \u092f\u0939 \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0915\u0908 \u092f\u0939 \u090f\u0915 \u0910\u0938\u093e Construct \u0939\u0948\u0902 \u091c\u094b \u0916\u0941\u0926 \u0915\u094b \u0939\u0940 Call \u0915\u0930\u0924\u093e \u0939\u0948\u0902 \u0964  \u0939\u092e\u0947 \u0905\u092a\u0928\u0947 logic \u0932\u0917\u093e \u0915\u0930 Recursion \u0938\u0947 \u092c\u093e\u0939\u0930 \u0932\u093e\u0928\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902\u0964 <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"cte-with-recursive-example\">CTE With Recursive Example<\/h2>\n\n\n\n<p>\u0928\u093f\u092e\u094d\u0928 Example \u092e\u0947 \u0939\u092e\u0928\u0947 \u090f\u0915 \u091b\u094b\u091f\u093e \u0938\u093e Loop Logic \u0932\u093f\u0916\u093e \u0939\u0948\u0902 \u091c\u093f\u0938\u0938\u0947 \u0915\u0908 \u0906\u092a\u0915\u094b \u0938\u092e\u091d \u092e\u0947 \u0906\u092f\u0947 \u0915\u0940 \u0915\u093f\u0938 \u0924\u0930\u0939 \u0938\u0947 \u0939\u092e Recursive \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930 Loop \u0932\u093f\u0916 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964 <\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"626\" height=\"328\" src=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=626%2C328&#038;ssl=1\" alt=\"CTE - WITH RECURSIVE\" class=\"wp-image-10135\" srcset=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?w=626&amp;ssl=1 626w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=300%2C157&amp;ssl=1 300w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=220%2C115&amp;ssl=1 220w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=140%2C73&amp;ssl=1 140w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=620%2C324&amp;ssl=1 620w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=60%2C31&amp;ssl=1 60w, https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-4.png?resize=150%2C79&amp;ssl=1 150w\" sizes=\"auto, (max-width: 626px) 100vw, 626px\" \/><\/figure>\n<\/div>\n\n\n<p>\u0926\u0947\u0916\u093f\u090f \u091c\u092c \u092d\u0940 \u0939\u092e \u0915\u094b\u0908 \u092d\u0940 \u0932\u0942\u092a \u0932\u093f\u0916\u0947 \u0939\u0948\u0902 \u0924\u094b \u0939\u092e\u0947 3 Parameter \u0926\u0947\u0928\u093e \u0939\u094b\u0924\u0947 \u0939\u0948 \u0964 <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1. Initialization :<\/strong> \u0938\u092c\u0938\u0947 \u092a\u0939\u0932\u0947 \u0932\u0942\u092a \u0915\u094b \u091a\u0932\u093e\u0928\u0947 \u0915\u0947 \u0932\u093f\u090f \u090f\u0915 variable \u092e\u0947 \u0935\u0948\u0932\u094d\u092f\u0942 \u0915\u094b Initialize \u0915\u093f\u092f\u093e \u091c\u093e\u0924\u093e \u0939\u0948\u0902\u0964 \u092f\u0939\u093e \u092a\u0930 Initialization <strong>SELECT 0<\/strong> \u0938\u0947 \u0915\u093f\u092f\u093e \u0917\u092f\u093e \u0939\u0948\u0902\u0964 <\/li>\n\n\n\n<li><strong>2. Exit Condition :<\/strong> \u0939\u092e\u0947 \u090f\u0915 Condition \u0926\u0947\u0928\u093e  \u0939\u094b\u0924\u0940 \u0939\u0948\u0902 \u091c\u092c \u0924\u0915 Variable \u0909\u0938 Condition \u0915\u094b Satisfy \u0915\u0930\u0924\u093e \u0939\u0948 \u091c\u092c \u0924\u0915 loop \u091a\u0932\u0924\u093e \u0939\u0948\u0902 \u0914\u0930 \u091c\u0948\u0938\u0947 \u0939\u0940 Unsetisfy \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0932\u0942\u092a \u0938\u0947 exit \u0939\u094b \u091c\u093e\u0924\u0947 \u0939\u0948\u0902 \u0935\u0948\u0938\u093e \u0939\u0940 \u0907\u0938\u092e\u0947 <strong>WHERE i&lt;5<\/strong> \u0938\u0947 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u0964 <\/li>\n\n\n\n<li><strong>3. Increment \/ Decrement :<\/strong> variable \u0915\u0908 \u0935\u0948\u0932\u094d\u092f\u0942 \u0915\u094b Increment \u0915\u093f\u092f\u093e \u091c\u093e\u0924\u093e \u0939\u0948\u0902 \u0924\u093e\u0915\u093f loop \u0915\u094b \u0906\u0917\u0947 \u092c\u095c\u093e\u092f\u093e \u091c\u093e \u0938\u0915\u0947 \u0964 \u092f\u093e reverse loop \u091a\u0932\u093e\u0928\u093e \u091a\u093e\u0939\u0924\u0947 \u0939\u0948\u0902 \u0924\u092c Decrement \u0915\u0930\u0924\u0947 \u0939\u0948\u0902\u0964 Increment \u0915\u0947 \u0932\u093f\u090f \u092f\u0939\u093e \u092a\u0930<strong> i+1<\/strong> \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u093f\u092f\u093e \u0917\u092f\u093e \u0939\u0948\u0964 <\/li>\n<\/ul>\n\n\n\n<p><a href=\"https:\/\/youtu.be\/QrlpO6lXZ2g\" target=\"_blank\" rel=\"noopener\">Related Video : With Clause Recursive CTE (Common Table Expression)<\/a><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">WITH RECURSIVE mytable(i) AS\n(\n\tSELECT 0\n\tUNION ALL\n\tSELECT i+1 FROM mytable WHERE i&lt;5\n)\nSELECT * FROM mytable;<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"80\" height=\"156\" src=\"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/image-3.png?resize=80%2C156&#038;ssl=1\" alt=\"\" class=\"wp-image-10089\"\/><\/figure>\n\n\n\n<p>Result \u0926\u0947\u0916\u0924\u0947 \u0939\u0940 \u0906\u092a\u0915\u0947 \u092e\u0928 \u092e\u0947 \u090f\u0915 Query \u0906\u092f\u0940 \u0939\u094b\u0917\u0940 \u0915\u0940 \u091c\u092c <strong>i&lt;5<\/strong> \u0926\u093f\u092f\u093e \u0939\u0948\u0902 \u0924\u094b \u092f\u0939 5 \u0924\u0915 \u0915\u094d\u092f\u0942 Execute \u0939\u0941\u0935\u093e? \u0924\u094b \u0907\u0938\u0915\u093e \u091c\u0935\u093e\u092c \u092f\u0939 \u0939\u0948\u0902 \u0939\u092e\u0928\u0947 i \u092a\u0930 condition \u0932\u0917\u093e\u0908 \u0939\u0948\u0902 \u0914\u0930 Display i+1 \u0915\u093f\u092f\u093e \u0939\u0948\u0902 \u092f\u093e\u0928\u093f \u0915\u0908 \u091c\u092c \u0906\u0908 \u0915\u0908 \u0935\u0948\u0932\u094d\u092f\u0942 \u0906\u0916\u0930\u0940 Cycle \u092a\u0930 4 \u0925\u0940 \u091c\u092c 5 print \u0915\u093f\u092f\u093e \u0914\u0930 \u0909\u0915\u0947 \u092c\u093e\u0926 Recursive Execution End \u0939\u094b \u0917\u092f\u093e \u0964 <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>\u0906\u0936\u093e \u0939\u0948\u0902 \u092e\u0941\u091d\u0947 \u0915\u0940 \u0906\u092a\u0915\u094b\u00a0<strong>MySQL<\/strong> \u092e\u0947 Loop Logic \u092c\u0939\u0941\u0924 \u0905\u091a\u094d\u091b\u0947 \u0938\u0947 \u0938\u092e\u091d \u092e\u0947 \u0906 \u0917\u092f\u093e \u0939\u094b\u0917\u093e, \u092f\u0926\u093f \u092b\u093f\u0930 \u092d\u0940 \u0906\u092a\u0915\u0947 \u0915\u094b\u0908 Doubts \u0939\u0948\u0902 \u0924\u094b \u0906\u092a \u092e\u0941\u091d\u0947 Comment \u0915\u0930 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<p>\u092f\u0939 Article \u0915\u0948\u0938\u093e \u0932\u0917\u093e \u092c\u0924\u093e\u0928\u093e \u0928 \u092d\u0942\u0932\u0947, \u0905\u092a\u0928\u093e \u0915\u0940\u092e\u0924\u0940 \u0938\u092e\u092f \u0926\u0947\u0928\u0947 \u0915\u0947 \u0932\u093f\u090f \u0927\u0928\u094d\u092f\u0935\u093e\u0926 \u0964<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"strong-\u0939\u092e\u093e\u0930\u0947-\u0905\u0928\u094d\u092f-\u0906\u0930\u094d\u091f\u093f\u0915\u0932-strong\"><strong>\u0939\u092e\u093e\u0930\u0947 \u0905\u0928\u094d\u092f \u0906\u0930\u094d\u091f\u093f\u0915\u0932<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-function\/\">MySQL Function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-stored-procedure\/\">MySQL Stored Procedure<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/get-post-method\/\">PHP Get \/Post Method \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/php-math-function\/\">PHP Math Function \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/learn-cpp-from-c\/\">C \u0938\u0947 C ++ \u0938\u0940\u0916\u0947 \u0938\u0930\u0932 \u0936\u092c\u094d\u0926\u094b\u0902 \u092e\u0947\u0902<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/php-variables\/\">PHP Variable \u0915\u0947 \u092c\u093e\u0930\u0947 \u092e\u0947\u0902 \u091c\u093e\u0928\u0947<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/arduino-control-structure\/\">Arduino Control Structure<\/a>&nbsp;<\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/c-union\/\">Union in C<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/c-pointer\/\">Declaration of Pointer in C<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/search-jquery\/\">jQuery Search Filter<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/mysql-table-create-alter-drop\/\">MySQL Create Table | Alter Table | Drop Table<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/api-using-node-react-express-mysql\/\">API Using NODE REACT EXPRESS MYSQL SEQUELIZE (For Backend OR Server Side)<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/selfimagination.in\/tips\/cpanel\/\">CPANEL In Hindi<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>common table expression, sql cte common table expression, common table expressions, sql cte (common table expression), sql common table expression, common table expression in sql, mysql common table expression, common table expression in mysql, common table expression syntax, common table expression in database, how to use common table expressions What is CTE \u0915\u094d\u092f\u093e \u0939\u0948\u0902 ? [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":10142,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-10086","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2023\/01\/MySQL-loop-using-cte.jpg?fit=1200%2C628&ssl=1","_links":{"self":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/10086","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/comments?post=10086"}],"version-history":[{"count":55,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/10086\/revisions"}],"predecessor-version":[{"id":10146,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/10086\/revisions\/10146"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media\/10142"}],"wp:attachment":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media?parent=10086"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/categories?post=10086"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/tags?post=10086"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}