{"id":5871,"date":"2017-07-01T01:51:20","date_gmt":"2017-07-01T08:51:20","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5871"},"modified":"2023-12-28T18:21:57","modified_gmt":"2023-12-29T01:21:57","slug":"mysql-cte","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-cte\/","title":{"rendered":"MySQL CTE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL CTE or common table expression to construct complex queries in a more readable manner.<\/p>\n\n\n\n

MySQL introduced the common table expression or CTE  feature since version 8.0 so you should have MySQL 8.0+ to practice with the statements in this tutorial.<\/p>\n\n\n\n

Introduction to the common table expression (CTE)<\/h2>\n\n\n\n

A common table expression is a named temporary result set that exists solely within the execution scope of a single SQL statement, such as SELECT<\/code><\/a>, INSERT<\/code><\/a>, UPDATE<\/code><\/a>, or DELETE<\/code><\/a>.<\/p>\n\n\n\n

Similar to a derived table<\/a>, a common table expression (CTE) is not stored as an object and lasts only during the query execution.<\/p>\n\n\n\n

Unlike a derived table, a common table expression (CTE) can be self-referencing (in the case of a recursive CTE<\/a>) or referenced multiple times within the same query. Moreover, a CTE offers enhanced readability and performance compared to a derived table.<\/p>\n\n\n\n

MySQL CTE syntax<\/h2>\n\n\n\n

The structure of a CTE includes the name, an optional column list, and a query that defines the CTE. After you define a CTE, you can use like a view in the SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>, or CREATE VIEW<\/code> statement.<\/p>\n\n\n\n

The following illustrates the basic syntax of a CTE:<\/p>\n\n\n

WITH<\/span> cte_name (column_list) AS<\/span> (\n    query<\/span>\n) \nSELECT<\/span> * FROM<\/span> cte_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n