Master MySQL 8 CTEs: Syntax, Benefits, and Real‑World Examples

Introduction

MySQL 8 has been officially released with many excellent features, and this article focuses on Common Table Expressions (CTE).

What is a CTE

A derived table is a subquery used in the FROM clause. A CTE works like a derived table but is declared before the main query block using WITH. SELECT ... FROM (subquery) AS derived, t1 ... Example of a CTE:

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

CTE Syntax

Simple examples:

WITH qn AS (SELECT a FROM t1)
SELECT * from qn;

WITH qn AS (SELECT a+2 AS a, b FROM t1)
UPDATE t1, qn SET t1.a=qn.a + 10 WHERE t1.a - qn.a = 0; 

WITH qn(a, b) AS (SELECT a+2, b FROM t2)
DELETE t1 FROM t1, qn WHERE t1.a - qn.a = 0;

INSERT INTO t2
WITH qn AS (SELECT 10*a AS a FROM t1) 
SELECT * from qn;

SELECT * FROM t1 WHERE t1.a IN
(WITH cte as (SELECT * FROM t1 AS t2 LIMIT 1)
SELECT a + 0 FROM cte);

Benefits of CTEs

CTEs have four clear advantages over derived tables:

(1) Better readability

Derived table style:

SELECT ...
FROM t1 LEFT JOIN ((SELECT ... FROM ...) AS dt JOIN t2 ON ...) ON ...

CTE style:

WITH dt AS (SELECT ... FROM ...)
SELECT ...
FROM t1 LEFT JOIN (dt JOIN t2 ON ...) ON ...

(2) Reusable multiple times

Derived tables cannot be referenced twice, while a CTE can be used in multiple places.

WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) 
SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;

(3) Can reference other CTEs

CTEs can be nested, allowing one CTE to depend on another.

WITH d1 AS (SELECT ... FROM ...), 
     d2 AS (SELECT ... FROM d1 ...)
SELECT
FROM d1, d2 ...

(4) Performance improvement

Derived tables are materialized each time they appear, consuming more space and time. A CTE is materialized only once regardless of how many times it is referenced.

Examples

(1) Generate numbers 1‑10

Recursive CTE to produce a sequence:

WITH RECURSIVE my_cte AS (
  SELECT 1 AS n
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<10
)
SELECT * FROM my_cte;

Explanation of the steps and the resulting rows is provided.

(2) Create a table using a CTE

USE test;
CREATE TABLE numbers
  WITH RECURSIVE my_cte(n) AS (
    SELECT 1
    UNION ALL
    SELECT 1+n FROM my_cte WHERE n<6
  )
  SELECT * FROM my_cte;

(3) Use CTE in INSERT

INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS (
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;

(4) Use CTE in UPDATE

WITH RECURSIVE my_cte(n) AS (
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
SET numbers.n=0
WHERE numbers.n=my_cte.n*my_cte.n;

(5) Use CTE in DELETE

DELETE FROM numbers 
WHERE numbers.n > (
  WITH RECURSIVE my_cte(n) AS (
    SELECT 1
    UNION ALL
    SELECT 1+n FROM my_cte WHERE n<6
  )
  SELECT AVG(n)/2 FROM my_cte
);

Setting Up MySQL 8 with Docker

I use Docker to run MySQL 8.0.11, which is the simplest method.

After starting the container, the connection error may appear:

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

The issue is caused by MySQL 8's default authentication plugin. Resolve it by specifying the native password plugin when launching the container:

docker run -p 3306:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=111111 -d mysql:8.0.11 --default-authentication-plugin=mysql_native_password

Then you can log in using:

docker run -it --link mysql8:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'