Recursive CTEs di SQL: Menggali Kekuatan Query Hierarkis dan Graph Traversal

1. Pendahuluan

Pernahkah Anda berhadapan dengan data di database yang memiliki hubungan “parent-child” atau “item-subitem” yang berulang? Bayangkan struktur organisasi perusahaan, daftar kategori produk yang bersarang, atau bahkan utas komentar di media sosial. Data seperti ini, yang kita sebut data hierarkis atau data berbentuk graf, seringkali menjadi tantangan tersendiri saat ingin mengambil atau menganalisisnya.

Secara tradisional, kita mungkin akan mencoba menggunakan self-join berulang. Namun, pendekatan ini cepat menjadi rumit, tidak efisien, dan kaku jika kedalaman hierarki tidak tetap atau tidak diketahui sebelumnya. Jika hierarkinya terlalu dalam, query Anda bisa jadi sangat panjang dan sulit dibaca.

📌 Masalah Umum:

Bagaimana cara menemukan semua bawahan dari seorang manajer, termasuk bawahan dari bawahan mereka, dan seterusnya?
Bagaimana cara menghitung total komponen yang dibutuhkan untuk merakit suatu produk, yang mana setiap komponen bisa memiliki sub-komponen lagi (Bill of Materials)?
Bagaimana cara mengetahui semua kota yang bisa dijangkau dari kota A melalui serangkaian penerbangan?

Di sinilah Recursive Common Table Expressions (CTEs) datang sebagai pahlawan. Recursive CTEs adalah fitur SQL yang sangat powerful dan elegan untuk menanyakan data hierarkis atau graf secara efisien. Dengan Recursive CTE, Anda bisa “menjelajahi” struktur data ini secara iteratif, selangkah demi selangkah, hingga kondisi tertentu terpenuhi.

Artikel ini akan membawa Anda menyelami dunia Recursive CTEs, dari konsep dasar hingga contoh-contoh praktis yang akan mengubah cara Anda berinteraksi dengan data kompleks. Mari kita mulai!

2. Apa Itu Common Table Expression (CTE)?

Sebelum masuk ke “Recursive CTE”, mari kita pahami dulu apa itu Common Table Expression (CTE). CTE, yang didefinisikan dengan klausa WITH, adalah hasil set sementara yang bisa Anda referensikan dalam pernyataan SQL berikutnya (SELECT, INSERT, UPDATE, DELETE). Anggap saja CTE sebagai “variabel” atau “subquery bernama” yang membuat query Anda lebih modular dan mudah dibaca.

💡 Manfaat CTE:

Keterbacaan: Memecah query kompleks menjadi bagian-bagian logis yang lebih kecil.
Modularitas: Setiap CTE dapat fokus pada satu tugas tertentu.
Reusabilitas: Anda bisa merujuk ke CTE yang sama berkali-kali dalam query utama.

Contoh CTE Sederhana

Misalnya, kita ingin menemukan karyawan dengan gaji di atas rata-rata:

WITH RataRataGaji AS (
    SELECT AVG(gaji) AS avg_gaji
    FROM karyawan
)
SELECT k.nama, k.gaji
FROM karyawan k, RataRataGaji rr
WHERE k.gaji > rr.avg_gaji;

Di sini, RataRataGaji adalah CTE yang menghitung rata-rata gaji, kemudian digunakan di query utama. Ini jauh lebih bersih daripada menyisipkan subquery (SELECT AVG(gaji) FROM karyawan) langsung di klausa WHERE.

3. Memahami Recursive CTE: Konsep Dasar

Recursive CTE adalah jenis CTE khusus yang dapat mereferensikan dirinya sendiri. Ini memungkinkan Anda untuk menjalankan query secara iteratif, seperti sebuah loop, yang sangat cocok untuk menelusuri struktur data hierarkis.

Struktur dasar Recursive CTE terdiri dari dua bagian utama, dipisahkan oleh UNION ALL (atau UNION):

Anchor Member (Anggota Jangkar):
- Ini adalah bagian inisialisasi atau “basis” dari rekursi.
- Query ini dieksekusi sekali dan menghasilkan set baris awal.
- Anggota jangkar TIDAK boleh mereferensikan CTE itu sendiri.
Recursive Member (Anggota Rekursif):
- Ini adalah bagian yang mereferensikan CTE itu sendiri.
- Query ini dieksekusi berulang kali, menggunakan hasil dari eksekusi sebelumnya dari CTE sebagai input.
- Setiap kali dieksekusi, ia menambahkan baris baru ke hasil CTE.
Kondisi Terminasi:
- Ini adalah bagian KRITIS yang memastikan rekursi berhenti.
- Biasanya diimplementasikan melalui klausa WHERE di anggota rekursif, yang membatasi kapan iterasi harus berhenti (misalnya, ketika tidak ada lagi “child” yang ditemukan).
- ❌ Peringatan: Tanpa kondisi terminasi yang tepat, Recursive CTE Anda bisa berjalan selamanya dan menguras sumber daya database!

Analogi: Menjelajahi Pohon Keluarga

Bayangkan Anda ingin menemukan semua keturunan dari kakek-nenek Anda.

Anchor Member: Mulai dari kakek-nenek Anda. Ini adalah titik awal.
Recursive Member: Dari setiap orang yang Anda temukan di langkah sebelumnya, cari anak-anak mereka. Ulangi proses ini untuk setiap anak yang ditemukan.
Kondisi Terminasi: Berhenti ketika Anda tidak menemukan lagi anak-anak dari orang yang terakhir Anda telusuri.

Setiap “generasi” baru adalah hasil dari iterasi sebelumnya, dan semua hasil digabungkan menjadi satu set akhir.

4. Sintaks Recursive CTE dalam Praktik

Sintaks umum untuk Recursive CTE adalah sebagai berikut:

WITH RECURSIVE nama_cte (kolom1, kolom2, ...) AS (
    -- Anchor Member (Query awal)
    SELECT kolom1, kolom2, ...
    FROM tabel_awal
    WHERE kondisi_awal

    UNION ALL -- Atau UNION
    
    -- Recursive Member (Query yang mereferensikan dirinya sendiri)
    SELECT t.kolom1, t.kolom2, ...
    FROM tabel_lain t
    JOIN nama_cte r ON t.kolom_join = r.kolom_join
    WHERE kondisi_terminasi -- Penting!
)
-- Query Utama yang menggunakan Recursive CTE
SELECT *
FROM nama_cte;

Kata kunci RECURSIVE (atau CASCADING di beberapa sistem seperti Oracle) memberitahu database bahwa ini adalah CTE rekursif.

Mari kita gunakan contoh tabel karyawan untuk menemukan semua bawahan dari seorang manajer:

CREATE TABLE karyawan (
    id INT PRIMARY KEY,
    nama VARCHAR(100),
    manager_id INT NULL REFERENCES karyawan(id)
);

INSERT INTO karyawan (id, nama, manager_id) VALUES
(1, 'Alice', NULL), -- CEO
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3),
(7, 'Grace', 4);

Contoh 1: Mencari Semua Bawahan dari Seorang Manajer

Kita ingin menemukan semua bawahan (langsung maupun tidak langsung) dari Alice (ID: 1).

WITH RECURSIVE daftar_bawahan AS (
    -- Anchor Member: Mulai dari manajer yang kita cari (Alice)
    SELECT
        id,
        nama,
        manager_id,
        0 AS level -- Menambahkan kolom 'level' untuk melacak kedalaman
    FROM karyawan
    WHERE id = 1

    UNION ALL

    -- Recursive Member: Temukan bawahan dari setiap karyawan di 'daftar_bawahan'
    SELECT
        k.id,
        k.nama,
        k.manager_id,
        db.level + 1 AS level
    FROM karyawan k
    JOIN daftar_bawahan db ON k.manager_id = db.id
)
SELECT id, nama, manager_id, level
FROM daftar_bawahan;

✅ Output yang Diharapkan:

id | nama    | manager_id | level
---|---------|------------|------
1  | Alice   | NULL       | 0
2  | Bob     | 1          | 1
3  | Charlie | 1          | 1
4  | David   | 2          | 2
5  | Eve     | 2          | 2
6  | Frank   | 3          | 2
7  | Grace   | 4          | 3

Output ini menunjukkan hierarki bawahan Alice beserta level kedalamannya.

Contoh 2: Mencari Semua Atasan dari Seorang Karyawan

Sekarang, mari kita cari semua atasan (langsung maupun tidak langsung) dari Grace (ID: 7).

WITH RECURSIVE daftar_atasan AS (
    -- Anchor Member: Mulai dari karyawan yang kita cari (Grace)
    SELECT
        id,
        nama,
        manager_id,
        0 AS level
    FROM karyawan
    WHERE id = 7

    UNION ALL

    -- Recursive Member: Temukan manajer dari setiap karyawan di 'daftar_atasan'
    SELECT
        k.id,
        k.nama,
        k.manager_id,
        da.level + 1 AS level
    FROM karyawan k
    JOIN daftar_atasan da ON k.id = da.manager_id
)
SELECT id, nama, manager_id, level
FROM daftar_atasan;

❌ Penting: Perhatikan kondisi join di anggota rekursif! Untuk bawahan, kita join k.manager_id = db.id. Untuk atasan, kita join k.id = da.manager_id. Ini adalah detail kecil yang sangat krusial.

5. Studi Kasus Nyata: Mengatasi Masalah Kompleks

Recursive CTEs bersinar terang dalam skenario di mana hubungan data bersifat iteratif dan tidak terbatas kedalamannya.

Studi Kasus 1: Struktur Organisasi (Hierarki Atasan-Bawahan)

Seperti contoh di atas, ini adalah kasus klasik. Anda bisa mengembangkannya untuk:

Mencari jalur lengkap dari seorang karyawan ke CEO.
Menghitung jumlah karyawan di bawah setiap manajer.
Mendeteksi siklus (misalnya, jika seorang karyawan secara tidak sengaja diatur sebagai manajer dari dirinya sendiri atau manajernya).

-- Mencari jalur lengkap dari Grace ke CEO
WITH RECURSIVE employee_path AS (
    SELECT
        id,
        nama,
        manager_id,
        CAST(nama AS VARCHAR(255)) AS path, -- Kolom untuk melacak jalur
        0 AS level
    FROM karyawan
    WHERE id = 7 -- Mulai dari Grace

    UNION ALL

    SELECT
        k.id,
        k.nama,
        k.manager_id,
        CAST(k.nama || ' -> ' || ep.path AS VARCHAR(255)) AS path,
        ep.level + 1 AS level
    FROM karyawan k
    JOIN employee_path ep ON k.id = ep.manager_id
)
SELECT *
FROM employee_path
WHERE manager_id IS NULL; -- Filter untuk mendapatkan jalur hingga ke CEO

💡 Tips: Menggunakan CAST untuk tipe data path penting agar panjang string bisa dinamis.

Studi Kasus 2: Bill of Materials (BOM) / Resep Produk

Bayangkan Anda membuat produk yang terdiri dari sub-komponen, dan sub-komponen tersebut juga bisa terdiri dari sub-komponen lain.

CREATE TABLE produk_komponen (
    produk_id INT,
    komponen_id INT,
    kuantitas INT,
    PRIMARY KEY (produk_id, komponen_id)
);

INSERT INTO produk_komponen (produk_id, komponen_id, kuantitas) VALUES
(100, 10, 2), -- Produk Akhir 100 butuh Komponen 10 (2 buah)
(100, 20, 1),
(10, 1, 3),   -- Komponen 10 butuh Komponen 1 (3 buah)
(10, 2, 1),
(20, 3, 5),   -- Komponen 20 butuh Komponen 3 (5 buah)
(3, 4, 1);    -- Komponen 3 butuh Komponen 4 (1 buah)

-- Mencari semua komponen yang dibutuhkan untuk Produk 100
WITH RECURSIVE bom AS (
    -- Anchor Member: Mulai dari produk_id yang kita cari
    SELECT
        produk_id,
        komponen_id,
        kuantitas,
        1 AS level,
        kuantitas AS total_kuantitas -- Kuantitas di level ini
    FROM produk_komponen
    WHERE produk_id = 100

    UNION ALL

    -- Recursive Member: Temukan sub-komponen dari setiap komponen yang ditemukan
    SELECT
        b.produk_id,
        pk.komponen_id,
        pk.kuantitas,
        b.level + 1 AS level,
        b.total_kuantitas * pk.kuantitas AS total_kuantitas -- Hitung total kuantitas kumulatif
    FROM produk_komponen pk
    JOIN bom b ON pk.produk_id = b.komponen_id
)
SELECT komponen_id, SUM(total_kuantitas) AS total_dibutuhkan
FROM bom
WHERE komponen_id NOT IN (SELECT produk_id FROM produk_komponen) -- Hanya komponen dasar
GROUP BY komponen_id;

Query ini akan memberikan daftar komponen dasar yang dibutuhkan untuk membuat Produk 100, beserta total kuantitasnya. Ini sangat berguna untuk manajemen inventaris dan perencanaan produksi.

Studi Kasus 3: Graph Traversal (Jalur Terpendek Sederhana)

Recursive CTE juga bisa digunakan untuk menelusuri graf, seperti jaringan pertemanan atau rute perjalanan.

CREATE TABLE rute_penerbangan (
    asal VARCHAR(50),
    tujuan VARCHAR(50),
    jarak INT,
    PRIMARY KEY (asal, tujuan)
);

INSERT INTO rute_penerbangan (asal, tujuan, jarak) VALUES
('Jakarta', 'Surabaya', 700),
('Surabaya', 'Bali', 300),
('Jakarta', 'Bandung', 150),
('Bandung', 'Yogyakarta', 400),
('Yogyakarta', 'Surabaya', 350),
('Bali', 'Lombok', 100);

-- Mencari semua kota yang bisa dijangkau dari Jakarta
WITH RECURSIVE reachable_cities AS (
    -- Anchor Member: Mulai dari kota asal (Jakarta)
    SELECT
        asal AS start_city,
        tujuan AS current_city,
        CAST(asal || ' -> ' || tujuan AS VARCHAR(255)) AS path,
        jarak AS total_jarak,
        1 AS num_stops
    FROM rute_penerbangan
    WHERE asal = 'Jakarta'

    UNION ALL

    -- Recursive