Common Table Expression (CTE) in MySQL 8

Queries that are difficult to read without CTE

Deep nested subqueries often make SQL queries difficult to trace in order. Common Table Expression (CTE) names each step so that the query can be read from top to bottom.

WITH pesanan_bulan_ini AS (
    SELECT pelanggan_id, SUM(total) AS total_belanja
    FROM pesanan
    WHERE dibuat_pada >= DATE_FORMAT(NOW(), '%Y-%m-01')
    GROUP BY pelanggan_id
),
pelanggan_aktif AS (
    SELECT pelanggan_id, total_belanja
    FROM pesanan_bulan_ini
    WHERE total_belanja >= 500000
)
SELECT p.nama, pa.total_belanja
FROM pelanggan_aktif pa
JOIN pelanggan p ON p.id = pa.pelanggan_id
ORDER BY pa.total_belanja DESC;

Recursive CTE

MySQL 8 also supports recursive CTE, useful for tiered data such as category structures or organizations.

WITH RECURSIVE bawahan AS (
    SELECT id, nama, atasan_id
    FROM pegawai
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.nama, e.atasan_id
    FROM pegawai e
    JOIN bawahan b ON e.atasan_id = b.id
)
SELECT * FROM bawahan;

Part One UNION ALL is the starting point of the search, while the second part is repeated until no new line matches.

CTE is not always faster

CTE mainly aids readability. In MySQL, non-recursive CTE is generally executed like a regular subquery, so the main benefit is the code structure, not the faster auto.

practice;

Convert one of your app's nested subqueries into CTE, then compare its readability.