Introduction to the MySQL Common Table Expression - Simple Talk (2023)

This article is part of Robert Sheldon's ongoing series on learning MySQL. To view all 11 articles in the series,Click here.

As with many relational database management systems, MySQL provides a variety of methods for combining data in a DML (data manipulation language) statement. You can join multiple tables in a single query, or add subqueries that retrieve data from other tables. You can also access temporary tables and views from one statement, often with permanent tables.

MySQL also provides another valuable tool for working with data: the Common Table Expression (CTE). A CTE is a named result set that is defined in aCONClause. theCONThe clause is associated with a single DML declaration, but it is built outside of the declaration. However, only this statement can access the result set.

In some cases, it may include a CTESELECTStatement embedded within another statement, such as a subquery or aDELETE... SELECTExpression. But even then, theCONThe clause is defined outside of it.SELECTstatement, and only thatSELECTstatement can access the result set.

One way to think of a CTE is as a view type with a very limited scope (a declaration). Another way to think of a CTE is as a kind of named subquery, defined in a separate clause from the main query. However, a CTE is neither and in this article I will explain how the CTE works and walk you through a series of examples showing the different ways you can use it to retrieve data.

Note: The examples use the tables and data created in the last section of the article titled "Appendix: Preparing the demo objects and data."

Introduction to common table expressions

A common table expression is defined within aCONClause. The clause comes before the main DML declaration, sometimes called a top-level declaration. Additionally, the clause can contain one or more CTE definitions, as shown in the following syntax:

If heCONclause contains more than one CTE, you must separate them with commas and give each CTE a unique name, although this only applies in the context ofCONClause. for example twoSELECTStatements can contain CTEs with the same name because a CTE is limited to the scope of its associated top-level statement.

CTE names are followed by one or more optional column names, then theANDkeyword and finally aSELECTQuery enclosed in parentheses. If you specify column names, their number must match the number of columns returned bySELECTQuery. If you do not specify column names, the column names returned by theSELECTqueries are used.

Common table expressions are generally used withSELECTTestify. However, you can also use them withTO UPDATEyCLEARDeclarations that follow the same syntax as the previous ones. Additionally, you can include CTEs in your subqueries when passing them to your external statements. You can also use CTE in statements that support the use ofSELECTas part of the statement definition. For example, you can add aCONclause aSELECTconsultation in oneINSERT... SELECTstatement or in aTO CREATE TABLE... SELECTExpression.

In this article, I will mainly focus on creating CTEs that useSELECTstatements as its top-level statements, since that's the most common way to use a CTE. This approach is also a good way to get familiar with CTEs without compromising the data. You can then apply the basic principles you learn here to other types of returns as you become more familiar with how CTEs work.

With that in mind, let's start with a simple example. MonitoringSELECTstatement contains aCONclause that defines a CTE namedplanes:

DieSELECTConsultation in the CTE retrieves all the aircraft in theplanestable that hasJetAs thetype of motor. The CTE result set consists of the data returned bySELECTSee and can be accessed from the upper levelSELECTExpression.

The top-level statement retrieves the data directly from the CTE, similar to how a statement can retrieve data from a view. The main difference is that the view definition is stored in the database and can be used by anyone with the proper permissions. The CTE, on the other hand, is very limited in scope and can only be called in the context of the top-level statement.

In this case the top levelSELECTstatement only retrieves theairplaneymaximum weightColumns of the CTE and orders the results bymaximum weightcolumn in descending order. The following image shows the results returned by the statement.

You can of course get the same results without using a CTE by looking at theplanesDirect table:

However, I wanted to show the basic components that are part of a CTE and how you can access that CTE from the top level.SELECTExpression. Both the CTE and the high-level statement certainly can be, and usually are, much more complex, but the principles remain the same.

Work with high level CTESELECTexpression

As mentioned above, a CTE is basically a named result set. When you query the CTE within the top-level statement, the data is returned in a tabular format, similar to what you get when you query a view, permanent table, temporary table, or derived table (such as queries generated by a subquery ) in oneSELECTexpressionVONClause). This means that you can work with the CTE just like you can with these other types of objects. For example, a common approach to referencing a CTE within a top-level query is to join it to another table, as in the following example:

DieCONclause defines a single CTE namedmfcs. The CTEsSELECTQuery returns thesupplier identificationyManufacturervalues ​​of theManufacturerDesk. the upper levelSELECTthe statement then binds to thatplanestable amfcsCTE, based on thesupplier identificationcolumn in each. The following figure shows the results of the statement.

As this example shows, you can treat the CTE like any other table structure in your top-level query. However, as you've already seen, you can also rephrase this statement without a CTE by joining theplanestable directly toManufacturerTisch:

Because there is so little data, the performance difference between the two statements is negligible, but that may not be the case with a much larger data set. However, as is often the case with MySQL, it can be hard to know which approach is better without comparing both statements against a realistic data set. Even then, there may not be a significant difference in performance, in which case it could be down to developer preference.

As mentioned above, MySQL often supports multiple ways to achieve the same results, as shown in the examples above. Common table expressions can sometimes help simplify code and make it more readable, which is a great benefit, but performance should generally be the primary consideration.

Comparing different approaches usually requires you to test them on a valid data set, in part because it can be difficult to find specific recommendations when comparing approaches. For example, not many database developers would be willing to say that you should do this.foreverUse CTE instead of inner joinsacircumstances or vice versa.

However, you may find recommendations that are less rigid and perhaps worth considering, e.g. B. when comparing CTEs with subqueries. For example, a CTE is often considered a better option ifSELECTcontains multiple subqueries that retrieve the same data, as in the following example:

If that statement sounds familiar to you, it's because I took it outmy previous article in this series, which deals with subqueries.As you can see, the statement contains three subqueries, all the same, which can result in significant redundant processing depending on how the query is processed by the database engine. The following figure shows the results returned by this statement.

Instead of using subqueries, you can achieve the same results by defining a CTE that retrieves the averagemaximum weightvalue for any manufacturer. Then in your top level query you can join thatplanesCTE table with join based on vendor ID, as shown in the following example:

In this case, the CTE specifies the column names to be used for the result set, that is, thesupplier identificationThe values ​​are returned asI would likePillar. In addition, the CTE groups the data in theManufacturertable through thesupplier identificationvalues ​​and returns the averageaverage weightvalue for all.

The top-level query then follows the top-level query.planesCTE table, but limits the results to those aircraft withmaximum weightValue greater than the average weight returned by CTE. Notice that the statement now uses instead of subqueriesaverage weightSpalte vom CTE.

Again, the performance difference between these two approaches is negligible since we're working with such a small data set. Only by comparing the statements to a more realistic data set can we get a true picture of their performance differences. However, in my opinion, the CTE makes the code more readable, ie. h it is easier to follow the logic of the instruction.

Define multiple CTEs in oneCONclause

Up to this point, the examples in this article only included one CTE perCONclause, but you can define multiple CTEs and reference each of them in your top-level declaration. Just be sure to give the CTEs different names and separate them with commas. For example the followingCONclause defines three CTEs, all of which are referenced at the top levelSELECTExpression:

The three CTEs are similar, except that each extracts data based on the other.type of motorWorth. the upper levelSELECTQuery then uses theUNIONoperators to join them completely. (HeUNION ALLThe operator combines the results of severalSELECTstatements in a single result set). The following figure shows part of the results returned by this statement.

This is a fairly simple example, but it demonstrates the concept of defining multiple CTEs and referencing them in the top-level query. In this case, the three CTEs work independently, but it is not always necessary to do so. For example, himCONThe following example clause also contains three CTEs, but in this case the second CTE is (mfc_avg) refers to the first CTE (mfcs), while the third CTE (pl_avg) Being alone:

As this example shows, a CTE can reference a CTE that precedes it. However, this only works one way; a CTE cannot refer to one that follows it. In this case themfc_avgCTE agreesplanestable amfcsCTE and groups the data based on thatsupplier identificationWorth. The top-level query then retrieves data from that CTE, but only returns the rows with aavg_parkingValue greater than that returned by the averagepl_avgCTE. The following figure shows the results returned by this statement.

It should be emphasized that theWOclause in the top-level query contains a subquery that retrieves data from thepl_avgCTE. This not only points to the inherent flexibility of CTEs, but also to the fact that CTEs and subqueries are not mutually exclusive.

Working with recursive CTEs

One of the most useful aspects of the CTE is its ability to perform recursive queries. This type of CTE, known as a recursive CTE, is one that references itself within the CTE query. theCONclause in a recursive CTE must contain theRECURSIVEkeyword, and the CTE query must contain two parts separated by theUNIONOperator. The first (non-recursive) part fills an initial row of data, and the second (recursive) part does the actual recursion based on that first row. Only the recursive part can refer to the CTE itself.

To understand how this works, consider the following example, which generates a list of even numbers up to and including 20:

The name of the CTE isworktop, and only the return remainsWertPillar. The non-recursive part of the CTE query sets the value of the first row2, which is assigned to theWertPillar. The recursive part of the query retrieves the data from the CTE but increments theWertcolumn after2with each iteration. The consultation continues to increaseWertcolumn after2as long asWertit's less than20. the upper levelSELECTThe statement then retrieves the data from the CTE and returns the results shown in the following figure.

Note: Since the recursive query says < 20, you might think it wouldn't return 20 on the output. But the iteration that returns 20 occurs, but it stops iterating because the value is no longer less than 20.

When creating a recursive CTE, keep in mind that MySQL places several restrictions on the recursive part. The secondSELECTdeclaration must not contain aggregate functions, window functionsDISTINGUISHABLEsignal or theGROUP VONoSERIE VONClause.

Running a recursive query can be useful when working with hierarchical data. To demonstrate how this works, I used the following code to create and populate a table calledairline_emps, which stores the IDs and job titles of a group of fictitious employees, along with the ID of the person each reports to:

everything exceptemp_id 1(Big Boss) reports to someone else. For example, the supervisor reports to the top executive, who in turn reports to the big boss. i included oneSELECTstatement along with theINSERTIONso you can review the data after adding it to the table.

Using this data, you can now create the following recursive CTE that finds each person's position level in the organization and how that position fits into the reporting hierarchy:

Since this is a recursive CTE, it is split into two parts connected withUNION ALLOperator. The non-recursive part fills the first line based on theNULLvalue in thereported inPillar. This series is for the big boss at the top of the pecking order. The non-recursive part also assigns the value1For himemp_tierColumn and arrange theemp_idvalue in therec_routecolumn, converting the value to theCHARtype of data. The first row returned by the CTE is similar to the following figure.

The recursive part of the CTE uses an inner join to match theairline_empstable aempCTE. The union is based onreported incolumn in theairline_empstable and theemp_idcolumn in theempCTE. The join condition makes it possible to recursively traverse each level of the report hierarchy based onreported inWorth. The recursive part then increases theemp_tiercolumn after1with each new level in the hierarchy.

For example,emp_id 2(head of department) andemp_id 6(Top Executive) both report directlyemp_id 1(Big Boss), die tooemp_tierThe column for these two rows is incremented by1, resulting in a value of2For each row. This means that they are both at the second level of the employee hierarchy. The next level in the hierarchy are the people who report to the division head or senior manager, that is, theemp_tierThe column for these rows is set to3. This process continues until there are no more layers left.

During this process, therec_routeThe column is also updated on each row by concatenating theemp_idtickets to provide a representation of the reporting hierarchy. For example, himreported incolumn foremp_id 9will show that the host is in contactemp_id 8(team leader) who reports toemp_id 6(Senior Executive) who reports toemp_id 1(Big Boss), with each level separated by a slash. The following image shows the data returned by the query.

the upper levelSELECTThe statement retrieves data from the CTE only without joining other tables. The statement also includes aSERIE VON-Clause that orders the results by firstemp_tiercolumn and then through theemp_idPilar.

Using CTE with DML statements

Earlier in this article, I mentioned that you can use CTEs with statements other thanSELECT. I also explained that my focus in this article was mainly how to implement the CTE withSELECTExpression. However, I want to show you at least one of the alternative ways to give you an idea of ​​what it would look like (and to whet your appetite).

The following example shows a CTE used with aTO UPDATEInstruction to change the data in theairline_empsTable created in the previous section:

DieCON-clause and CTE work just like you've seen in other examples. The clause contains a single CTE namedrptswho calls thememp_idValues ​​for the employees they report toemp_id 8. The query returns the values.9y10.

the upper levelTO UPDATEstatement uses the data returned by the CTE to update thereported incolumn too7for these two employees. theTO UPDATEexpressionWOcontains a subquery that retrieves the data from the CTE, so the statement only updates those two rows.

After running this update statement, you can run againSELECTStatement from the previous section to verify the changes. I have included the explanation here for your convenience:

The statement returns the results shown in the following figure.

Please note that the staff9y10now shows areported invalue of7. Additionallyrec_routeThe value in each of the two rows has been updated to reflect the new reporting hierarchy.

Working with general expressions of MySQL tables

The CTE can be a powerful tool when querying and manipulating data in your MySQL databases. Recursive CTEs can be particularly useful when working with self-referential data, as the previous examples have shown. However, CTEs are not always intuitive and you should have a good understanding of how they work before adding them to your existing database code, especially if you intend to use them to manipulate data. For this reason, I recommend you check them out as well.MySQL-Documentationin CTE, along with any other resources available to you. The more time you spend learning about CTEs in advance, the more effectively you can use them in your MySQL queries.

Appendix: Preparation of the objects and demo data

For the examples in this article, I have theto traveldatabase and added theManufactureryplanesto the database. If you want to try these examples yourself, first run the following script on your MySQL instance:

After creating the database, you can add sample data to itManufacturertable by running the followingINSERTIONstatement that inserts seven lines:

i included oneSELECTstatement afterINSERTIONstatement so you can confirm that all seven rows have been added to the table. The first row of the table has asupplier identificationvalue of101, and subsequent lines are incremented by one. After you have filled theManufacturertable you can do the followingINSERTIONInstructions for adding data toplanesTisch:

DieINSERTIONstatement uses thesupplier identificationvalues ​​of theManufacturerDesk. These values ​​provide the foreign key values ​​necessary for thesupplier identificationcolumn in theplanesDesk. Also, the front row is taken1001For himplane_idvalue with whichplane_idThe values ​​for the other rows increased accordingly. As with the predecessorINSERTIONExplanation, I inserted aSELECTStatement confirming that the data has been added correctly.