Common TABLE Expressions

Subquery di klausa FROM — yang kita kenal sebagai derived table — memang ampuh, tetapi punya dua kelemahan nyata di query besar. Pertama, definisinya menempel di tengah kalimat SQL sehingga pembaca harus menyusuri tanda kurung berlapis untuk memahami maksudnya. Kedua, sebuah derived table tidak bisa dipanggil dua kali; bila hasilnya dibutuhkan di dua tempat, seluruh subquery harus ditulis ulang kata demi kata. MySQL 8 menjawab kedua kelemahan itu dengan Common Table Expression (CTE): subquery yang diberi nama lewat klausa WITH, lalu dipanggil dengan nama tersebut seperti tabel biasa. Pada chapter ini kita mempelajari bentuk dasarnya, kesetaraannya dengan derived table, CTE beruntun, pemakaian berulang, penulisan ulang subquery bersarang, dan bukti bahwa CTE hanya hidup selama satu statement.

Menyiapkan Tabel Latihan #

Kita pakai database latihan belajar_set dengan satu tabel pesanan berisi tujuh transaksi toko kopi. Nilai kolom jumlah sengaja dipilih supaya hasil agregasi mudah dihitung ulang secara manual:

sql
DROP DATABASE IF EXISTS belajar_set;
CREATE DATABASE belajar_set;
USE belajar_set;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE pesanan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan VARCHAR(50) NOT NULL,
  produk VARCHAR(50) NOT NULL,
  jumlah INT NOT NULL,
  harga DECIMAL(10,2) NOT NULL
);

INSERT INTO pesanan (pelanggan, produk, jumlah, harga) VALUES
  ('Budi', 'Kopi Gayo', 2, 55000.00),
  ('Budi', 'Teh Melati', 3, 25000.00),
  ('Siti', 'Kopi Toraja', 1, 62000.00),
  ('Siti', 'Kopi Gayo', 4, 55000.00),
  ('Andi', 'Gula Aren', 6, 15000.00),
  ('Rina', 'Teh Melati', 2, 25000.00),
  ('Dewi', 'Kopi Toraja', 3, 62000.00);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Total unit tiap pelanggan dari data ini: Budi 5, Siti 5, Andi 6, Rina 2, dan Dewi 3 — angka-angka yang akan sering muncul pada contoh berikutnya. Rata-rata total seluruh pelanggan adalah 4,2, sedikit di bawah total Budi dan Siti.

CTE: Derived Table yang Diberi Nama #

Bentuk paling sederhana dari sebuah CTE menggantikan derived table dengan definisi bernama. Sebelum versi CTE, kita menulis rekap total unit per pelanggan sebagai subquery di klausa FROM:

sql
SELECT pelanggan, total_unit
FROM (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM pesanan
  GROUP BY pelanggan
) AS rekap
ORDER BY pelanggan;

Output yang Dihasilkan:

text
+-----------+------------+
| pelanggan | total_unit |
+-----------+------------+
| Andi      |          6 |
| Budi      |          5 |
| Dewi      |          3 |
| Rina      |          2 |
| Siti      |          5 |
+-----------+------------+
5 rows in set (0.00 sec)

Versi CTE-nya memindahkan subquery ke depan dan memberinya nama lewat klausa WITH:

sql
WITH rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM pesanan
  GROUP BY pelanggan
)
SELECT pelanggan, total_unit
FROM rekap
ORDER BY pelanggan;

Output yang Dihasilkan:

text
+-----------+------------+
| pelanggan | total_unit |
+-----------+------------+
| Andi      |          6 |
| Budi      |          5 |
| Dewi      |          3 |
| Rina      |          2 |
| Siti      |          5 |
+-----------+------------+
5 rows in set (0.00 sec)

Kedua query menghasilkan lima baris yang persis sama. Mari kita bedah baris per baris versi CTE-nya: kata kunci WITH membuka statement dan memberi sinyal bahwa definisi bernama akan menyusul, lalu rekap AS (...) menetapkan nama rekap untuk hasil subquery di dalam tanda kurung. Query utama SELECT ... FROM rekap kemudian memanggil rekap seolah-olah nama tabel sungguhan — tidak ada lagi tanda kurung berlapis di klausa FROM. Nama CTE sendiri sebaiknya deskriptif seperti rekap atau pesanan_bersih karena nama itulah yang dibaca berulang kali oleh siapa pun yang menyusuri query kita.

Mengganti Subquery Bersarang #

Manfaat CTE paling terasa saat menulis ulang query bertingkat yang panjang. Misalnya kita ingin daftar pelanggan yang total unitnya di atas rata-rata total semua pelanggan. Versi subquery bersarang memaksa agregasi yang sama ditulis dua kali:

sql
SELECT pelanggan, total_unit
FROM (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM pesanan
  GROUP BY pelanggan
) AS rekap
WHERE total_unit > (
  SELECT AVG(total_unit)
  FROM (
    SELECT SUM(jumlah) AS total_unit
    FROM pesanan
    GROUP BY pelanggan
  ) AS rekap2
)
ORDER BY pelanggan;

Output yang Dihasilkan:

text
+-----------+------------+
| pelanggan | total_unit |
+-----------+------------+
| Andi      |          6 |
| Budi      |          5 |
| Siti      |          5 |
+-----------+------------+
3 rows in set (0.00 sec)

Query di atas memang benar, tetapi pembaca harus menyusuri tiga lapis tanda kurung dan memastikan rekap dengan rekap2 memang definisi yang sama. Versi CTE menuliskan agregasi itu satu kali, lalu memanggilnya di dua tempat:

sql
WITH rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM pesanan
  GROUP BY pelanggan
)
SELECT pelanggan, total_unit
FROM rekap
WHERE total_unit > (SELECT AVG(total_unit) FROM rekap)
ORDER BY pelanggan;

Output yang Dihasilkan:

text
+-----------+------------+
| pelanggan | total_unit |
+-----------+------------+
| Andi      |          6 |
| Budi      |          5 |
| Siti      |          5 |
+-----------+------------+
3 rows in set (0.00 sec)

Kedua versi mengembalikan Andi (6), Budi (5), dan Siti (5) — tiga pelanggan di atas rata-rata 4,2. Bedah versi CTE-nya: subquery skalar (SELECT AVG(total_unit) FROM rekap) menghitung rata-rata dari CTE yang sama yang dipakai klausa FROM, sehingga tidak ada duplikasi definisi. Perubahan definisi rekap cukup dilakukan di satu tempat bila aturan bisnisnya berganti. Inilah alasan utama tim basis data besar menjadikan CTE standar penulisan query analitik.

Beberapa CTE Beruntun #

Satu statement boleh mendefinisikan lebih dari satu CTE — cukup dipisah tanda koma, dan CTE berikutnya boleh membaca CTE sebelumnya. Pola ini memecah transformasi data menjadi tahapan yang terbaca dari atas ke bawah:

sql
WITH besar AS (
  SELECT pelanggan, produk, jumlah
  FROM pesanan
  WHERE jumlah >= 3
),
rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM besar
  GROUP BY pelanggan
)
SELECT pelanggan, total_unit
FROM rekap
ORDER BY pelanggan;

Output yang Dihasilkan:

text
+-----------+------------+
| pelanggan | total_unit |
+-----------+------------+
| Andi      |          6 |
| Budi      |          3 |
| Dewi      |          3 |
| Siti      |          4 |
+-----------+------------+
4 rows in set (0.00 sec)

Mari kita uraikan alirannya: CTE pertama besar menyaring pesanan dan hanya menyimpan transaksi berjumlah tiga unit ke atas, sehingga tersisa empat baris — Teh Melati Budi (3), Kopi Gayo Siti (4), Gula Aren Andi (6), dan Kopi Toraja Dewi (3). CTE kedua rekap membaca besar — bukan tabel asli — lalu menjumlahkan per pelanggan menjadi Andi 6, Budi 3, Dewi 3, dan Siti 4. Query utama akhirnya tinggal memilih kolom dan mengurutkan. Urutan definisi penting: rekap harus dideklarasikan setelah besar karena rekap membacanya; MySQL memproses definisi dari kiri ke kanan.

CTE Bisa Dipakai Berulang #

Pemakaian berulang dalam satu statement adalah keunggulan yang tidak dimiliki derived table. Contoh berikut memakai CTE rekap dua kali: sekali sebagai sumber utama, sekali di dalam subquery skalar untuk menghitung total keseluruhan:

sql
WITH rekap AS (
  SELECT pelanggan, SUM(jumlah) AS total_unit
  FROM pesanan
  GROUP BY pelanggan
)
SELECT
  pelanggan,
  total_unit,
  ROUND(100 * total_unit / (SELECT SUM(total_unit) FROM rekap), 1) AS persen
FROM rekap
ORDER BY total_unit DESC, pelanggan;

Output yang Dihasilkan:

text
+-----------+------------+--------+
| pelanggan | total_unit | persen |
+-----------+------------+--------+
| Andi      |          6 |   28.6 |
| Budi      |          5 |   23.8 |
| Siti      |          5 |   23.8 |
| Dewi      |          3 |   14.3 |
| Rina      |          2 |    9.5 |
+-----------+------------+--------+
5 rows in set (0.00 sec)

Setiap pelanggan kini tampil dengan kontribusi persentase terhadap 21 unit total: Andi 28,6%, Budi dan Siti masing-masing 23,8%, Dewi 14,3%, dan Rina 9,5%. Versi derived table untuk query yang sama harus menyalin subquery rekap dua kali — persis seperti contoh subquery bersarang di atas. Cukup satu kesalahan ketik di salah satu salinan untuk membuat kedua angka tidak sinkron, dan bug semacam itu nyaris tak terlihat saat review. Satu definisi berarti satu sumber kebenaran.

CTE Hanya Hidup Satu Statement #

Batasan terpenting CTE: nama yang didefinisikan WITH hanya berlaku sampai statement itu selesai. Nama CTE bukan tabel dan bukan view — mari kita buktikan dengan menjalankan dua statement berurutan:

sql
-- @error
WITH angka AS (SELECT 1 AS x) SELECT * FROM angka;
SELECT * FROM angka;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

+---+
| x |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

ERROR 1146 (42S02): Table 'belajar_set.angka' doesn't exist
Bye

Statement pertama berhasil mengembalikan satu baris berisi 1, lalu statement kedua ditolak dengan ERROR 1146 karena tabel angka tidak pernah benar-benar dibuat — nama itu sudah kedaluwarsa begitu statement pertama tuntas. Setelah baris ; penutup, nama CTE lenyap dari pandangan server. Bila hasil sebuah query perlu dipakai berulang lintas statement, aplikasi, atau pelaporan, yang kita butuhkan justru objek yang tersimpan di database seperti VIEW — pembahasannya menunggu section tersendiri di akhir course ini.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

sql
DROP DATABASE belajar_set;

Output yang Dihasilkan:

text

Query OK, 1 row affected (0.00 sec)

Referensi #

Kita kini bisa memberi nama pada subquery dengan WITH, merangkai beberapa CTE menjadi tahapan transformasi, dan memakai satu CTE berulang kali tanpa menyalin definisinya. Semua contoh sejauh ini memproses data yang sudah tersedia di tabel. Chapter berikutnya mengangkat kemampuan CTE satu tingkat lagi: WITH RECURSIVE, bentuk CTE yang menghasilkan baris baru dari baris hasilnya sendiri — mulai dari deret angka klasik hingga penelusuran hierarki manajer dan pegawai.