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.
Recursive and non-recursive CTE will be looked into in the following sections.
A Common table expression (CTE) is just like a derived table, but its declaration is put before the query block instead of in FROM
clause.
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)...