| 3 | 4 | | 5 | 6 | | 10 | 15 | | 20 | 25 | +----------+----------+ 

VALUES  can also be used in unions with SELECT  statements, TABLE  statements, or both. 

The constructed tables in the UNION  must contain the same number of columns, just as if you were using SELECT . See Section 13.2.10.3, “UNION Clause” , for further examples. 

•In joins. See Section 13.2.10.2, “JOIN Clause” , for more information and examples. 

•In place of VALUES()  in an INSERT  or REPLACE  statement, in which case its semantics differ slightly from what is described here. See Section 13.2.6, “INSERT Statement” , for details. 

•In place of the source table in CREATE TABLE ... SELECT  and CREATE VIEW ... SELECT . See the descriptions of these statements for more information and examples. 

## 13.2.15 WITH (Common Table Expressions) 

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following discussion describes how to write statements that use CTEs. 

•Common Table Expressions 

•Recursive Common Table Expressions 

•Limiting Common Table Expression Recursion 

•Recursive Common Table Expression Examples 

•Common Table Expressions Compared to Similar Constructs 

For information about CTE optimization, see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” . 

## Additional Resources 

These articles contain additional information about using CTEs in MySQL, including many examples: 

•MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs) 

•MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs), Part Two – how to generate series 

•MySQL 8.0 Labs: [Recursive] Common Table Expressions in MySQL (CTEs), Part Three – hierarchies 

•MySQL 8.0.1: [Recursive] Common Table Expressions in MySQL (CTEs), Part Four – depth-first or breadth-first traversal, transitive closure, cycle avoidance 

## Common Table Expressions 

To specify common table expressions, use a WITH  clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery. The following example defines CTEs named cte1  and cte2  in the WITH  clause, and refers to them in the top-level SELECT  that follows the WITH  clause: 

