MySQL 8 Cookbook

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.

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.