Subquery yang bersarang dua-tiga tingkat cepat menjadi sulit dibaca: pembaca harus mencocokkan tanda kurung dari ujung ke ujung untuk tahu bagian mana yang dihitung lebih dulu. Common Table Expression (CTE) membalik penulisanannya — beri nama pada tiap subquery di depan, lalu susun query utama di atas nama-nama itu seperti merangkai pipeline. Kita akan memakai CTE untuk merapikan query laporan domain toko, lalu bertemu bentuk spesialnya: WITH RECURSIVE yang bisa membangkitkan baris yang tidak ada di database sama sekali.

Data Contoh #

Empat tabel domain toko dipakai sepanjang chapter ini:

sql 
CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  kota TEXT
);
CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  harga INTEGER NOT NULL,
  stok INTEGER
);
CREATE TABLE pesanan (
  id INTEGER PRIMARY KEY,
  pelanggan_id INTEGER REFERENCES pelanggan(id),
  tanggal TEXT NOT NULL
);
CREATE TABLE detail_pesanan (
  pesanan_id INTEGER REFERENCES pesanan(id),
  produk_id INTEGER REFERENCES produk(id),
  jumlah INTEGER NOT NULL,
  PRIMARY KEY (pesanan_id, produk_id)
);

INSERT INTO pelanggan (id, nama, kota) VALUES
  (1, 'Budi', 'Jakarta'), (2, 'Siti', 'Bandung'), (3, 'Ani', NULL),
  (4, 'Rina', 'Surabaya'), (5, 'Dewi', 'Jakarta');
INSERT INTO produk (id, nama, harga, stok) VALUES
  (1, 'Kopi Gayo', 45000, 20), (2, 'Teh Melati', 18000, 35),
  (3, 'Gula Aren', 25000, 15), (4, 'Sirup Vanila', 25000, NULL),
  (5, 'Kopi Toraja', 52000, 10), (6, 'Teh Hijau', 22000, 0),
  (7, 'Cangkir Keramik', 35000, 8);
INSERT INTO pesanan (id, pelanggan_id, tanggal) VALUES
  (1, 1, '2025-06-01'), (2, 2, '2025-06-03'), (3, 1, '2025-06-07'),
  (4, 3, '2025-06-10'), (5, 2, '2025-06-15'), (6, 1, '2025-07-02');
INSERT INTO detail_pesanan (pesanan_id, produk_id, jumlah) VALUES
  (1, 1, 2), (1, 3, 1), (2, 2, 3), (3, 1, 1),
  (3, 4, 2), (4, 5, 1), (5, 6, 2), (5, 3, 2), (6, 1, 1);

WITH Satu Nama #

Klausa WITH nama AS (subquery) mendefinisikan hasil antara yang bisa dipakai seperti tabel biasa oleh query utama. Kita mulai dari laporan unit terjual per produk — subquery agregatnya diberi nama terjual:

sql 
WITH terjual AS (
  SELECT produk_id, SUM(jumlah) AS unit
  FROM detail_pesanan
  GROUP BY produk_id
)
SELECT p.nama, t.unit
FROM produk p
JOIN terjual t ON t.produk_id = p.id
ORDER BY t.unit DESC, p.nama;

Output yang Dihasilkan:

text 
Kopi Gayo|4
Gula Aren|3
Teh Melati|3
Sirup Vanila|2
Teh Hijau|2
Kopi Toraja|1

Alur bacanya kini dari atas ke bawah: definisikan terjual, lalu gabungkan dengan produk. CTE juga punya sifat yang membedakannya dari tabel biasa — LEFT JOIN dari produk akan memunculkan kembali Cangkir Keramik dengan NULL, sehingga kita sengaja memakai JOIN untuk laporan “yang terjual saja”.

Pipeline Beberapa CTE #

Beberapa CTE boleh didefinisikan sekaligus, dipisah koma; CTE berikutnya boleh memakai CTE sebelumnya — persis seperti merangkai tahapan pipeline. Laporan nilai pesanan di bawah melalui dua tahap: hitung nilai tiap baris detail, lalu jumlahkan per pesanan:

sql 
WITH item AS (
  SELECT d.pesanan_id, d.jumlah * p.harga AS nilai
  FROM detail_pesanan d
  JOIN produk p ON p.id = d.produk_id
),
per_pesanan AS (
  SELECT pesanan_id, SUM(nilai) AS total
  FROM item
  GROUP BY pesanan_id
)
SELECT o.id AS pesanan, pl.nama AS pelanggan, pp.total AS total
FROM per_pesanan pp
JOIN pesanan o ON o.id = pp.pesanan_id
JOIN pelanggan pl ON pl.id = o.pelanggan_id
ORDER BY o.id;

Output yang Dihasilkan:

text 
1|Budi|115000
2|Siti|54000
3|Budi|95000
4|Ani|52000
5|Siti|94000
6|Budi|45000

Mari kita bedah perannya:

  1. CTE item — menggabungkan detail_pesanan dengan produk untuk mendapat nilai uang tiap baris (jumlah × harga).
  2. CTE per_pesanan — meringkas item menjadi total per pesanan, memakai SUM yang dibahas pada Chapter Mengelompokan Hasil dengan GROUP BY dan HAVING.
  3. Query utama — menggabungkan hasilnya dengan pesanan dan pelanggan untuk mendapat nama pembeli, memakai JOIN dari Chapter Menggabungkan Tabel dengan JOIN.

CTE vs Subquery Bersarang #

Kekuatan CTE paling terasa ketika hasil antara dipakai lebih dari sekali. Misalnya kita mencari pesanan yang totalnya melebihi rata-rata — tanpa CTE, subquery per pesanan harus ditulis dua kali:

sql 
SELECT pesanan_id, total
FROM (
  SELECT pesanan_id, SUM(jumlah) AS total
  FROM detail_pesanan
  GROUP BY pesanan_id
)
WHERE total > (
  SELECT AVG(total) FROM (
    SELECT pesanan_id, SUM(jumlah) AS total
    FROM detail_pesanan
    GROUP BY pesanan_id
  )
)
ORDER BY pesanan_id;

Versi CTE-nya menuliskan logika itu sekali saja:

sql 
WITH per_pesanan AS (
  SELECT pesanan_id, SUM(jumlah) AS total
  FROM detail_pesanan
  GROUP BY pesanan_id
)
SELECT pesanan_id, total
FROM per_pesanan
WHERE total > (SELECT AVG(total) FROM per_pesanan)
ORDER BY pesanan_id;

Output yang Dihasilkan (kedua versi identik):

text 
1|3
2|3
3|3
5|4

Rata-rata total unit adalah 2.5, sehingga empat pesanan lolos ambang. Perhatikan bahwa per_pesanan dirujuk dua kali — sesuatu yang mustahil dilakukan subquery tanpa menyalin ulang teksnya.

WITH RECURSIVE: Membangkitkan Baris #

Bentuk paling istimewa adalah WITH RECURSIVE: CTE yang boleh merujuk dirinya sendiri sehingga bisa membangkitkan baris baru dari baris sebelumnya. Strukturnya dua bagian — anchor (baris awal) dan step (cara membesar):

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

Output yang Dihasilkan:

text 
1
2
3
4
5
6
7
8
9
10

Bagian SELECT 1 adalah anchor yang menghasilkan baris pertama. Bagian SELECT n + 1 FROM angka WHERE n < 10 adalah step yang mengambil tiap baris hasil sebelumnya lalu menambah satu; WHERE n < 10 adalah rem yang menghentikan rekursi. Teknik ini dipakai untuk membuat deret tanggal, menelusuri pohon hierarki, atau memecah teks — intinya setiap kali kita butuh baris yang belum ada di tabel.

Peringkat dan total berjalan adalah jenis analisis yang tetap butuh alat lain — Chapter Window Functions membahas OVER yang menghitung agregat tanpa menutup baris demi baris.