RECURSIVE CTE

CTE biasa hanya memberi nama pada hasil query yang datanya sudah ada di tabel. WITH RECURSIVE melangkah lebih jauh: CTE ini boleh membaca hasilnya sendiri untuk menghasilkan baris baru, sehingga satu definisi kecil mampu membangkitkan deret angka, rentetan tanggal, atau peta hierarki bertingkat. Tiga kebutuhan itulah kasus penggunaan paling umum di dunia nyata — tabel angka untuk laporan, kalender untuk kerangka anggaran, dan struktur atasan-pegawai yang tersimpan sebagai rujukan diri. Kekuatan tersebut datang dengan satu kewajiban: rekursi harus tahu kapan berhenti, dan MySQL mengawasi hal itu lewat batas kedalaman cte_max_recursion_depth. Pada chapter ini kita membedah anatomi WITH RECURSIVE baris per baris, membuktikan batas kedalaman itu secara nyata, lalu membangun deret tanggal dan menelusuri hierarki pegawai.

Anatomi: Anchor, Bagian Rekursif, Terminator #

Contoh klasik pertama kita membangkitkan deret angka 1 sampai 10 tanpa tabel apa pun. Perhatikan ketiga bagiannya sebelum melihat penjelasannya:

DROP DATABASE IF EXISTS belajar_set;
CREATE DATABASE belajar_set;
USE belajar_set;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

WITH RECURSIVE deret AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM deret WHERE n < 10
)
SELECT n FROM deret ORDER BY n;

Output yang Dihasilkan:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

Mari kita bedah ketiga bagian pembentuknya baris per baris:

1. Baris WITH RECURSIVE deret AS ( — kata kunci RECURSIVE wajib ditulis setelah WITH untuk memberi izin bahwa CTE deret boleh membaca dirinya sendiri di dalam definisinya. Tanpa kata RECURSIVE, MySQL menolak rujukan FROM deret di dalam definisi.
2. Anchor: baris SELECT 1 AS n — titik awal rekursi. Query pertama sebelum UNION ALL dijalankan sekali di awal dan menghasilkan baris permulaan n = 1. Anchor inilah benih yang memulai seluruh proses; tanpa anchor, tidak ada baris yang bisa dibaca oleh bagian rekursif.
3. Bagian rekursif: baris SELECT n + 1 FROM deret WHERE n < 10 — query kedua sesudah UNION ALL membaca CTE deret itu sendiri. Pada iterasi pertama ia membaca baris n = 1 dari anchor dan menghasilkan n = 2; pada iterasi kedua ia membaca n = 2 dan menghasilkan n = 3; demikian seterusnya. Setiap iterasi hanya membaca baris yang dihasilkan iterasi sebelumnya, bukan seluruh tumpukan.
4. Terminator: bagian WHERE n < 10 — rem pengereman rekursi. Begitu iterasi menghasilkan n = 10, kondisi n < 10 menjadi salah sehingga iterasi berikutnya tidak menghasilkan baris baru; tanpa baris baru, rekursi berhenti dengan sendirinya.
5. UNION ALL sebagai penghubung — hasil anchor dan hasil tiap iterasi ditumpuk tanpa penghapusan duplikat. Penghubung UNION ALL adalah bentuk yang dipakai hampir semua rekursi praktis: baris setiap generasi tetap utuh, sehingga dua baris kembar yang sah — misalnya dua pegawai bernama sama di generasi yang sama — tidak saling melebur.

Hasil akhirnya sepuluh baris dari 1 hingga 10 — tabel yang lahir murni dari logika, bukan dari penyimpanan. ORDER BY n kita tambahkan agar urutan hasil dijamin, sekalipun MySQL umumnya memproses baris sesuai urutan generasi.

Batas Kedalaman Default #

Rekursi tanpa rem yang bekerja adalah bencana: SELECT n + 1 FROM deret WHERE n + 1 > 0 tidak akan pernah berhenti dengan sendirinya. MySQL melindungi server dari situasi itu dengan membatasi jumlah iterasi lewat variabel cte_max_recursion_depth. Nilai bawaannya kita cek dulu:

SELECT @@cte_max_recursion_depth;

Output yang Dihasilkan:

+---------------------------+
| @@cte_max_recursion_depth |
+---------------------------+
|                      1000 |
+---------------------------+
1 row in set (0.00 sec)

Batas bawaan 1000 iterasi sudah cukup untuk hampir semua kebutuhan. Sekarang kita turunkan batas itu ke 5 dalam sesi yang sama, lalu jalankan kembali deret 1 sampai 10 yang butuh 9 iterasi — kedua perintah harus berada dalam satu blok karena variabel cte_max_recursion_depth berlaku per sesi:

-- @error
SET SESSION cte_max_recursion_depth = 5;
WITH RECURSIVE deret AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM deret WHERE n < 10) SELECT n FROM deret;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 3636 (HY000): Recursive query aborted after 6 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
Bye

Server menghentikan query dengan ERROR 3636 tepat setelah iterasi keenam — batas 5 kedalaman terlampaui. Query kita gagal dengan rapi, server tetap sehat, dan tidak ada loop tak berujung. Karena variabel ini berlaku hanya untuk sesi yang mengaturnya, sesi klien baru otomatis kembali ke nilai bawaan:

SELECT @@cte_max_recursion_depth;

Output yang Dihasilkan:

+---------------------------+
| @@cte_max_recursion_depth |
+---------------------------+
|                      1000 |
+---------------------------+
1 row in set (0.00 sec)

Deret Tanggal untuk Kerangka Anggaran #

Pola anchor + terminator yang sama bekerja pada tanggal. Tim keuangan kita perlu kerangka anggaran mingguan: satu baris untuk setiap tanggal selama tujuh hari berturut-turut mulai 1 Maret 2026. Anchor berupa tanggal literal, dan bagian rekursif menambah satu hari lewat DATE_ADD:

WITH RECURSIVE tanggal AS (
  SELECT DATE '2026-03-01' AS t
  UNION ALL
  SELECT DATE_ADD(t, INTERVAL 1 DAY) FROM tanggal WHERE t < DATE '2026-03-07'
)
SELECT t FROM tanggal ORDER BY t;

Output yang Dihasilkan:

+------------+
| t          |
+------------+
| 2026-03-01 |
| 2026-03-02 |
| 2026-03-03 |
| 2026-03-04 |
| 2026-03-05 |
| 2026-03-06 |
| 2026-03-07 |
+------------+
7 rows in set (0.00 sec)

Anchor SELECT DATE '2026-03-01' AS t menetapkan hari pertama sebagai literal tetap. Bagian rekursif DATE_ADD(t, INTERVAL 1 DAY) membaca tanggal generasi sebelumnya lalu menambah satu hari, sementara terminator WHERE t < DATE '2026-03-07' menghentikan proses begitu 7 Maret dihasilkan. Hasilnya tujuh baris tanggal — kalender mini yang siap dipakai. Kekuatan sesungguhnya tampak saat deret ini langsung mengisi tabel anggaran melalui INSERT:

CREATE TABLE anggaran (
  tanggal DATE PRIMARY KEY,
  target DECIMAL(10,2) NULL
);

INSERT INTO anggaran (tanggal)
WITH RECURSIVE tanggal AS (
  SELECT DATE '2026-03-01' AS t
  UNION ALL
  SELECT DATE_ADD(t, INTERVAL 1 DAY) FROM tanggal WHERE t < DATE '2026-03-07'
)
SELECT t FROM tanggal;

UPDATE anggaran SET target = 500000.00 WHERE tanggal = '2026-03-01';

SELECT * FROM anggaran ORDER BY tanggal;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+------------+-----------+
| tanggal    | target    |
+------------+-----------+
| 2026-03-01 | 500000.00 |
| 2026-03-02 |      NULL |
| 2026-03-03 |      NULL |
| 2026-03-04 |      NULL |
| 2026-03-05 |      NULL |
| 2026-03-06 |      NULL |
| 2026-03-07 |      NULL |
+------------+-----------+
7 rows in set (0.00 sec)

Klausa WITH RECURSIVE diletakkan setelah daftar kolom INSERT INTO anggaran (tanggal) dan menggantikan posisi SELECT biasa — tujuh tanggal mengalir ke tabel dalam satu perintah. Kolom target kita biarkan NULL sebagai kerangka, lalu baris pertama diisi lewat UPDATE untuk menunjukkan bahwa hasil rekursi adalah baris sungguhan yang bisa diperbarui seperti data lain. Mengisi tujuh tanggal secara manual tentu masih terasa masuk akal; mengisi kalender satu tahun penuh 365 baris lewat WHERE t < DATE '2026-12-31' adalah saat pola ini benar-benar membayar waktu kita.

Menelusuri Hierarki Manajer dan Pegawai #

Penggunaan WITH RECURSIVE paling bernilai di dunia nyata adalah penelusuran hierarki: struktur organisasi yang tersimpan dalam satu tabel pegawai dengan kolom atasan_id merujuk ke id di tabel yang sama. Kita bangun struktur organisasi toko kita — Ratna direktur di puncak, Hendra dan Maya manajer, lima pegawai di bawahnya:

Diagram di atas menggambarkan relasi yang akan kita simpan sebagai data. Setiap anak panah “atasan → bawahan” menjadi satu kolom atasan_id pada baris si bawahan:

CREATE TABLE pegawai (
  id INT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  atasan_id INT NULL
);

INSERT INTO pegawai (id, nama, atasan_id) VALUES
  (1, 'Ratna', NULL),
  (2, 'Hendra', 1),
  (3, 'Maya', 1),
  (4, 'Budi', 2),
  (5, 'Siti', 2),
  (6, 'Andi', 3),
  (7, 'Dewi', 3),
  (8, 'Rina', 3);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Ratna berdiri sendiri di puncak sehingga atasan_id-nya NULL — dialah root yang menjadi anchor penelusuran. Rekursi berjalan menurun dari root ke seluruh cabang:

WITH RECURSIVE hierarki AS (
  SELECT id, nama, atasan_id, 1 AS tingkat
  FROM pegawai
  WHERE atasan_id IS NULL
  UNION ALL
  SELECT p.id, p.nama, p.atasan_id, h.tingkat + 1
  FROM pegawai p JOIN hierarki h ON p.atasan_id = h.id
)
SELECT tingkat, id, nama, atasan_id
FROM hierarki
ORDER BY tingkat, nama;

Output yang Dihasilkan:

+---------+------+--------+-----------+
| tingkat | id   | nama   | atasan_id |
+---------+------+--------+-----------+
|       1 |    1 | Ratna  |      NULL |
|       2 |    2 | Hendra |         1 |
|       2 |    3 | Maya   |         1 |
|       3 |    6 | Andi   |         3 |
|       3 |    4 | Budi   |         2 |
|       3 |    7 | Dewi   |         3 |
|       3 |    8 | Rina   |         3 |
|       3 |    5 | Siti   |         2 |
+---------+------+--------+-----------+
8 rows in set (0.00 sec)

Bedah baris per baris mengikuti pola tiga bagian yang sama: anchor WHERE atasan_id IS NULL memilih Ratna sebagai baris tingkat 1 — satu-satunya baris tanpa atasan. Bagian rekursif FROM pegawai p JOIN hierarki h ON p.atasan_id = h.id mencari pegawai yang atasannya berada di hasil generasi sebelumnya: iterasi pertama bergabung dengan Ratna dan mengangkat Hendra serta Maya ke tingkat 2; iterasi kedua bergabung dengan Hendra dan Maya lalu mengangkat Budi, Siti, Andi, Dewi, dan Rina ke tingkat 3. Kolom h.tingkat + 1 menghitung kedalaman setiap generasi. Perhatikan bahwa rekursi ini tidak menulis terminator eksplisit: begitu generasi terakhir tidak lagi punya bawahan, JOIN tidak menghasilkan baris baru dan rekursi berhenti sendiri — rem alami dari struktur pohon.

Mulai penelusuran tidak harus dari puncak. Ganti anchor menjadi pegawai tertentu dan rekursi akan memetakan tim di bawah orang itu — misalnya seluruh anggota tim Maya:

WITH RECURSIVE tim AS (
  SELECT id, nama, atasan_id, 1 AS tingkat
  FROM pegawai
  WHERE id = 3
  UNION ALL
  SELECT p.id, p.nama, p.atasan_id, h.tingkat + 1
  FROM pegawai p JOIN tim h ON p.atasan_id = h.id
)
SELECT * FROM tim ORDER BY tingkat, nama;

Output yang Dihasilkan:

+------+------+-----------+---------+
| id   | nama | atasan_id | tingkat |
+------+------+-----------+---------+
|    3 | Maya |         1 |       1 |
|    6 | Andi |         3 |       2 |
|    7 | Dewi |         3 |       2 |
|    8 | Rina |         3 |       2 |
+------+------+-----------+---------+
4 rows in set (0.00 sec)

Empat baris hasil memuat Maya beserta tiga orang bawahannya; Hendra beserta timnya tidak tersentuh karena cabang mereka tidak pernah bergabung dengan anchor id = 3. Pola anchor yang bisa dipindah inilah yang dipakai fitur seperti “laporan bawahan seorang manajer” pada aplikasi kepegawaian.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

DROP DATABASE belajar_set;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• WITH (Common Table Expressions) — MySQL 8.4 Reference Manual
• Date and Time Functions — MySQL 8.4 Reference Manual
• SELECT Statement — MySQL 8.4 Reference Manual

Deret angka, kalender, dan hierarki kini bisa kita bangkitkan dari logika murni lewat WITH RECURSIVE beserta pemahaman anchor, bagian rekursif, terminator, dan rem cte_max_recursion_depth. Seluruh perangkat section ini — UNION, UNION ALL, dan dua bentuk CTE — sudah di tangan kita. Chapter penutup section berikutnya menimbang kapan CTE layak dipakai dan kapan justru menambah ribut, membandingkannya dengan VIEW dan subquery biasa, lalu merangkai pipeline transformasi bertahap dari data mentah sampai laporan top-N.