Recursive CTE SQL: Telusuri Hierarki Organisasi & Kategori

Recursive CTE adalah CTE yang manggil dirinya sendiri, dipakai buat nelusurin data bertingkat.

Kapan kamu butuh? Pas datanya punya hubungan induk-anak yang kedalamannya nggak jelas. Bagan organisasi. Kategori produk yang punya sub-kategori yang punya sub-sub-kategori. Jalur balasan komentar.

JOIN biasa nggak bisa nangani ini kalau kamu nggak tau ada berapa tingkat. Recursive CTE bisa.

Apa itu recursive CTE?

Recursive CTE adalah Common Table Expression yang ngereferensi dirinya sendiri di dalam definisinya, jadi dia bisa ngulang pencarian sampai semua tingkat hierarki ketemu.

Strukturnya selalu tiga bagian:

1. Anchor member: titik awal. Baris pertama yang jadi pijakan.
2. Recursive member: bagian yang manggil CTE-nya sendiri, digabung pakai UNION ALL.
3. Kondisi berhenti: apa yang bikin dia stop.

Kalau CTE biasa masih asing, mampir dulu ke definisi CTE di glossary.

Gimana sintaks recursive CTE?

Bentuk dasarnya gini:

WITH RECURSIVE nama_cte AS (
    -- 1. Anchor member
    SELECT ... FROM tabel WHERE kondisi_awal

    UNION ALL

    -- 2. Recursive member
    SELECT ... FROM tabel t
    JOIN nama_cte c ON t.kolom_induk = c.kolom_id
)
SELECT * FROM nama_cte;

Tiga hal yang wajib bener:

• Keyword RECURSIVE setelah WITH. Di SQL Server nggak perlu ini.
• UNION ALL, bukan UNION. UNION bakal ngilangin duplikat dan bikin lambat.
• Recursive member harus JOIN ke nama CTE-nya sendiri. Kalau nggak, dia nggak recursive.

Contoh 1: Telusuri bagan organisasi

Tabel karyawan punya struktur kayak gini:

id_karyawan	nama	id_atasan
1	Rani (Direktur)	NULL
2	Bagas (Manajer Ops)	1
3	Sinta (Manajer Sales)	1
4	Dimas (Supervisor)	3
5	Nia (Staf Sales)	4

Perhatiin: Nia ada di level 4, tapi kamu nggak bisa tau itu dari satu JOIN.

Recursive CTE-nya:

WITH RECURSIVE hierarki AS (
    -- Anchor: mulai dari yang nggak punya atasan
    SELECT id_karyawan, nama, id_atasan, 1 AS level
    FROM karyawan
    WHERE id_atasan IS NULL

    UNION ALL

    -- Recursive: cari anak buah dari yang udah ketemu
    SELECT k.id_karyawan, k.nama, k.id_atasan, h.level + 1
    FROM karyawan k
    JOIN hierarki h ON k.id_atasan = h.id_karyawan
    WHERE h.level < 10
)
SELECT level, nama FROM hierarki ORDER BY level, nama;

Hasilnya:

level	nama
1	Rani (Direktur)
2	Bagas (Manajer Ops)
2	Sinta (Manajer Sales)
3	Dimas (Supervisor)
4	Nia (Staf Sales)

Cara kerjanya: putaran pertama ngambil Rani. Putaran kedua nyari siapa yang atasannya Rani, dapet Bagas dan Sinta. Putaran ketiga nyari anak buah mereka, dapet Dimas. Begitu terus sampai nggak ada baris baru.

WHERE h.level < 10 itu pengaman. Kalau ada data ngaco (misal A atasannya B, B atasannya A), query kamu berhenti di level 10 bukan jalan selamanya.

Contoh 2: Cari semua bawahan satu manajer

Pertanyaan yang sering muncul: "siapa aja yang di bawah Sinta, termasuk bawahan dari bawahannya?"

Tinggal ganti anchor-nya:

WITH RECURSIVE bawahan AS (
    SELECT id_karyawan, nama, id_atasan, 0 AS jarak
    FROM karyawan
    WHERE id_karyawan = 3   -- Sinta

    UNION ALL

    SELECT k.id_karyawan, k.nama, k.id_atasan, b.jarak + 1
    FROM karyawan k
    JOIN bawahan b ON k.id_atasan = b.id_karyawan
)
SELECT nama, jarak
FROM bawahan
WHERE jarak > 0;

Hasilnya: Dimas (jarak 1), Nia (jarak 2).

WHERE jarak > 0 di akhir buat ngeluarin Sinta sendiri dari hasil.

Contoh 3: Kategori produk bertingkat + jalur lengkap

Ini yang paling sering kepakai di e-commerce. Kamu mau nampilin jalur kayak "Makanan > Sembako > Beras".

WITH RECURSIVE pohon_kategori AS (
    SELECT id_kategori, nama, id_induk,
           nama AS jalur,
           1 AS level
    FROM kategori
    WHERE id_induk IS NULL

    UNION ALL

    SELECT k.id_kategori, k.nama, k.id_induk,
           p.jalur || ' > ' || k.nama AS jalur,
           p.level + 1
    FROM kategori k
    JOIN pohon_kategori p ON k.id_induk = p.id_kategori
    WHERE p.level < 6
)
SELECT jalur, level
FROM pohon_kategori
ORDER BY jalur;

Yang menarik di sini: kolom jalur dibangun bertahap. Tiap putaran nambahin nama kategori baru ke belakang jalur induknya.

Hasilnya:

Makanan                          | 1
Makanan > Sembako                | 2
Makanan > Sembako > Beras        | 3
Makanan > Sembako > Minyak       | 3
Makanan > Snack                  | 2

Operator || itu buat nyambung teks di PostgreSQL. Di MySQL pakai CONCAT(), di SQL Server pakai +.

Contoh kasus: hitung omzet per cabang kategori di toko_berkah

Dataset toko_berkah dari ngulikdata punya 6.240 transaksi dan struktur kategori 3 tingkat: 4 kategori induk, 11 sub-kategori, 38 sub-sub-kategori.

Masalahnya: transaksi kecatat di level paling bawah (misal "Beras Premium"), tapi pemilik toko mau lihat omzet di level induk ("Makanan").

Tanpa recursive CTE, kamu harus JOIN 3 kali dan hardcode jumlah levelnya. Begitu ada kategori baru yang 4 tingkat, query-nya bocor.

WITH RECURSIVE pohon AS (
    SELECT id_kategori, nama, id_induk,
           id_kategori AS id_akar,
           nama AS nama_akar
    FROM kategori
    WHERE id_induk IS NULL

    UNION ALL

    SELECT k.id_kategori, k.nama, k.id_induk,
           p.id_akar, p.nama_akar
    FROM kategori k
    JOIN pohon p ON k.id_induk = p.id_kategori
)
SELECT
    p.nama_akar AS kategori_induk,
    SUM(t.total_harga) AS omzet,
    COUNT(DISTINCT t.id_transaksi) AS jml_transaksi
FROM transaksi t
JOIN produk pr ON pr.id_produk = t.id_produk
JOIN pohon p ON p.id_kategori = pr.id_kategori
GROUP BY p.nama_akar
ORDER BY omzet DESC;

Triknya ada di kolom id_akar, dia dibawa terus turun ke tiap level, jadi tiap sub-sub-kategori tetap tau siapa nenek moyangnya.

Hasilnya:

kategori_induk	omzet	jml_transaksi
Makanan	Rp687.400.000	4.128
Kebutuhan Rumah	Rp312.900.000	1.845
Perlengkapan Dapur	Rp198.600.000	892
Lainnya	Rp41.200.000	376

Yang nggak keliatan sebelum query ini jalan: kategori "Perlengkapan Dapur" cuma muncul di 14% transaksi, tapi nyumbang 16% omzet. Rata-rata belanjanya Rp222.600 per transaksi, tertinggi dari semua kategori induk.

Sebelumnya angka ini kesebar di 9 sub-kategori berbeda dan nggak ada yang nyadar.

Kesalahan umum pakai recursive CTE

Lupa kondisi berhenti. Kalau data kamu punya lingkaran (A atasannya B, B atasannya A), query kamu jalan sampai database nyerah. Selalu pasang WHERE level < N.

Pakai UNION, bukan UNION ALL. UNION ngecek duplikat tiap putaran. Di hierarki dalam, ini bikin query lambat banget tanpa manfaat.

Anchor-nya salah. Kalau kamu mau nelusurin ke bawah, anchor-nya harus akar (id_atasan IS NULL). Kalau mau ke atas (nyari atasan dari satu orang), anchor-nya orang itu, dan JOIN-nya dibalik.

Nggak ada index di kolom induk. Kolom id_atasan atau id_induk dipakai di JOIN tiap putaran. Tanpa index, query kamu bisa 20x lebih lambat di tabel besar.

Pakai MySQL versi lama. MySQL di bawah 8.0 nggak dukung recursive CTE sama sekali. Cek versi kamu dulu, detail sintaksnya ada di dokumentasi resmi MySQL.

FAQ

Apa bedanya CTE biasa sama recursive CTE?

CTE biasa cuma tabel sementara yang dieksekusi sekali. Recursive CTE manggil dirinya sendiri berulang-ulang, jadi hasil putaran pertama dipakai buat cari baris berikutnya sampai nggak ada baris baru. Bedanya di keyword RECURSIVE dan struktur dua bagian yang digabung pakai UNION ALL.

Database apa aja yang mendukung recursive CTE?

PostgreSQL, MySQL 8.0 ke atas, SQL Server, SQLite, dan BigQuery semuanya dukung. SQL Server nggak butuh keyword RECURSIVE, cukup WITH. MySQL di bawah 8.0 nggak bisa. Kamu harus nyimpen kolom jalur atau ngulang JOIN manual.

Gimana cara ngehindarin infinite loop di recursive CTE?

Tambahin kondisi pembatas level, misal WHERE level < 10 di recursive member. Ini pengaman kalau datanya punya lingkaran. PostgreSQL nggak punya batas otomatis, tapi MySQL punya cte_max_recursion_depth yang default-nya 1000.

Kapan recursive CTE lebih baik dari JOIN berulang?

Kalau kedalaman hierarkinya nggak kamu tau pasti. JOIN berulang cuma bisa kalau kamu tau persis levelnya ada berapa. Begitu ada cabang yang lebih dalam, query kamu kelewatan datanya. Kalau JOIN masih bikin bingung, cek panduan JOIN dulu.

Recursive CTE lemot nggak buat data besar?

Bisa lemot kalau kolom penghubungnya nggak diindex. Pasang index di id_atasan atau id_induk, karena kolom itu dipakai di JOIN tiap putaran. Buat hierarki yang jarang berubah, simpan hasilnya ke tabel jadi dan refresh sekali sehari.

Latihan sekarang

• Recursive CTE selalu punya anchor member dan recursive member, digabung UNION ALL.
• Kondisi berhenti bukan opsional. Satu data ngaco, dan query kamu jalan selamanya.
• Kolom kayak level, jalur, atau id_akar yang kamu bawa turun tiap putaran, itu yang bikin hasilnya kepakai buat laporan.

Coba sekarang: bikin tabel karyawan kecil (5 baris) dan tulis recursive CTE-nya sendiri. Nggak perlu data besar buat ngerti pola ini.

Latihan langsung di browser bisa kamu lakuin di NgulikSQL. Habis ini, lanjut ke window function SQL, pasangan yang sering dipakai bareng recursive CTE buat laporan hierarkis.