KAL
Menu

CTE

Klausa WITH untuk memecah query rumit menjadi langkah yang terbaca.

Daftar isi

Topik ini membahas common table expression (CTE), yaitu nama sementara untuk sebuah query bantu yang dideklarasikan melalui klausa WITH pada MySQL 8.0. Pembahasan dimulai dari masalah yang diselesaikan CTE, dilanjutkan sintaks dasar, penggabungan beberapa CTE dalam satu pernyataan, dan diakhiri perbandingan dengan subquery serta tabel sementara. Setelah mengikuti topik ini, query analitik bertingkat dapat ditulis sebagai rangkaian langkah yang terbaca dari atas ke bawah, bukan sebagai satu subquery yang bersarang dalam-dalam.

Masalah subquery yang bersarang#

Query analitik sering menuntut perhitungan bertingkat. Contoh pada basis data perpustakaan: cari anggota yang jumlah peminjamannya berada di atas rata-rata. Pertanyaan itu membutuhkan dua tahap, yaitu menghitung total peminjaman setiap anggota, lalu menghitung rata-rata dari total tersebut. Dengan subquery, kedua tahap itu dilipat ke dalam satu pernyataan SELECT yang padat.

Jalankan persiapan berikut, kemudian amati bagaimana versi subquery terbaca.

sql persiapan-cte.sql 
-- tidak-dijalankan
-- Skrip persiapan topik CTE: basis data perpustakaan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL
);

CREATE TABLE buku (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  judul   VARCHAR(200) NOT NULL,
  penulis VARCHAR(100) NOT NULL,
  stok    INT NOT NULL DEFAULT 0
);

CREATE TABLE peminjaman (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id      INT NOT NULL,
  buku_id         INT NOT NULL,
  tanggal_pinjam  DATE NOT NULL,
  jatuh_tempo     DATE NOT NULL,
  tanggal_kembali DATE NULL,
  FOREIGN KEY (anggota_id) REFERENCES anggota(id),
  FOREIGN KEY (buku_id) REFERENCES buku(id)
);

INSERT INTO anggota (nama) VALUES
  ('Ali'), ('Budi'), ('Citra'), ('Dewi'), ('Eka');

INSERT INTO buku (judul, penulis, stok) VALUES
  ('Dasar Astronomi untuk Pemula', 'Hartono', 3),
  ('Sejarah Maritim Nusantara', 'Wijaya', 2),
  ('Panduan Menanam Sayuran', 'Santoso', 4),
  ('Pengantar Fisika Klasik', 'Kusuma', 1),
  ('Cerita Rakyat Nusantara', 'Lestari', 3),
  ('Ilmu Gizi Sehari-hari', 'Pratiwi', 2);

INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, jatuh_tempo, tanggal_kembali) VALUES
  (1, 1, '2026-05-02', '2026-05-16', '2026-05-15'),
  (1, 2, '2026-05-02', '2026-05-16', '2026-05-14'),
  (1, 3, '2026-06-01', '2026-06-15', '2026-06-20'),
  (2, 4, '2026-05-10', '2026-05-24', '2026-05-22'),
  (2, 5, '2026-06-03', '2026-06-17', NULL),
  (2, 6, '2026-06-03', '2026-06-17', '2026-06-10'),
  (3, 1, '2026-05-20', '2026-06-03', '2026-06-01'),
  (3, 4, '2026-06-05', '2026-06-19', NULL),
  (4, 2, '2026-05-25', '2026-06-08', '2026-06-07'),
  (4, 3, '2026-06-12', '2026-06-26', NULL),
  (5, 4, '2026-06-14', '2026-06-28', '2026-06-27'),
  (5, 5, '2026-06-18', '2026-07-02', NULL),
  (3, 6, '2026-07-01', '2026-07-15', NULL);
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < persiapan-cte.sql
sql Contoh: anggota di atas rata-rata peminjaman dengan subquery 
-- tidak-dijalankan
SELECT nama
FROM anggota
WHERE (SELECT COUNT(*) FROM peminjaman
       WHERE anggota_id = anggota.id) > (
  SELECT AVG(total) FROM (
    SELECT COUNT(*) AS total FROM peminjaman GROUP BY anggota_id
  ) AS rekap
);
bash 
# versi subquery bersarang
mysql -t -u root perpustakaan <<'SQL'
SELECT nama
FROM anggota
WHERE (SELECT COUNT(*) FROM peminjaman
       WHERE anggota_id = anggota.id) > (
  SELECT AVG(total) FROM (
    SELECT COUNT(*) AS total FROM peminjaman GROUP BY anggota_id
  ) AS rekap
);
SQL
text Keluaran 
+-------+
| nama  |
+-------+
| Ali   |
| Budi  |
| Citra |
+-------+

Hasilnya benar, tetapi struktur pernyataannya menuntut pembacaan dari dalam ke luar. Tahap agregasi pertama tersembunyi di bagian terdalam, tahap rata-rata berada di tengah, dan saringan akhir ada di lapisan paling luar. Semakin banyak tahap yang dibutuhkan, semakin sulit melacak kolom mana yang dipakai oleh tahap berikutnya.

Struktur WITH dan CTE pertama#

CTE mendeklarasikan tahap-tahap perhitungan secara eksplisit dan berurutan. Sintaksnya berupa WITH nama_cte AS (subquery) yang ditempatkan sebelum pernyataan utama. Di dalam pernyataan utama, nama tersebut dipakai seperti tabel biasa. Sebuah CTE hanya hidup selama pernyataan yang mendeklarasikannya dijalankan, dan nama itu tidak dapat dipanggil dari pernyataan lain.

sql Bentuk umum klausa WITH 
-- tidak-dijalankan
WITH nama_cte AS (
  SELECT ...   -- tahap pertama: query bantu
)
SELECT ...      -- pernyataan utama memakai nama_cte seperti tabel
FROM nama_cte;

Query yang sama tentang anggota di atas rata-rata ditulis ulang menjadi dua langkah bernama. Langkah pertama, jumlah_pinjam, menghitung total peminjaman setiap anggota. Langkah kedua, pernyataan utama, membandingkan total setiap anggota dengan rata-rata yang diambil dari CTE yang sama.

bash 
# keputusan yang sama ditulis dengan CTE
mysql -t -u root perpustakaan <<'SQL'
WITH jumlah_pinjam AS (
  SELECT anggota_id, COUNT(*) AS total_pinjam
  FROM peminjaman
  GROUP BY anggota_id
)
SELECT a.nama, j.total_pinjam
FROM jumlah_pinjam AS j
JOIN anggota AS a ON a.id = j.anggota_id
WHERE j.total_pinjam > (SELECT AVG(total_pinjam) FROM jumlah_pinjam)
ORDER BY j.total_pinjam DESC, a.nama;
SQL
text Keluaran 
+-------+--------------+
| nama  | total_pinjam |
+-------+--------------+
| Ali   |            3 |
| Budi  |            3 |
| Citra |            3 |
+-------+--------------+

Alur pernyataan kini mengikuti urutan berpikirnya. Pembaca menemukan definisi data terlebih dahulu di bagian atas, baru kemudian melihat pemakaian data itu di bawahnya. Perhatikan pula bahwa CTE jumlah_pinjam dirujuk dua kali, yaitu pada FROM dan pada subquery rata-rata, tanpa perlu menulis ulang definisinya.

Beberapa CTE dalam satu pernyataan#

Klausa WITH menerima lebih dari satu CTE sekaligus. Setiap CTE dipisahkan koma, dan CTE yang berada di belakang boleh merujuk CTE yang sudah dideklarasikan di depannya. Dengan pola ini, perhitungan bertingkat disusun seperti rantai langkah yang tiap mata rantainya bernama jelas.

Contoh berikut menanyakan jumlah peminjaman yang masih aktif untuk setiap buku. CTE pertama, pinjam_aktif, mengumpulkan peminjaman yang belum dikembalikan. CTE kedua, aktif_per_buku, merangkum hasil CTE pertama per buku, lalu pernyataan utama menambahkan judulnya.

bash 
# dua CTE berantai dalam satu pernyataan
mysql -t -u root perpustakaan <<'SQL'
WITH pinjam_aktif AS (
  SELECT buku_id
  FROM peminjaman
  WHERE tanggal_kembali IS NULL
),
aktif_per_buku AS (
  SELECT buku_id, COUNT(*) AS jumlah_aktif
  FROM pinjam_aktif
  GROUP BY buku_id
)
SELECT b.judul, ab.jumlah_aktif
FROM aktif_per_buku AS ab
JOIN buku AS b ON b.id = ab.buku_id
ORDER BY ab.jumlah_aktif DESC, b.judul;
SQL
text Keluaran 
+-------------------------+--------------+
| judul                   | jumlah_aktif |
+-------------------------+--------------+
| Cerita Rakyat Nusantara |            2 |
| Ilmu Gizi Sehari-hari   |            1 |
| Panduan Menanam Sayuran |            1 |
| Pengantar Fisika Klasik |            1 |
+-------------------------+--------------+

Membaca pernyataan ini tidak memerlukan pelacakan arah ke dalam seperti pada subquery bersarang. Definisi aktif_per_buku yang berdiri di atas hasil pinjam_aktif juga menegaskan urutan penyusunan data secara harfiah.

Penamaan kolom pada CTE#

Kolom hasil CTE mengambil nama kolom dari subquerynya. Apabila sebuah kolom merupakan hasil ekspresi tanpa alias, MySQL akan mengganti namanya secara otomatis dan keterbacaan menurun. Klausa WITH menyediakan daftar kolom eksplisit untuk keadaan itu, yaitu WITH nama_cte (kolom_a, kolom_b) AS (...) yang mengganti nama kolom hasil subquery secara berurutan.

bash 
# CTE dengan daftar kolom eksplisit
mysql -t -u root perpustakaan <<'SQL'
WITH rekap (nama_anggota, total_pinjam) AS (
  SELECT a.nama, COUNT(*)
  FROM anggota AS a
  JOIN peminjaman AS p ON p.anggota_id = a.id
  GROUP BY a.nama
)
SELECT nama_anggota, total_pinjam
FROM rekap
ORDER BY total_pinjam DESC, nama_anggota;
SQL
text Keluaran 
+--------------+--------------+
| nama_anggota | total_pinjam |
+--------------+--------------+
| Ali          |            3 |
| Budi         |            3 |
| Citra        |            3 |
| Dewi         |            2 |
| Eka          |            2 |
+--------------+--------------+

Pemilihan antara CTE dan pendekatan lain#

CTE, subquery, dan tabel sementara (temporary table) sama-sama menyimpan hasil query untuk dipakai tahap berikutnya, tetapi rentang hidup dan kemampuannya berbeda. Tabel berikut merangkum perbedaannya.

AspekSubquery dalam FROMCTETabel sementara
Rentang hidupsatu pernyataansatu pernyataansampai sesi berakhir atau dihapus
Dipakai ulangditulis ulang di tiap tempatdirujuk bebas dalam satu pernyataandirujuk dari banyak pernyataan
Dapat diberi indekstidaktidakdapat
Versi MySQL minimumsejak lama8.0sejak lama

Ketiga cara itu bukan saling menggantikan, melainkan saling melengkapi. CTE adalah pilihan utama bila langkah perhitungan hanya dibutuhkan satu pernyataan dan keterbacaan menjadi prioritas. Tabel sementara lebih cocok bila hasil perantara dipakai oleh banyak pernyataan atau membutuhkan indeks tersendiri.

Kesalahan umum#

Nama kolom di dalam satu CTE wajib unik. Pernyataan berikut memakai alias a dua kali sehingga server menolaknya. Perbaikannya adalah memberi alias yang berbeda untuk setiap kolom.

bash 
# nama kolom ganda dalam satu CTE ditolak server
mysql -u root perpustakaan -e "WITH x AS (SELECT id AS a, nama AS a FROM anggota) SELECT * FROM x;"
text Keluaran 
ERROR 1060 (42S21) at line 1: Duplicate column name 'a'

Kesalahan kedua adalah memperlakukan CTE seolah-olah tabel yang tetap ada. Pernyataan berikut mendeklarasikan CTE lalu, pada pernyataan kedua yang terpisah, mencoba membacanya. Server tidak mengenal nama itu lagi karena CTE hanya hidup di dalam pernyataan yang mendeklarasikannya.

bash 
# CTE tidak dapat dibaca dari pernyataan lain
mysql -t -u root perpustakaan -e "WITH jumlah_pinjam AS (SELECT anggota_id, COUNT(*) AS total FROM peminjaman GROUP BY anggota_id) SELECT COUNT(*) AS jumlah_anggota FROM jumlah_pinjam; SELECT COUNT(*) AS baris FROM jumlah_pinjam;"
text Keluaran 
+----------------+
| jumlah_anggota |
+----------------+
|              5 |
+----------------+
ERROR 1146 (42S02) at line 1: Table 'perpustakaan.jumlah_pinjam' doesn't exist

Apabila hasil perantara memang harus dipakai oleh beberapa pernyataan, simpan sebagai tabel sementara atau sebagai view yang didefinisikan sekali.

Ringkasan#

  • CTE adalah nama sementara bagi sebuah query bantu pada klausa WITH, dan hanya hidup selama pernyataan yang mendeklarasikannya.
  • Beberapa CTE dapat dirantai dalam satu pernyataan, dan CTE di belakang boleh merujuk CTE di depannya.
  • Daftar kolom eksplisit pada WITH mengganti nama kolom hasil subquery, sehingga CTE yang mengandung ekspresi tetap memiliki nama kolom yang jelas.
  • Untuk hasil perantara yang dipakai lintas pernyataan atau butuh indeks, tabel sementara lebih tepat daripada CTE.

Referensi#