{"id":8393,"date":"2021-01-28T07:00:00","date_gmt":"2021-01-28T07:00:00","guid":{"rendered":"https:\/\/ubiq.co\/tech-blog\/?p=8393"},"modified":"2026-03-06T07:06:10","modified_gmt":"2026-03-06T07:06:10","slug":"common-table-expression-in-mysql","status":"publish","type":"post","link":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/","title":{"rendered":"Common Table Expression(CTE) in MySQL"},"content":{"rendered":"\n<p>Common Table Expressions (CTE) are a useful feature that allow MySQL developers to name temporary result of SQL queries. They allow you to easily write complex SQL queries in MySQL and also <a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-speed-up-sql-queries\/\">speed up existing SQL queries<\/a>. In this article, we will look at what is Common Table Expression in MySQL and how to write CTEs in MySQL.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 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\" style=\"cursor:inherit\">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=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><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><\/span><\/span><\/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:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#What_is_Common_Table_Expression_CTE\" >What is Common Table Expression (CTE)<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#Common_Table_Expression_in_MySQL\" >Common Table Expression in MySQL<\/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:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#Variations_of_CTE\" >Variations of CTE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#CTE_as_Subquery\" >CTE as Subquery<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#Recursive_CTE\" >Recursive CTE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#Common_Table_Expression_in_Ubiq\" >Common Table Expression in Ubiq<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_Common_Table_Expression_CTE\"><\/span>What is Common Table Expression (CTE)<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Often database developers and data analysts end up creating really long SQL queries with many subqueries. They are not only difficult to understand but also slow in performance. Common Table Expression is a named result set whose scope is limited to the SQL query in which it is defined and used. It is similar to a derived table but it is not stored as an object and can be self-referencing, unlike derived tables. As a result, CTE can speed up queries &amp; give better performance than derived tables in MySQL. They improve query readability and can be re-used in other queries. You can even use it to create self-referencing recursive queries.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Common_Table_Expression_in_MySQL\"><\/span>Common Table Expression in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>We will learn the syntax of basic CTE and then some of its common variations. Here is the syntax of common table expression in MySQL.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH cte_name (column_list) AS (     \n      query \n)  \nSELECT * FROM cte_name;<\/pre>\n\n\n\n<p>In the above query, you need to specify CTE&#8217;s name, the SQL query whose result should be referenced using CTE name. Please note, column_list is optional. But if you mention it, then the number of columns in <em>column_list<\/em> should be same as those in your <em>query<\/em>.<\/p>\n\n\n\n<p>You can also define multiple CTEs using a single statement as shown below.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH<br>    cte_name1 AS (<br>        -- Query here<br>    ),<br>    cte_name2 AS (<br>        -- Query here<br>    )<\/pre>\n\n\n\n<p>Let us look at an example of common table expression (CTE).<\/p>\n\n\n\n<p>Let us say you have <em>sales(id, order_date, amount)<\/em> table.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; create table sales(id int, order_date date, amount int);\n\nmysql&gt; insert into sales(id, order_date, amount)\n      values(1, '2021-01-01', 200),\n      (2, '2021-01-02', 250),\n      (3, '2021-01-03', 220),\n      (4, '2021-01-04', 230),\n      (5, '2021-01-05', 210),\n      (6, '2021-01-06', 100),\n      (7, '2021-01-07', 120),\n      (8, '2021-01-08', 150),\n      (9, '2021-01-09', 180),\n      (10, '2021-01-10', 200);\n\n mysql&gt; select * from sales;\n +------+------------+--------+\n | id   | order_date | amount |\n +------+------------+--------+\n |    1 | 2021-01-01 |    200 |\n |    2 | 2021-01-02 |    250 |\n |    3 | 2021-01-03 |    220 |\n |    4 | 2021-01-04 |    230 |\n |    5 | 2021-01-05 |    210 |\n |    6 | 2021-01-06 |    100 |\n |    7 | 2021-01-07 |    120 |\n |    8 | 2021-01-08 |    150 |\n |    9 | 2021-01-09 |    180 |\n |   10 | 2021-01-10 |    200 |\n +------+------------+--------+<\/pre>\n\n\n\n<p>Here is a CTE to select only sales records that have amount&gt;200.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">with test_cte as (\n     select id,order_date,amount from sales\n     where amount&gt;200\n ) select * from test_cte; \n\n +------+------------+--------+\n | id   | order_date | amount |\n +------+------------+--------+\n |    1 | 2021-01-01 |    200 |\n |    2 | 2021-01-02 |    250 |\n |    3 | 2021-01-03 |    220 |\n |    4 | 2021-01-04 |    230 |\n |    5 | 2021-01-05 |    210 |\n |   10 | 2021-01-10 |    200 |\n +------+------------+--------+<\/pre>\n\n\n\n<p>In the above CTE, we specify the SELECT query to filter rows with amount&gt;200 inside WITH clause.<\/p>\n\n\n\n<p>You can also change the names of your table columns via CTE. Here is an example where we have renamed the order_date column in sales table to sale_date in CTE. Here, MySQL will look at the number of columns mentioned in CTE and its query and assign column names based on their order in the query result. Here sale_date is 2nd column in CTE and order_date is 2nd column in SELECT query so order_date is renamed to sale_date in CTE.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">with test_cte (id, <strong>sale_date<\/strong>, amount) as (<br>     select id,<strong>order_date<\/strong>,amount from sales<br>     where amount&gt;200<br> ) select * from test_cte; <br><br> +------+------------+--------+<br> | id   | <strong>sale_date<\/strong>  | amount |<br> +------+------------+--------+<br> |    1 | 2021-01-01 |    200 |<br> |    2 | 2021-01-02 |    250 |<br> |    3 | 2021-01-03 |    220 |<br> |    4 | 2021-01-04 |    230 |<br> |    5 | 2021-01-05 |    210 |<br> |   10 | 2021-01-10 |    200 |<br> +------+------------+--------+<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Variations_of_CTE\"><\/span>Variations of CTE<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Please note, you can use CTE with SELECT, UPDATE, INSERT and DELETE statements. Here are the syntaxes for the same.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH \u2026 SELECT \u2026\nWITH \u2026 UPDATE \u2026\nWITH \u2026 DELETE \u2026<\/pre>\n\n\n\n<p>You can also use the result of CTE as input to other queries, just as we use result of sub queries as input to other queries. For example, if you use the result of a SELECT statement as the input for CREATE\/INSERT\/REPLACE statements, then also you can use WITH clause in it. Here are some examples,<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE \u2026 WITH \u2026 SELECT \u2026\nCREATE VIEW \u2026 WITH \u2026 SELECT \u2026\nINSERT \u2026 WITH \u2026 SELECT \u2026\nREPLACE \u2026 WITH \u2026 SELECT \u2026\nDECLARE CURSOR \u2026 WITH \u2026 SELECT \u2026\nEXPLAIN \u2026 WITH \u2026 SELECT \u2026<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"CTE_as_Subquery\"><\/span>CTE as Subquery<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Also, you may use a CTE as a subquery, as shown below.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT \u2026 WHERE id IN (WITH \u2026 SELECT \u2026);<br>SELECT * FROM (WITH \u2026 SELECT \u2026) AS temporary_table;<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Recursive_CTE\"><\/span>Recursive CTE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Here is the syntax for recursive CTE in MySQL. You need to mention RECURSIVE keyword after WITH in SQL query.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH RECURSIVE cte_name (column_names) AS ( subquery )   <br>SELECT * FROM cte_name;  <\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Common_Table_Expression_in_Ubiq\"><\/span>Common Table Expression in Ubiq<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p><a href=\"https:\/\/ubiq.co\/mysql-reporting-tools\">Ubiq<\/a>&nbsp;Reporting tool supports CTE-based SELECT queries and makes it easy to visualize SQL results in different ways. It also allows you to create dashboards &amp; charts from MySQL query results. Here is the above CTE query in Ubiq.<\/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=\"730\" height=\"478\" src=\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/cte-ubiq-1.webp?resize=730%2C478&#038;ssl=1\" alt=\"\" class=\"wp-image-8394\"\/><\/figure>\n<\/div>\n\n\n<p>In fact, after you run the query, you can simply click a visualization type to plot the result in a chart.<\/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=\"730\" height=\"583\" src=\"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/cte-ubiq-2.webp?resize=730%2C583&#038;ssl=1\" alt=\"\" class=\"wp-image-8395\"\/><\/figure>\n<\/div>\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>In this article, we have learnt what common table expressions (CTE) are in MySQL. Please note, CTE is supported in all major database systems such as SQL Server, Oracle, PostgreSQL, etc. We have also learnt several important variations in its syntax, and also recursive CTEs. They are a great way to simplify complex queries and good alternative to subqueries.<\/p>\n\n\n\n<p>Need a reporting tool for MySQL?&nbsp;<a href=\"http:\/\/ubiq.co\/\">Ubiq<\/a>&nbsp;makes it easy to visualize data in minutes, and monitor in real-time dashboards.&nbsp;<a href=\"http:\/\/ubiq.co\/accounts\/register\">Try it<\/a>&nbsp;Today!<\/p>\n\n\n\n<p>Also read:<br><a href=\"https:\/\/ubiq.co\/tech-blog\/how-to-calculate-margin-in-mysql\/\">How to Calculate Margin in MySQL<\/a><br><a href=\"https:\/\/ubiq.co\/tech-blog\/mysql-query-to-get-top-selling-products\/\">How to Get Top Selling Products in MySQL<\/a><br><a href=\"https:\/\/ubiq.co\/tech-blog\/multiple-counts-with-different-conditions-in-single-mysql-query\/\">How to Get Multiple Counts in Single MySQL Query<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expressions in MySQL.<\/p>\n","protected":false},"author":1,"featured_media":8396,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[324],"tags":[442],"class_list":["post-8393","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-common-expression-table"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Common Table Expression(CTE) in MySQL - Ubiq BI<\/title>\n<meta name=\"description\" content=\"Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expression in MySQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Common Table Expression(CTE) in MySQL - Ubiq BI\" \/>\n<meta property=\"og:description\" content=\"Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expression in MySQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Ubiq BI\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ubiqbi\" \/>\n<meta property=\"article:published_time\" content=\"2021-01-28T07:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-06T07:06:10+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"730\" \/>\n\t<meta property=\"og:image:height\" content=\"410\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Sreeram Sreenivasan\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@UbiqBI\" \/>\n<meta name=\"twitter:site\" content=\"@UbiqBI\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sreeram Sreenivasan\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/\"},\"author\":{\"name\":\"Sreeram Sreenivasan\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/#\\\/schema\\\/person\\\/db98d49a766a3a111d8510935ab90abc\"},\"headline\":\"Common Table Expression(CTE) in MySQL\",\"datePublished\":\"2021-01-28T07:00:00+00:00\",\"dateModified\":\"2026-03-06T07:06:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/\"},\"wordCount\":707,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/i0.wp.com\\\/ubiq.co\\\/tech-blog\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1\",\"keywords\":[\"common expression table\"],\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/\",\"url\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/\",\"name\":\"Common Table Expression(CTE) in MySQL - Ubiq BI\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/i0.wp.com\\\/ubiq.co\\\/tech-blog\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1\",\"datePublished\":\"2021-01-28T07:00:00+00:00\",\"dateModified\":\"2026-03-06T07:06:10+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/#\\\/schema\\\/person\\\/db98d49a766a3a111d8510935ab90abc\"},\"description\":\"Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expression in MySQL.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#primaryimage\",\"url\":\"https:\\\/\\\/i0.wp.com\\\/ubiq.co\\\/tech-blog\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1\",\"contentUrl\":\"https:\\\/\\\/i0.wp.com\\\/ubiq.co\\\/tech-blog\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1\",\"width\":730,\"height\":410,\"caption\":\"common expression table in mysql\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/common-table-expression-in-mysql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Common Table Expression(CTE) in MySQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/#website\",\"url\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/\",\"name\":\"Ubiq BI\",\"description\":\"Build dashboards &amp; reports in minutes\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/#\\\/schema\\\/person\\\/db98d49a766a3a111d8510935ab90abc\",\"name\":\"Sreeram Sreenivasan\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g\",\"caption\":\"Sreeram Sreenivasan\"},\"description\":\"Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI &amp; software development.\",\"sameAs\":[\"https:\\\/\\\/www.linkedin.com\\\/in\\\/sreeram-sreenivasan\\\/\"],\"url\":\"https:\\\/\\\/ubiq.co\\\/tech-blog\\\/author\\\/wordpress\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Common Table Expression(CTE) in MySQL - Ubiq BI","description":"Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expression in MySQL.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"Common Table Expression(CTE) in MySQL - Ubiq BI","og_description":"Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expression in MySQL.","og_url":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/","og_site_name":"Ubiq BI","article_publisher":"https:\/\/www.facebook.com\/ubiqbi","article_published_time":"2021-01-28T07:00:00+00:00","article_modified_time":"2026-03-06T07:06:10+00:00","og_image":[{"width":730,"height":410,"url":"https:\/\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp","type":"image\/webp"}],"author":"Sreeram Sreenivasan","twitter_card":"summary_large_image","twitter_creator":"@UbiqBI","twitter_site":"@UbiqBI","twitter_misc":{"Written by":"Sreeram Sreenivasan","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#article","isPartOf":{"@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/"},"author":{"name":"Sreeram Sreenivasan","@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc"},"headline":"Common Table Expression(CTE) in MySQL","datePublished":"2021-01-28T07:00:00+00:00","dateModified":"2026-03-06T07:06:10+00:00","mainEntityOfPage":{"@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/"},"wordCount":707,"commentCount":0,"image":{"@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1","keywords":["common expression table"],"articleSection":["MySQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/","url":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/","name":"Common Table Expression(CTE) in MySQL - Ubiq BI","isPartOf":{"@id":"https:\/\/ubiq.co\/tech-blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#primaryimage"},"image":{"@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1","datePublished":"2021-01-28T07:00:00+00:00","dateModified":"2026-03-06T07:06:10+00:00","author":{"@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc"},"description":"Common Table Expressions allow you to easily write complex SQL queries in MySQL. Here is how to create common table expression in MySQL.","breadcrumb":{"@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#primaryimage","url":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1","contentUrl":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1","width":730,"height":410,"caption":"common expression table in mysql"},{"@type":"BreadcrumbList","@id":"https:\/\/ubiq.co\/tech-blog\/common-table-expression-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/ubiq.co\/tech-blog\/"},{"@type":"ListItem","position":2,"name":"Common Table Expression(CTE) in MySQL"}]},{"@type":"WebSite","@id":"https:\/\/ubiq.co\/tech-blog\/#website","url":"https:\/\/ubiq.co\/tech-blog\/","name":"Ubiq BI","description":"Build dashboards &amp; reports in minutes","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/ubiq.co\/tech-blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/ubiq.co\/tech-blog\/#\/schema\/person\/db98d49a766a3a111d8510935ab90abc","name":"Sreeram Sreenivasan","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4b3127ed2d4bb8efb3fa0bbb52cf2efd4d0156c97fc05a503537c883e8279947?s=96&d=mm&r=g","caption":"Sreeram Sreenivasan"},"description":"Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI &amp; software development.","sameAs":["https:\/\/www.linkedin.com\/in\/sreeram-sreenivasan\/"],"url":"https:\/\/ubiq.co\/tech-blog\/author\/wordpress\/"}]}},"jetpack_featured_media_url":"https:\/\/i0.wp.com\/ubiq.co\/tech-blog\/wp-content\/uploads\/2025\/06\/common-table-expression-in-mysql.webp?fit=730%2C410&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/pbGGTT-2bn","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/comments?post=8393"}],"version-history":[{"count":3,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8393\/revisions"}],"predecessor-version":[{"id":10663,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/posts\/8393\/revisions\/10663"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/media\/8396"}],"wp:attachment":[{"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/media?parent=8393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/categories?post=8393"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ubiq.co\/tech-blog\/wp-json\/wp\/v2\/tags?post=8393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}