Query pendek bisa berubah jadi monster begitu kamu menumpuk subquery di dalam subquery. Saya pernah mewarisi laporan penjualan dengan tiga level subquery bersarang, dan butuh waktu lama cuma untuk membaca arah datanya. Common Table Expression—disingkat CTE—adalah cara menulis query besar sebagai blok-blok kecil yang punya nama, mirip menyusun fungsi saat coding. Setelah memakainya, saya jarang lagi menulis subquery yang menggantung di klausa WHERE.

Apa Itu CTE?

CTE adalah hasil query sementara yang kamu beri nama dan pakai di dalam satu pernyataan SQL. Penulisannya diawali kata kunci WITH, lalu nama CTE, lalu query-nya di dalam tanda kurung. Begitu didefinisikan, kamu bisa memakainya seperti tabel biasa di query utama. CTE hanya hidup selama satu statement berjalan—begitu statement selesai, CTE-nya hilang.

Contoh Pertama: Laporan Penjualan Bulanan

Anggap kamu punya tabel orders dengan kolom order_date dan total. Kamu ingin melihat bulan-bulan yang penjualannya melewati angka tertentu. Tanpa CTE, kamu harus menulis ulang agregasi di klausa HAVING atau membungkusnya jadi subquery. Dengan CTE, agregasinya dihitung sekali, lalu query utama tinggal memfilter.

sql
WITH penjualan_bulanan AS (
    SELECT
        date_trunc('month', order_date) AS bulan,
        SUM(total) AS total_penjualan
    FROM orders
    GROUP BY 1
)
SELECT bulan, total_penjualan
FROM penjualan_bulanan
WHERE total_penjualan > 100000000
ORDER BY bulan;

Bacaannya mengalir dari atas ke bawah: hitung dulu penjualan per bulan, baru filter hasilnya. Kalau nanti logika agregasinya berubah, kamu cuma menyentuh satu tempat.

Beberapa CTE dalam Satu Query

CTE bisa lebih dari satu. Kamu definisikan semuanya di awal, dipisah koma, lalu query utama menggabungkannya. Pola ini berguna saat kamu perlu membandingkan data aktual dengan target, misalnya.

sql
WITH
penjualan_bulanan AS (
    SELECT date_trunc('month', order_date) AS bulan, SUM(total) AS aktual
    FROM orders
    GROUP BY 1
),
target_bulanan AS (
    SELECT generate_series('2025-01-01'::date, '2025-12-01'::date, '1 month') AS bulan,
           120000000 AS target
)
SELECT t.bulan, t.target, COALESCE(p.aktual, 0) AS aktual
FROM target_bulanan t
LEFT JOIN penjualan_bulanan p USING (bulan)
ORDER BY t.bulan;

CTE kedua memakai generate_series untuk membuat deret bulan, jadi bulan yang tidak punya transaksi tetap muncul dengan aktual nol. Ini jauh lebih gampang dibaca ketimbang menaruh dua subquery di klausa FROM. Kalau sudah nyaman dengan agregasi, padukan dengan SQL Window Functions—misalnya menghitung ranking penjualan per bulan di dalam CTE, lalu memfilter hasilnya di query utama.

CTE vs Subquery vs Tabel Sementara

Ketiganya bisa dipakai untuk hal yang mirip, tapi punya peran berbeda. Subquery inline cocok untuk sekali pakai dan logika sederhana. CTE unggul saat hasil antara dipakai beberapa kali atau saat kamu ingin membaca query dari atas ke bawah. Tabel sementara (CREATE TEMP TABLE) berguna kalau hasilnya perlu dipakai lintas statement, bukan cuma satu query. Untuk laporan sekali jalan, CTE hampir selalu pilihan paling bersih.

Recursive CTE: Data Hierarki

Recursive CTE adalah fitur yang membuat CTE benar-benar kuat. Dengan menambahkan kata RECURSIVE setelah WITH, sebuah CTE bisa memanggil dirinya sendiri. Ini cara standar untuk menelusuri data hierarki: struktur organisasi, kategori bersarang, bill of materials, sampai thread komentar.

Struktur recursive CTE selalu terdiri dari dua bagian yang disatukan UNION ALL: anchor member (baris awal) dan recursive member (bagian yang memanggil nama CTE-nya sendiri). Lihat contoh paling sederhana dulu—membuat deret angka 1 sampai 10.

sql
WITH RECURSIVE deret AS (
    SELECT 1 AS n                 -- anchor member
    UNION ALL
    SELECT n + 1 FROM deret       -- recursive member
    WHERE n < 10                  -- kondisi berhenti
)
SELECT * FROM deret;

Anchor member menghasilkan baris pertama (n = 1). Recursive member mengambil hasil sebelumnya dan menambah satu, selama n masih di bawah 10. Tanpa kondisi berhenti, query ini akan berputar selamanya.

Recursive CTE bisa infinite loop kalau kondisi berhentinya salah. Selalu sertakan batasan, misalnya WHERE level < 10 atau WHERE depth < 50, supaya query tidak menggantung.

Studi Kasus: Struktur Organisasi

Sekarang contoh yang lebih nyata. Anggap ada tabel employees dengan kolom manager_id yang menunjuk ke id atasan masing-masing.

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
('Andi', NULL),   -- CEO, tidak punya atasan
('Budi', 1),
('Citra', 1),
('Dewi', 2),
('Eko', 2),
('Fajar', 3);

Untuk menampilkan seluruh rantai organisasi beserta levelnya, gunakan recursive CTE.

sql
WITH RECURSIVE struktur_org AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL          -- anchor: mulai dari CEO

    UNION ALL

    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN struktur_org s ON e.manager_id = s.id   -- recursive: cari bawahan
)
SELECT id, name, level
FROM struktur_org
ORDER BY level, id;

Anchor member mengambil baris CEO (manager_id IS NULL). Recursive member menggabungkan employees dengan hasil CTE sebelumnya untuk menemukan bawahan di level berikutnya. Hasilnya: Andi di level 1, Budi dan Citra di level 2, lalu Dewi, Eko, dan Fajar di level 3.

Contoh di atas memakai PostgreSQL, tapi konsepnya sama di SQLite (versi 3.8.3 ke atas) dan MySQL (8.0 ke atas). Kalau kamu baru mulai dengan database, tutorial SQLite bisa jadi titik awal yang ringan tanpa perlu server.

Memodifikasi Data dengan CTE (PostgreSQL)

CTE di PostgreSQL tidak cuma untuk SELECT. Kamu bisa menjalankan INSERT, UPDATE, atau DELETE di dalam CTE, lalu memakai klausa RETURNING untuk membaca baris yang terdampak. Pola ini sering dipakai untuk memindahkan data sekaligus mencatat log audit.

sql
WITH dihapus AS (
    DELETE FROM cart_items
    WHERE user_id = 42 AND expired_at < now()
    RETURNING product_id, quantity
)
INSERT INTO audit_log (action, product_id, quantity, deleted_at)
SELECT 'purge_expired', product_id, quantity, now()
FROM dihapus;

Dalam satu statement, keranjang yang kedaluwarsa dihapus dan setiap baris yang terhapus langsung dicatat ke tabel audit. Tidak ada jeda antara dua operasi, jadi risiko kehilangan jejak audit jauh lebih kecil.

PostgreSQL biasanya mengevaluasi CTE satu kali per query, tapi perencana query bisa meng-inline CTE yang dipakai sekali agar eksekusinya lebih murah. Kalau kamu ingin memaksa hasil CTE disimpan (di-materialize) supaya tidak dihitung ulang, tambahkan kata MATERIALIZED setelah AS—fitur ini tersedia sejak PostgreSQL 12.

Kapan Sebaiknya Pakai CTE

Pakai CTE saat query terasa terlalu panjang untuk diikuti dalam sekali baca, saat hasil antara dipakai lebih dari sekali, atau saat kamu berurusan dengan data hierarki. Lewati CTE untuk query sederhana—menambah WITH pada SELECT satu tabel malah jadi berisik. Dan kalau kamu mengakses database lewat Python, PostgreSQL dan psycopg tidak peduli: CTE tetap dikirim sebagai string query biasa, jadi tidak ada perubahan di sisi kode Python.

Apa bedanya CTE dengan subquery?

Subquery inline ditulis langsung di klausa WHERE atau FROM dan cuma bisa dipakai sekali di tempat itu. CTE diberi nama dan bisa dipakai berulang dalam satu statement, plus lebih enak dibaca karena query dibaca dari atas ke bawah. Dari sisi performa keduanya sering setara—perencana query PostgreSQL yang menentukan cara eksekusinya.

Apakah CTE selalu membuat query lebih cepat?

Tidak. CTE adalah alat untuk keterbacaan, bukan jaminan performa. Pada PostgreSQL sebelum versi 12, CTE selalu di-materialize sehingga kadang justru lebih lambat. Sejak PostgreSQL 12, perencana query bisa meng-inline CTE. Kalau performa jadi soal, cek dengan EXPLAIN ANALYZE.