CTE (Common Table Expression) adalah hasil query sementara yang diberi nama dengan WITH nama AS (...), lalu dipakai seperti tabel biasa di query berikutnya. Gunanya memecah pertanyaan rumit menjadi beberapa langkah: misalnya hitung dulu nilai setiap pesanan, lalu rangkum per bulan, lalu bandingkan antar cabang. Setiap langkah bisa dicek sendiri, sehingga kesalahan lebih mudah ditemukan. Subquery mengerjakan hal yang sama tetapi ditulis bersarang di dalam query lain. Di bawah ada contoh keduanya dan kapan memakai masing-masing.

Semua contoh memakai dataset latihan kedai kopi. WITH didukung SQLite 3.8.3 ke atas, PostgreSQL, MySQL 8 ke atas, dan MariaDB 10.2 ke atas. Satu-satunya fungsi khusus SQLite di artikel ini adalah strftime; padanannya di database lain ada di artikel fungsi tanggal.

Subquery di WHERE

Subquery adalah query di dalam tanda kurung yang hasilnya dipakai oleh query luar. Produk yang harganya di atas rata-rata harga menu:

SELECT nama_produk, harga
FROM produk
WHERE harga > (SELECT AVG(harga) FROM produk)
ORDER BY harga DESC;
nama_produk harga
Matcha Latte 27000
Cappuccino 25000
Cokelat Panas 23000
Kopi Susu Gula Aren 22000
Croissant 20000

Subquery (SELECT AVG(harga) FROM produk) menghasilkan satu angka (rata-rata harga menu sekitar Rp18.818), lalu dipakai sebagai pembanding. Kamu tidak perlu menghitung rata-ratanya dulu lalu mengetik angkanya manual.

Subquery juga bisa menghasilkan daftar untuk IN. Member yang pernah membeli di cabang Darmo (C04):

SELECT id_pelanggan, nama
FROM pelanggan
WHERE id_pelanggan IN (
  SELECT id_pelanggan FROM pesanan WHERE id_cabang = 'C04'
);
id_pelanggan nama
M002 Sari Dewi
M007 Agus Setiawan
M011 Hendra Wijaya
M016 Citra Lestari

Subquery di FROM

Subquery juga bisa dipakai sebagai tabel. Ini yang dipakai di artikel GROUP BY untuk menghitung rata-rata nilai pesanan: rangkum dulu per pesanan, lalu rangkum lagi. Masalahnya, kalau langkahnya lebih dari dua, subquery bersarang menjadi sulit dibaca. Di situlah CTE dipakai.

CTE: Query Bertahap dengan WITH

Pertanyaan: "berapa omzet setiap cabang di bulan Maret dan April?" Jawabannya butuh tiga langkah:

  1. Hitung nilai setiap pesanan dari tabel item.
  2. Gabungkan dengan tabel pesanan, lalu rangkum per cabang per bulan.
  3. Tambahkan nama cabang dan saring dua bulan terakhir.

Dengan CTE, setiap langkah ditulis berurutan dan diberi nama:

WITH nilai_pesanan AS (
  SELECT id_pesanan, SUM(qty * harga_satuan) AS nilai
  FROM item_pesanan
  GROUP BY id_pesanan
),
bulanan AS (
  SELECT p.id_cabang,
         strftime('%Y-%m', p.tanggal) AS bulan,
         SUM(n.nilai) AS omzet
  FROM pesanan AS p
  JOIN nilai_pesanan AS n ON n.id_pesanan = p.id_pesanan
  GROUP BY p.id_cabang, bulan
)
SELECT c.nama_cabang, b.bulan, b.omzet
FROM bulanan AS b
JOIN cabang AS c ON c.id_cabang = b.id_cabang
WHERE b.bulan IN ('2026-03', '2026-04')
ORDER BY c.nama_cabang, b.bulan;
nama_cabang bulan omzet
Buahbatu 2026-03 161000
Buahbatu 2026-04 102000
Dago 2026-03 292000
Dago 2026-04 288000
Darmo 2026-03 109000
Darmo 2026-04 172000
Tebet 2026-03 236000
Tebet 2026-04 113000

Tebet turun dari Rp236.000 ke Rp113.000, lebih dari separuh. Temuan ini ditelusuri lebih jauh di studi kasus SQL.

Aturan penulisan CTE:

  • WITH ditulis sekali di awal. CTE berikutnya cukup dipisah koma, tanpa WITH lagi.
  • CTE yang ditulis belakangan boleh memakai CTE sebelumnya (bulanan memakai nilai_pesanan).
  • Query terakhir, tanpa koma, adalah yang menghasilkan output.

Perhatikan juga langkah pertama. Nilai pesanan dirangkum sebelum digabung dengan tabel pesanan. Ini cara aman menghindari jebakan baris ganda setelah JOIN, karena setiap pesanan hanya muncul satu kali di nilai_pesanan.

Mengecek Setiap Langkah

Kelebihan terbesar CTE: setiap langkah bisa dicek dengan mengganti query terakhir. Misalnya, sebelum percaya pada hasil di atas, cek apakah nilai_pesanan sudah benar:

WITH nilai_pesanan AS (
  SELECT id_pesanan, SUM(qty * harga_satuan) AS nilai
  FROM item_pesanan
  GROUP BY id_pesanan
)
SELECT COUNT(*) AS jumlah_pesanan, SUM(nilai) AS total
FROM nilai_pesanan;
jumlah_pesanan total
68 2547000

Ada 68 pesanan dengan total Rp2.547.000, sama dengan isi dataset. Langkah pertama aman, jadi kalau nanti ada angka yang janggal, masalahnya ada di langkah berikutnya. Dengan subquery bersarang, pengecekan seperti ini jauh lebih merepotkan.

Contoh: Member dengan Belanja di Atas Rata-rata

Pertanyaan dengan dua lapis rangkuman: total belanja setiap member, lalu rata-rata dari total itu, lalu siapa saja yang di atas rata-rata.

WITH belanja AS (
  SELECT p.id_pelanggan, SUM(i.qty * i.harga_satuan) AS total_belanja
  FROM pesanan AS p
  JOIN item_pesanan AS i ON i.id_pesanan = p.id_pesanan
  WHERE p.id_pelanggan IS NOT NULL
  GROUP BY p.id_pelanggan
),
rata AS (
  SELECT AVG(total_belanja) AS rata_rata FROM belanja
)
SELECT pl.nama, b.total_belanja, ROUND(r.rata_rata, 0) AS rata_rata_member
FROM belanja AS b
JOIN rata AS r ON 1 = 1
JOIN pelanggan AS pl ON pl.id_pelanggan = b.id_pelanggan
WHERE b.total_belanja > r.rata_rata
ORDER BY b.total_belanja DESC;
nama total_belanja rata_rata_member
Wulan Sari 258000 111444
Tika Rahmawati 186000 111444
Andi Pratama 185000 111444
Rina Marlina 145000 111444
Bayu Nugroho 130000 111444
Fitri Handayani 126000 111444
Budi Santoso 125000 111444
Yoga Prasetyo 119000 111444

Delapan dari 18 member yang pernah membeli belanja di atas rata-rata Rp111.444. JOIN rata AS r ON 1 = 1 menempelkan satu baris rata-rata ke setiap baris belanja, karena CTE rata hanya berisi satu baris.

Kalau datanya ini milik kantor dan berisi nama sungguhan, ingat bahwa daftar seperti ini adalah data pribadi. Samarkan dulu sebelum dibagikan atau diolah di layanan AI.

Subquery atau CTE?

Situasi Pilihan
Satu nilai pembanding (rata-rata, maksimum) di WHERE Subquery
Daftar ID untuk IN Subquery
Dua langkah atau lebih CTE
Hasil satu langkah dipakai lebih dari sekali CTE
Query akan dibaca atau dirawat orang lain CTE

Hasil keduanya sama. Pilih yang paling mudah dibaca.

Kesalahan yang Sering Terjadi

  • Menulis WITH di setiap CTE. WITH hanya sekali di awal, sisanya dipisah koma.
  • Koma setelah CTE terakhir. Sebelum SELECT penutup tidak boleh ada koma.
  • Subquery yang menghasilkan lebih dari satu baris untuk =. WHERE harga = (SELECT harga FROM produk) error karena hasilnya banyak baris. Pakai IN, atau pastikan subquery hanya menghasilkan satu nilai.
  • NOT IN dengan subquery yang berisi NULL. Kalau daftar di dalam NOT IN mengandung NULL, hasilnya selalu kosong. Untuk mencari yang tidak punya pasangan, pakai pola LEFT JOIN + IS NULL.
  • Tidak mengecek langkah awal. Kalau langkah pertama salah, semua langkah setelahnya ikut salah.

Latihan

  1. Dengan CTE, hitung jumlah pesanan setiap member, lalu tampilkan member yang membeli lebih dari 3 kali.
  2. Tampilkan produk yang terjual lebih banyak dari rata-rata jumlah terjual semua produk.
  3. Hitung omzet per bulan, lalu tampilkan hanya bulan yang omzetnya di atas rata-rata bulanan.

Langkah Berikutnya