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:
- Hitung nilai setiap pesanan dari tabel item.
- Gabungkan dengan tabel pesanan, lalu rangkum per cabang per bulan.
- 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:
WITHditulis sekali di awal. CTE berikutnya cukup dipisah koma, tanpaWITHlagi.- CTE yang ditulis belakangan boleh memakai CTE sebelumnya (
bulananmemakainilai_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
WITHdi setiap CTE.WITHhanya sekali di awal, sisanya dipisah koma. - Koma setelah CTE terakhir. Sebelum
SELECTpenutup tidak boleh ada koma. - Subquery yang menghasilkan lebih dari satu baris untuk
=.WHERE harga = (SELECT harga FROM produk)error karena hasilnya banyak baris. PakaiIN, atau pastikan subquery hanya menghasilkan satu nilai. NOT INdengan subquery yang berisiNULL. Kalau daftar di dalamNOT INmengandungNULL, 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
- Dengan CTE, hitung jumlah pesanan setiap member, lalu tampilkan member yang membeli lebih dari 3 kali.
- Tampilkan produk yang terjual lebih banyak dari rata-rata jumlah terjual semua produk.
- Hitung omzet per bulan, lalu tampilkan hanya bulan yang omzetnya di atas rata-rata bulanan.
Langkah Berikutnya
- Membandingkan bulan ini dengan bulan lalu dan membuat peringkat tanpa menghilangkan baris: window function SQL.
- Lihat CTE dipakai untuk menjawab pertanyaan bisnis sungguhan di studi kasus SQL analisis penjualan.
- Urutan lengkapnya ada di Roadmap SQL untuk Data Analyst.