Summary: in this tutorial, you will learn how to use MySQL CTE or common table expression to construct complex queries in a more readable manner. SQL Server.
MySQL ( MySQL DBA Training) introduced the common table expression feature or CTE in short since version 8.0 so you should have MySQL 8.0 installed on your computer in order to practice with the statements in this tutorial. SQL Server Training.
What is a common table expression or CTE
A common table expression is a named temporary result set that exists only within the execution scope of a single SQL statement e.g.,SELECT, INSERT, UPDATE, or DELETE.
Similar to a derived table, a CTE is not stored as an object and last only during the execution of a query. ( MySQL DBA Training) Different from a derived table, a CTE can be self-referencing (a recursive CTE) or can be referenced multiple times in the same query. In addition, a CTE provides better readability and performance in comparison with a derived table. SQL Server.
MySQL CTE syntax
The structure of a CTE includes the name, an optional column list, and a query that defines the CTE. SQL Server. After the CTE is defined, you can use it like a view in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEWstatement.
The following illustrates the basic syntax of a CTE:
1234WITH cte_name (column_list) AS ( query) SELECT * FROM cte_name;
Notice that the number of columns in the query must be the same as the number of columns in the column_list. SQL Server Training. If you omit the column_list, the CTE will use the column list of the query that defines the CTE
Simple MySQL CTE examples ( MySQL DBA Training)
The following example illustrates how to use a CTE for querying data from the customers table in the sample database. Note that this example is only for the demonstration purpose to make it easy for you to understand the CTE concept.SQL Server.
WITH customers_in_usa AS ( SELECT customerName, state FROM customers WHERE country = 'USA') SELECT customerName FROM customers_in_usa WHERE state = 'CA' ORDER BY customerName;
In this example, the name of the CTE is customers_in_usa, the query that defines the CTE returns two columns customerName and state. Hence, the customers_in_usa CTE returns all customers located in the USA. ( MySQL DBA Training)
After defining the customers_in_usa CTE, we referenced it in the SELECT statement to select only customers located in California.