Subquery dan CTE di SQL

Kenapa query-mu butuh "anak tangga"

Query nyata jarang selesai dalam satu langkah datar. Pertanyaan seperti "tampilkan produk yang penjualannya di atas rata-rata" butuh dua langkah: hitung dulu rata-ratanya, baru bandingkan tiap produk dengannya. Subquery dan CTE adalah cara SQL menyusun langkah-langkah itu. Tanpa mereka, kamu terpaksa menarik data mentah lalu mengolahnya di Python atau Excel, padahal database bisa melakukannya lebih cepat dan lebih rapi.

Analogi: subquery itu kalkulator di tengah kalimat

Bayangkan kamu berkata, "belikan kopi yang harganya di bawah rata-rata harga kopi di kota ini". Untuk mengatakannya, kamu harus tahu dulu rata-ratanya, sebuah perhitungan kecil yang terselip di dalam permintaan besar. Subquery bekerja persis seperti itu: sebuah query kecil yang hasilnya dipakai oleh query besar di luarnya.

Contoh 1: subquery di WHERE, produk di atas rata-rata

SELECT nama_produk, harga
FROM produk
WHERE harga > (SELECT AVG(harga) FROM produk);

Subquery di dalam kurung berjalan dulu dan menghasilkan satu angka (rata-rata harga). Query luar lalu membandingkan tiap produk dengan angka itu. Ini pola paling umum: "tampilkan yang di atas/di bawah suatu ambang yang dihitung dari data itu sendiri".

Contoh 2: subquery di FROM, agregasi bertingkat

SELECT kategori, AVG(omzet_harian) AS rata_rata_harian
FROM (
    SELECT kategori, tanggal, SUM(total_bayar) AS omzet_harian
    FROM penjualan
    GROUP BY kategori, tanggal
) AS rekap_harian
GROUP BY kategori;

Di sini subquery di FROM membuat "tabel sementara" berisi omzet per kategori per hari, lalu query luar menghitung rata-rata hariannya per kategori. Ini teknik agregasi dua tingkat: kamu tidak bisa langsung AVG dari SUM dalam satu GROUP BY, jadi langkah pertamanya dibungkus sebagai subquery. Perhatikan alias AS rekap_harian yang wajib ada untuk subquery di FROM.

Contoh 3: CTE dengan WITH, versi yang lebih readable

WITH rekap_harian AS (
    SELECT kategori, tanggal, SUM(total_bayar) AS omzet_harian
    FROM penjualan
    GROUP BY kategori, tanggal
),
rata_rata AS (
    SELECT kategori, AVG(omzet_harian) AS rata_rata_harian
    FROM rekap_harian
    GROUP BY kategori
)
SELECT kategori, rata_rata_harian
FROM rata_rata
WHERE rata_rata_harian > 200000
ORDER BY rata_rata_harian DESC;

CTE (Common Table Expression) memindahkan subquery ke atas dengan nama yang jelas, dibaca dari atas ke bawah seperti resep masak. Query ini punya tiga langkah yang masing-masing bernama: hitung omzet harian, hitung rata-ratanya, saring yang di atas 200 ribu. Bandingkan dengan contoh 2 yang menumpuk kurung di tengah, CTE jauh lebih mudah dibaca dan di-debug.

Kapan subquery, kapan CTE

Aturan praktisnya: subquery cukup untuk satu langkah kecil yang sederhana, seperti contoh 1. Begitu langkahnya ada dua atau lebih, atau subquery yang sama dipakai berulang, pindah ke CTE. CTE juga bisa berantai (satu CTE memakai CTE sebelumnya, seperti contoh 3) dan bisa dipakai ulang di query utama tanpa menulis ulang logikanya.

Jebakan umum 1: subquery yang mengembalikan banyak baris untuk perbandingan satu nilai

SALAH: WHERE harga > (SELECT harga FROM produk WHERE kategori = 'Kopi'). Subquery ini mengembalikan banyak baris, tapi operator > hanya bisa membandingkan dengan satu nilai, sehingga query error.

BENAR: kalau subquery bisa mengembalikan banyak baris, pakai IN ("salah satu dari"), ANY/ALL, atau pastikan subquery memakai agregasi sehingga hasilnya satu nilai. Selalu tanyakan: subquery-ku mengembalikan satu nilai, satu kolom banyak baris, atau tabel penuh?

Jebakan umum 2: CTE dipakai tapi logikanya tetap berantakan

CTE bukan mantra ajaib. Menumpuk lima CTE yang masing-masing 30 baris tanpa nama yang jelas sama buruknya dengan subquery bertumpuk. Beri nama CTE sesuai maknanya (rekap_harian, bukan t1), satu CTE satu tanggung jawab, dan komentari langkah yang tidak jelas.

Catatan teknis: Di PostgreSQL dan SQL modern, CTE adalah "optimization fence" versi lama yang kini sudah bisa dioptimasi planner seperti subquery biasa, jadi jangan takut memakai CTE karena alasan performa. Di MySQL 8 ke atas pun CTE sudah didukung penuh. Pengecualian: CTE rekursif (WITH RECURSIVE) memang untuk kasus khusus seperti struktur hierarki karyawan-bawahan.

Ringkasan cepat

• Subquery di WHERE untuk ambang yang dihitung, di FROM untuk agregasi bertingkat.
• CTE (WITH) membuat query bertahap terbaca seperti resep, gampang di-debug.
• Satu langkah kecil pakai subquery, dua langkah atau lebih pakai CTE.
• Pastikan jumlah baris hasil subquery cocok dengan operator yang memakainya.