{"id":5153,"date":"2024-03-14T02:27:01","date_gmt":"2024-03-14T02:27:01","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/"},"modified":"2025-08-01T11:25:09","modified_gmt":"2025-08-01T11:25:09","slug":"how-is-the-usage-of-cte-in-mysql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/","title":{"rendered":"MySQL CTE Usage Guide: Tips &#038; Examples"},"content":{"rendered":"<p>In MySQL, Common Table Expressions (CTEs) are commonly used temporary expressions that create a named temporary result set in a query for referencing in subsequent queries. CTEs can enhance the readability and performance of queries.<\/p>\n<p>The general syntax for using CTE is as follows:<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">WITH<\/span> cte_name <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-comment\">-- CTE\u7684\u67e5\u8be2\u8bed\u53e5<\/span>\r\n    <span class=\"hljs-keyword\">SELECT<\/span> column1, column2\r\n    <span class=\"hljs-keyword\">FROM<\/span> table_name\r\n    <span class=\"hljs-keyword\">WHERE<\/span> <span class=\"hljs-keyword\">condition<\/span>\r\n)\r\n<span class=\"hljs-comment\">-- \u4f7f\u7528CTE<\/span>\r\n<span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-operator\">*<\/span>\r\n<span class=\"hljs-keyword\">FROM<\/span> cte_name;\r\n<\/code><\/pre>\n<p>In the above syntax, WITH cte_name AS is used to define a Common Table Expression (CTE), where cte_name is the name of the CTE, and the following query is used to specify the content of the CTE. Then, in subsequent queries, cte_name can be used to reference the data within the CTE.<\/p>\n<p>Here is an example demonstrating how to use CTE in MySQL.<\/p>\n<pre class=\"post-pre\"><code><span class=\"hljs-keyword\">WITH<\/span> employees_cte <span class=\"hljs-keyword\">AS<\/span> (\r\n    <span class=\"hljs-keyword\">SELECT<\/span> employee_id, first_name, last_name, salary\r\n    <span class=\"hljs-keyword\">FROM<\/span> employees\r\n    <span class=\"hljs-keyword\">WHERE<\/span> department_id <span class=\"hljs-operator\">=<\/span> <span class=\"hljs-number\">10<\/span>\r\n)\r\n<span class=\"hljs-keyword\">SELECT<\/span> <span class=\"hljs-operator\">*<\/span>\r\n<span class=\"hljs-keyword\">FROM<\/span> employees_cte;\r\n<\/code><\/pre>\n<p>In the example above, a CTE named employees_cte is firstly defined to select the IDs, names, surnames, and salaries of all employees with a department ID of 10. Then, in subsequent queries, the data in the CTE is referenced by using SELECT * FROM employees_cte.<\/p>\n<p>It is important to note that in MySQL, CTE can only be used in queries after its definition in the query, so if you need to reference CTE multiple times in the same query, you can define the same CTE multiple times using the WITH keyword.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In MySQL, Common Table Expressions (CTEs) are commonly used temporary expressions that create a named temporary result set in a query for referencing in subsequent queries. CTEs can enhance the readability and performance of queries. The general syntax for using CTE is as follows: WITH cte_name AS ( &#8212; CTE\u7684\u67e5\u8be2\u8bed\u53e5 SELECT column1, column2 FROM table_name [&hellip;]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[5490,5492,5489,4361,5491],"class_list":["post-5153","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-common-table-expressions","tag-cte-benefits","tag-mysql-cte","tag-mysql-queries","tag-sql-cte"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MySQL CTE Usage Guide: Tips &amp; Examples - Blog - Silicon Cloud<\/title>\n<meta name=\"description\" content=\"Master MySQL CTE usage: Improve query readability and performance with our practical guide. Includes syntax examples.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL CTE Usage Guide: Tips &amp; Examples\" \/>\n<meta property=\"og:description\" content=\"Master MySQL CTE usage: Improve query readability and performance with our practical guide. Includes syntax examples.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-14T02:27:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-01T11:25:09+00:00\" \/>\n<meta name=\"author\" content=\"Isabella Edwards\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Isabella Edwards\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\"},\"author\":{\"name\":\"Isabella Edwards\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\"},\"headline\":\"MySQL CTE Usage Guide: Tips &#038; Examples\",\"datePublished\":\"2024-03-14T02:27:01+00:00\",\"dateModified\":\"2025-08-01T11:25:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\"},\"wordCount\":207,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"keywords\":[\"Common Table Expressions\",\"CTE Benefits\",\"MySQL CTE\",\"MySQL queries\",\"SQL CTE\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\",\"name\":\"MySQL CTE Usage Guide: Tips & Examples - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-14T02:27:01+00:00\",\"dateModified\":\"2025-08-01T11:25:09+00:00\",\"description\":\"Master MySQL CTE usage: Improve query readability and performance with our practical guide. Includes syntax examples.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL CTE Usage Guide: Tips &#038; Examples\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\",\"name\":\"Isabella Edwards\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"caption\":\"Isabella Edwards\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MySQL CTE Usage Guide: Tips & Examples - Blog - Silicon Cloud","description":"Master MySQL CTE usage: Improve query readability and performance with our practical guide. Includes syntax examples.","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:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/","og_locale":"en_US","og_type":"article","og_title":"MySQL CTE Usage Guide: Tips & Examples","og_description":"Master MySQL CTE usage: Improve query readability and performance with our practical guide. Includes syntax examples.","og_url":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-14T02:27:01+00:00","article_modified_time":"2025-08-01T11:25:09+00:00","author":"Isabella Edwards","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Isabella Edwards","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/"},"author":{"name":"Isabella Edwards","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd"},"headline":"MySQL CTE Usage Guide: Tips &#038; Examples","datePublished":"2024-03-14T02:27:01+00:00","dateModified":"2025-08-01T11:25:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/"},"wordCount":207,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"keywords":["Common Table Expressions","CTE Benefits","MySQL CTE","MySQL queries","SQL CTE"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/","url":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/","name":"MySQL CTE Usage Guide: Tips & Examples - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-14T02:27:01+00:00","dateModified":"2025-08-01T11:25:09+00:00","description":"Master MySQL CTE usage: Improve query readability and performance with our practical guide. Includes syntax examples.","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-is-the-usage-of-cte-in-mysql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL CTE Usage Guide: Tips &#038; Examples"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd","name":"Isabella Edwards","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","caption":"Isabella Edwards"},"url":"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/5153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=5153"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/5153\/revisions"}],"predecessor-version":[{"id":149889,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/5153\/revisions\/149889"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=5153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=5153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=5153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}