WITH RECURSIVE — Buat Sequence dan Telusuri Hierarki dengan Query Rekursif

Data yang dipakai pada artikel ini — tabel employee

WITH RECURSIVE adalah cara menulis CTE yang memanggil dirinya sendiri untuk menghasilkan baris satu per satu.

Gerakan memasukkan hasil sebelumnya sebagai input berikutnya dan mengulanginya ini disebut rekursi.

Datanya adalah tabel employee (30 baris; kolom manager_id menunjuk ke emp_id manager, sebuah struktur self-referential).

Sebelum memulai latihan, lihat dulu definisi kolom dan data contoh tabel employee, dan juga SELECT minimal yang akan kita pakai sebagai anchor sebuah CTE rekursif.

① Jalankan SELECT 1; dan konfirmasi bahwa ia mengembalikan nilai 1 saja dalam satu baris (ini adalah SELECT paling sederhana yang akan kita pakai sebagai anchor CTE rekursif).

② Jalankan PRAGMA table_info(employee); untuk memeriksa definisi kolom.

③ Jalankan SELECT * FROM employee LIMIT 5; untuk meninjau 5 baris pertama. Perhatikan juga bagaimana NULL muncul pada kolom manager_id (karyawan yang tidak punya manager).

3 bagian yang menyusun query rekursif — anchor / recursive term / UNION ALL

Memakai kode di bawah, kita akan menelusuri 3 bagian — ① anchor (SELECT 1) / ② recursive term (SELECT n + 1 FROM seq WHERE n < 5) / ③ `UNION ALL` — satu per satu.

-- Kode referensi untuk bagian ini. Kita akan membahas 3 bagian ① / ② / ③ secara berurutan
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① anchor
  UNION ALL                           -- ③ UNION ALL (perekat antara anchor dan recursive term)
  SELECT n + 1 FROM seq WHERE n < 5   -- ② recursive term
)
SELECT n FROM seq;

-- Hasil (5 baris): n=1, 2, 3, 4, 5

① Anchor — bagian SELECT 1

Anchor adalah SELECT yang menghasilkan baris pertama.

SELECT 1 pada kode referensi adalah anchor — ia hanyalah SELECT biasa, ditulis sekali, yang tidak mereferensikan dirinya sendiri.

Nilai (1) yang dikembalikan di sini menjadi isi pertama seq(n), yang lalu diserahkan ke recursive term sebagai nilai awal n.

② Recursive term — bagian SELECT n + 1 FROM seq WHERE n < 5

-- Kode referensi (diulang). Bagian ini menjelaskan baris ② recursive term
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- ① anchor
  UNION ALL                           -- ③ UNION ALL (perekat)
  SELECT n + 1 FROM seq WHERE n < 5   -- ★ ② recursive term ← yang sedang kita jelaskan ★
)
SELECT n FROM seq;

Pada WITH RECURSIVE seq(n), bagian seq(n) adalah deklarasi nama CTE (seq) dan nama kolom (n). Isinya dimulai sebagai `1` dari anchor, dan setelahnya menampung hasil dari recursive term.

SELECT n + 1 berarti "nilai sebelumnya + 1" pada setiap kali.

n awal adalah 1, jadi n + 1 berikutnya adalah 2, lalu 3, dan seterusnya — ini berlanjut sampai WHERE berhenti mengembalikan baris.

③ UNION ALL — bagian `UNION ALL` pada kode

-- Kode referensi (diulang). Bagian ini menjelaskan baris ③ UNION ALL
WITH RECURSIVE seq(n) AS (
  SELECT 1                            -- anchor
  UNION ALL                           -- ★ yang sedang kita jelaskan ★ kelihatannya satu baris, tapi…
  SELECT n + 1 FROM seq WHERE n < 5   -- recursive term
)
SELECT n FROM seq;

-- …di dalam DBMS ini setara dengan "menumpuk apa yang dihasilkan recursive term, satu UNION ALL per iterasi":

SELECT 1            -- anchor (berjalan hanya sekali)
UNION ALL           -- ← tumpukan ke-1: menumpuk output lintasan-1
SELECT 2
UNION ALL           -- ← tumpukan ke-2
SELECT 3
UNION ALL           -- ← tumpukan ke-3
SELECT 4
UNION ALL           -- ← tumpukan ke-4
SELECT 5;

-- Hasilnya [1, 2, 3, 4, 5] dengan cara mana pun
-- Dengan kata lain, satu "UNION ALL" di dalam WITH RECURSIVE ≒ UNION ALL diulang untuk setiap iterasi

UNION ALL adalah operator yang menumpuk hasil dari dua atau lebih SELECT secara vertikal.

Kita memakai UNION ALL (yang mempertahankan semua baris) alih-alih UNION (yang menghapus duplikat) karena kita ingin setiap baris yang dihasilkan selama rekursi mendarat di hasil akhir.

Contoh 1 — Membuat sequence (1 sampai 5)

Kebutuhan: "Saya ingin membuat sequence dari 1 sampai 10 secara on the fly sebagai baris, tanpa tabel sequence yang sudah ada." Bahkan tanpa tabel master sequence, kamu bisa membangunnya dengan CTE rekursif. (Kalau dijalankan dengan benar, penjelasannya akan muncul.)

① Definisikan CTE untuk sequence dengan WITH RECURSIVE. Anchor harus berupa SELECT yang mengembalikan nilai awal 1.

② Pada recursive term, referensikan CTE itu sendiri dan kembalikan nilai sebelumnya ditambah 1. Kondisi terminasi harus "teruskan selama nilai saat ini kurang dari 10".

③ Pada kueri utama, kembalikan sequence yang dihasilkan dalam urutan menaik.

Contoh 2 — Proyeksi pertumbuhan gaji (berapa gaji N tahun dari sekarang dengan 5% per tahun?)

Contoh 1 adalah sequence numerik yang abstrak, tapi di sini kita akan membahas rekursi praktis yang mulai dari data tabel `employee` sungguhan.

Dengan gaji Sam (emp_id=19) saat ini, kita akan memproyeksikan berapa gajinya 5 tahun ke depan kalau naik 5% setiap tahun.

Cukup ganti recursive term jadi projected * 1.05 dan kamu mendapat deret yang tumbuh secara berbunga-bunga.

-- Proyeksi pertumbuhan gaji: kalau gaji Sam (emp_id=19) naik 5% per tahun, berapa N tahun kemudian?
WITH RECURSIVE salary_growth(year, projected) AS (
  SELECT 0, salary                                     -- anchor: ambil gaji saat ini dari employee
  FROM employee WHERE emp_id = 19
  UNION ALL
  SELECT year + 1, projected * 1.05                    -- recursive term: kenaikan 5% per tahun
  FROM salary_growth
  WHERE year < 5
)
SELECT year, ROUND(projected) AS projected_salary
FROM salary_growth;

-- Hasil (6 baris): proyeksi 5 tahun mulai dari gaji Sam saat ini 4100000
--  year | projected_salary
--  -----+-----------------
--    0  | 4100000   ← anchor (employee.salary apa adanya)
--    1  | 4305000   ← recursive term (4100000 × 1.05)
--    2  | 4520250   ← recursive term (4305000 × 1.05)
--    3  | 4746263   ← recursive term (4520250 × 1.05, ROUND diterapkan)
--    4  | 4983576   ← recursive term (4746262.5 × 1.05, ROUND diterapkan)
--    5  | 5232754   ← recursive term. Berikutnya, year=5 membuat WHERE year<5 false → stop

Ada dua perbedaan dari contoh sequence.

① Anchor mengambil nilai dari tabel employee (SELECT 0, salary FROM employee WHERE emp_id = 19) — kamu bisa mulai dari data nyata.

② Ini adalah CTE 2-kolom (year dan projected), dan recursive term memperbarui kedua kolom sekaligus dengan year + 1 dan projected * 1.05.

Begitu kamu bisa mengambil nilai dari tabel nyata pada anchor, kamu bisa menulis simulasi yang mulai dari nilai itu (bunga majemuk, proyeksi populasi, perhitungan langkah-ke-tujuan, dan seterusnya).

Kebutuhan: "Mulai dari gaji Bob (emp_id=2) saat ini, saya ingin memproyeksikan berapa gajinya 5 tahun ke depan kalau naik 10% per tahun." (Kalau dijalankan dengan benar, penjelasannya akan muncul.)

① Definisikan nama CTE sebagai growth dan kolomnya sebagai year dan projected pada WITH RECURSIVE.

② Anchor harus mengambil salary dari employee untuk emp_id = 2, dengan year mulai dari 0.

③ Recursive term harus mengembalikan year + 1 dan projected * 1.10, dengan kondisi terminasi year < 5.

④ Pada kueri utama, kembalikan year dan ROUND(projected) AS projected_salary dalam urutan menaik.

Contoh 3 — Pakai dengan JOIN (telusuri rantai manager_id satu langkah pada satu waktu)

Berikutnya kita akan membahas penggunaan kanonis lain dari CTE rekursif, menelusuri hierarki self-referential.

employee.manager_id menyimpan "emp_id manager kamu", jadi dengan mengikutinya langkah demi langkah kamu bisa menelusuri ke atas struktur organisasi: karyawan → manager → manager dari manager, dan seterusnya.

Pada contoh sequence kita memakai n+1 untuk menghasilkan nilai berikutnya; di sini kita mengambil baris berikutnya (manager) dengan JOIN untuk memajukan rantainya.

-- Menelusuri hierarki: dari karyawan awal (emp_id=19, Sam) ke atas melalui para manager
WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id    -- ① anchor: baris awal
  FROM employee WHERE emp_id = 19
  UNION ALL                          -- ③ UNION ALL
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN chain c ON e.emp_id = c.manager_id    -- ② recursive term: ambil baris hasil join
)
SELECT emp_id, name, manager_id FROM chain;

-- Hasil (2 baris):
--  emp_id | name | manager_id
--  -------+------+-----------
--    19   | Sam  |    2        ← anchor (titik awal)
--     2   | Bob  |  NULL       ← baris hasil recursive term; manager_id NULL jadi lintasan berikutnya berhenti

① Anchor (SELECT ... FROM employee WHERE emp_id = 19) mengambil baris awal.

Di sini ia memasukkan baris emp_id=19 (Sam) ke dalam chain sebagai isi pertamanya.

Perhatikan bahwa WITH RECURSIVE chain AS (...) tidak punya deklarasi nama kolom seperti chain(...). Itu karena SELECT pada anchor sudah memberi chain nama kolom natural (emp_id / name / manager_id) dari kolom-kolom employee. Ketika sudah mudah dibaca, kamu bisa menghilangkannya.

② Recursive term (JOIN chain c ON e.emp_id = c.manager_id) menggabungkan employee dengan chain sebelumnya dan mengambil baris hasil join ke dalam chain.

Begitu manager_id dari baris hasil join jadi NULL, iterasi berikutnya tidak bisa menemukan pasangan join, hasilnya 0 baris, dan rekursi berhenti secara alami.

(Tidak perlu kondisi terminasi eksplisit seperti WHERE n < 5 pada contoh sequence — ujung hierarki adalah kondisi terminasinya.)

Kebutuhan: "Mulai dari emp_id = 19 (Sam), saya ingin menelusuri rantai manager_id ke atas — manager Sam, lalu manager orang itu, dan seterusnya — untuk mendaftar rantai peringkatnya."

① Definisikan CTE untuk hierarki dengan WITH RECURSIVE. Anchor harus mengambil satu baris (emp_id, name, manager_id) dari employee untuk emp_id = 19.

② Pada recursive term, join employee dengan CTE pada "employee.emp_id = manager_id milik CTE" dan ambil baris manager (emp_id, name, manager_id) satu langkah pada satu waktu.

③ Pada kueri utama, kembalikan emp_id, name, manager_id.

Latihan 3 menelusuri dari bawah ke atas melalui para manager.

Sekarang kita akan ke arah sebaliknya: "Mulai dari emp_id = 1 (Alice), saya ingin menelusuri rantai manager_id ke bawah melalui bawahan Alice, lalu bawahan mereka, dan seterusnya — untuk mendaftar semua orang di tim Alice."

① Definisikan CTE untuk bawahan dengan WITH RECURSIVE. Anchor harus mengambil satu baris (emp_id, name, manager_id) dari employee untuk emp_id = 1.

② Pada recursive term, join employee dengan CTE pada "employee.manager_id = emp_id milik CTE" dan ambil bawahan dari baris saat ini (emp_id, name, manager_id) satu langkah pada satu waktu.

③ Pada kueri utama, kembalikan emp_id, name, manager_id dalam urutan menaik emp_id.

Contoh 4 — Rekursi yang menghasilkan beberapa baris sekaligus (seluruh pohon organisasi)

Contoh 3 menelusuri satu baris pada satu waktu (Sam → Bob → stop).

Di sini kita akan membahas kasus di mana anchor atau recursive term menghasilkan beberapa baris sekaligus.

Skenarionya: "Mulai dari 5 executive (karyawan yang tidak punya manager) dan ambil seluruh pohon organisasi 30 orang sekaligus." Kamu akan melihat query rekursif menelusuri beberapa cabang secara paralel dalam cara breadth-first.

-- Mulai dari semua executive (karyawan tanpa manager) dan secara rekursif ambil semua orang di organisasi
WITH RECURSIVE org_tree AS (
  -- ① anchor: 5 karyawan dengan manager_id = NULL (mengembalikan beberapa baris sekaligus)
  SELECT emp_id, name, manager_id
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  -- ② recursive term: ambil bawahan langsung dari baris sebelumnya (juga beberapa baris)
  SELECT e.emp_id, e.name, e.manager_id
  FROM employee e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT emp_id, name, manager_id FROM org_tree
ORDER BY emp_id;

-- Hasil (30 baris = seluruh perusahaan):
--   Anchor (5 baris): emp_id=1 Alice, 2 Bob, 3 Carol, 9 Ivan, 28 Brian
--   Lintasan recursive term 1 (25 baris): 9 bawahan Alice + 8 bawahan Bob + 8 bawahan Carol
--   Lintasan recursive term 2 (0 baris): para bawahan tidak punya bawahannya sendiri → stop
--   Total: 5 + 25 = 30 baris

Ada dua perbedaan dari Contoh 3.

① Anchor mengembalikan beberapa baris (5) sekaligus — setiap baris yang cocok dengan WHERE manager_id IS NULL dipakai untuk menginisialisasi CTE.

② Recursive term menelusuri beberapa cabang secara paralel — dengan 5 orang di org_tree, satu lintasan recursive term mengambil bawahan setiap orang pada saat yang sama dalam iterasi yang sama (9 Alice + 8 Bob + 8 Carol = 25 baris).

Dengan kata lain, query rekursif bisa melakukan lebih dari "menyelam lebih dalam satu baris pada satu waktu" — ia juga bisa mengembangkan beberapa cabang secara paralel breadth-first.

Kebutuhan: "Mulai dari Bob (emp_id=2) dan Carol (emp_id=3) pada saat yang sama, saya ingin mengambil bawahan mereka dalam satu kueri." (Kalau dijalankan dengan benar, penjelasannya akan muncul.)

① Nama CTE harus team pada WITH RECURSIVE; hilangkan deklarasi nama kolom (pakai nama kolom natural dari employee).

② Anchor harus mengambil dua baris dari employee di mana emp_id adalah 2 atau 3.

③ Recursive term harus join employee dengan CTE pada "employee.manager_id = emp_id milik CTE" dan ambil bawahannya.

④ Pada kueri utama, kembalikan emp_id, name, manager_id dalam urutan menaik emp_id.

Tips — Kenapa pakai WITH RECURSIVE?

Tiga situasi di mana WITH RECURSIVE bersinar

Query rekursif untuk situasi di mana SQL biasa tidak bisa atau bikin sangat menyiksa. Use case utamanya ada tiga:

① Menelusuri hierarki — struktur organisasi, folder, bill of materials — struktur self-referential yang kamu tidak tahu seberapa dalam. Daripada menumpuk beberapa JOIN secara manual, kamu bisa menulisnya dalam bentuk yang berhenti secara alami di ujung data.

② Membuat banyak baris — sequence, tanggal berurutan, kalender — daripada menulis UNION ALL seratus kali secara manual, satu baris dengan WHERE melakukannya.

③ Mendorong loop sisi aplikasi ke dalam SQL — menulis loop di aplikasi untuk menelusuri parent ID kena masalah N+1 query, tapi query rekursif menanganinya dalam satu kueri.

Kalau JOIN atau GROUP BY biasa cukup, kamu tidak butuh rekursi. Tapi saat kamu berada di salah satu dari tiga situasi di atas, ini adalah alat pertama yang harus dijangkau.