In this blog, you will find out how to use MySQL CTE or common table expression to build complex queries in a more readable manner.
MySQL initiate the public table expression characteristic or CTE in short since version 8.0 so you should have MySQL 8.0 installed on your computer in order to exercise with the statements in this blog.
What is a common table expression or CTE
A common table expression is a named impermanent result set that live only within the execution scope of a respective SQL statement e.g.,SELECT, INSERT, UPDATE, or DELETE.
Related to a derived table, a CTE is not stored as an object and last only during the executing of a query. Contrary from a traced table, a CTE can be self-referencing (a recursive CTE) or can be documented multiple times in the same query. In step-up, a CTE render better legibility and performance in comparability with a derived table.
MySQL CTE syntax
The construction of a CTE includes the name, an elective column list, and a query that okay the CTE. After the CTE is outlined, you can use it like a view in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
The next elaborate the basic syntax of a CTE:
Attention that the number of columns in the query must be the same as the number of columns in the column_list. If you exclude the column_list, the CTE will use the column list of the query that precise the CTE
Simple MySQL CTE examples
The next example exemplify how to use a CTE for enquiry data from the customers table in example database. Note that this instance is only for the presentation purpose to make it easy for you to interpret the CTE concept.
In this illustration, 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 placed in the USA.
Later defining the customers_in_usa CTE, we documented it in the SELECT statement to select only customers located in California.
See another representation:
In this illustration, the CTE returns the top 5 sales rep in 2003. After that, we documented to the topsales2003 CTE to get extra info about the sales rep consider first name and last name.
A more modern MySQL CTE example
See the following illustration:
In this illustration, we have two CTEs in the said query. The first CTE ( sales-rep) gets the employees whose job titles are the sales representative. The second CTE ( customer_salesrep ) mention the first CTE in the INNER JOIN clause to get the sales rep and customers of whom each sales rep is in charge.
After having the second CTE, we query data from that CTE using a simple SELECT statement with the ORDER BY clause.
WITH clause use
There are some contexts that you can use the WITH clause to make common table expressions:
First, a WITH clause can be used at the beginning of SELECT, UPDATE, and DELETE statements:
Second, a WITH clause can be used at the opening of a subquery or a derived table subquery:
Third, a WITH clause can be used directly preceding SELECT of the statements that include a SELECT clause:
In this blog, you have well-educated how to use the MySQL CTE to make complex queries.