O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required