MySQL 8 Cookbook

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 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), for example:

DELETE FROM t1 WHERE t1.a IN (SELECT b FROM derived);

Suppose you want to find out the percentage increase in the salary of each year with respect to the previous year. Without CTE, you need to write two subqueries, and they are essentially the same. MySQL is not smart enough to detect that and the subqueries are executed twice:

mysql> SELECT ...