Common Table Expression (CTE) di MySQL 8

Query yang sulit dibaca tanpa CTE

Subquery bersarang yang dalam sering membuat query SQL sulit ditelusuri urutannya. Common Table Expression (CTE) memberi nama pada setiap langkah sehingga query bisa dibaca dari atas ke bawah.

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;

CTE rekursif

MySQL 8 juga mendukung CTE rekursif, berguna untuk data berjenjang seperti struktur kategori atau organisasi.

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;

Bagian pertama UNION ALL adalah titik awal pencarian, sedangkan bagian kedua diulang sampai tidak ada baris baru yang cocok.

CTE bukan selalu lebih cepat

CTE terutama membantu keterbacaan. Di MySQL, CTE non-rekursif umumnya dieksekusi mirip subquery biasa, jadi manfaat utamanya adalah struktur kode, bukan otomatis lebih cepat.

Latihan

Ubah salah satu subquery bersarang yang ada di aplikasi Anda menjadi bentuk CTE, lalu bandingkan tingkat keterbacaannya.