Common Table Expressions in MySQL 8
It is not uncommon that a single SQL statement such as a SELECT statement includes sub-SQL SELECT statements, or sub-queries, that generate a sub-result set within the top-level statement. If these sub-queries are kept as such, using their result set within the top-level query would involve rather complex SQL. Common Table Expressions are a new feature in MySQL 8 meant for such a use. A Common Table Expression (CTE) is:
- Named,
- A temporary result set, and
- Generated by a subquery within an outer SQL query statement
A CTE exists only within the scope of the single top-level SQL query statement, and can be used multiple times within the top-level statement.
The WITH Clause
The new WITH clause has been introduced for the CTE feature. When using a WITH clause, it may declare one or more sub-clauses with each sub-clause associated with a subquery and a CTE name for the result set from the subquery.
Top-Level CTE Syntax
The top-level CTE syntax makes it more readable. The WITH clause does not have to be used within other SQL statements, and a top-level SQL may start with a WITH clause. The CTE names may be referred/used in subsequent SQL statements and within other CTEs. A typical CTE syntax would be WITH cte1_name AS (SELECT col1, col2 FROM table_1), cte2_name AS (SELECT col3, col4 FROM table_2) SELECT col1 from cte1_name;
CTE Is DML
A CTE is an optional construct within DML statement syntax. A CTE is not DDL because it does not define any new schema object. When used in a SELECT statement, a CTE’s column names are derived from the SELECT list of the first SELECT within the AS(subquery) clause. A CTE may optionally be declared with explicit column names. CTEs may be joined using a JOIN clause.
Recursive CTE
By default a CTE is non-recursive, implying that it does not refer to itself. A CTE may be made recursive by referring to itself. A recursive CTE must use the RECURSIVE keyword. Recursive CTEs could be used for hierarchical data traversal.
How Is CTE Different from a Derived Table?
A CTE is very similar to a derived table. A derived table is a named expression, or table, to refer to the result set of a subquery. If similar, why use CTEs at all? CTEs have some advantages over derived tables. A CTE can be used/referred multiple times within a single statement, whereas a derived table can be referred only once. A CTE has other benefits of being able to refer to other CTEs and itself (recursive CTE), not to mention the top-level syntax for a CTE.
Common Table Expressions (CTEs) are a new feature in MySQL 8.0 to simplify SQL code related to result sets generated by subqueries, offering a better alternative to derived tables.
