Common table expressions (CTE)

MySQL 8 supports common table expressions, both non-recursive and recursive.

Common table expressions enable the use of named temporary result sets, implemented by permitting a WITH clause preceding SELECT statements and certain other statements.

Note

Why do you need CTEs?It is not possible to refer to a derived table twice in the same query. So the derived tables are evaluated twice or as many times as referred, which indicates a serious performance problem. Using CTE, the subquery is evaluated only once.

How to do it...

Recursive and non-recursive CTE will be looked into in the following sections.

Non-recursive CTE

A Common table expression (CTE) is just like a derived table, but its declaration is put before the query block instead of in FROMclause.

Derived Table

SELECT... FROM (subquery) AS derived, t1 ...

CTE

SELECT... WITH derived AS (subquery) SELECT ... FROM derived, t1 ...

A CTE may precede SELECT/UPDATE/DELETE, including subqueries WITH derived AS (subquery)...