Query Rekursif Common Table Expression (CTE)

Penggunaan CTE seperti pada posting sebelumnya mungkin tidak terlalu memberikan keuntungan yang signifikan pada beberapa kasus. Tapi CTE dapat mereferensikan ke diri sendiri sehingga dapat membuat query rekursif. Hal ini sangat memberikan keuntungan. Sebuah query dapat dikatakan query rekursif jika query tersebut mereferensikan CTE rekursif. Hasil query merupakan data hirarki, contohnya menampilkan organisasi yang mempunyai departemen dan sub departemen.
CTE rekursif mempunyai tiga elemen:

• Pemanggilan pertama dari CTE rekursif terdiri dari satu atau lebih <CTE_query_definition> yang digabung dengan menggunakan operator UNION ALL, UNION, EXCEPT atau INTERSECT. Query ini disebut sebagai “anchor members” yang harus diletakkan sebelum “recursive members”.
• Pemanggilan rekursif yang terdiri dari satu atau lebih <CTE_query_definition> yang digabung dengan menggunakan operator UNION ALL dan mereferensikan ke diri sendiri. Query ini disebut sebagai “recursive members”.
• Pengecekan terminasi query yang dilakukan secara implisit. Proses rekursif akan berhenti jika tidak ada baris data yang dikembalikan dari pemanggilan sebelumnya.

Perlu diperhatikan bahwa CTE rekursif yang tidak benar dapat mengakibatkan perulangan yang tidak berakhir. Untuk membatasi level rekursif dapat menggunakan pilihan MAXRECURSION dengan nilai antara 0 sampai 32767 (0: tidak ada batasan) pada klausa OPTION pada operasi SELECT, INSERT, UPDATE atau DELETE. Contoh dibawah ini mencari semua kemungkinan jalur yang ada antara dua kota. Tabel satu berisi nama-nama kota, dan tabel lainnya berisi jalan yang mungkin dilewati antar kota.
DECLARE @Kota TABLE (Kode int, Nama varchar(MAX));
DECLARE @Jalan TABLE (KodeAsal int, KodeTujuan int);
INSERT INTO @Kota VALUES
  (1, 'Surabaya'), (2, 'Malang'),
  (3, 'Semarang'), (4, 'Yogyakarta'),
  (5, 'Jakarta'), (6, 'Tangerang');
INSERT INTO @Jalan VALUES
  (1, 2), (1, 3), (2, 1), (2, 4), (3, 1),
  (3, 4), (3, 5), (4, 2), (4, 3), (4, 6),
  (5, 3), (5, 6), (6, 5);
WITH JalurCTE (Kode, Jalur, NamaAsal, NamaTujuan, Jarak, KodeTujuan)
AS
(
  SELECT
      CAST(Kode AS varchar(MAX)),
      CAST(Nama AS varchar(MAX)),
      CAST(Nama AS varchar(MAX)),
      CAST(NULL AS varchar(MAX)),
      CAST(0 AS int),
      Kode
    FROM @Kota
  UNION ALL
  SELECT
      c.Kode + '-' + CAST(j.KodeTujuan AS varchar(MAX)),
      c.Jalur + '-' + k.Nama,
      c.NamaAsal,
      k.Nama,
      c.Jarak + 1,
      j.KodeTujuan
    FROM JalurCTE AS c
    INNER JOIN @Jalan AS j ON j.KodeAsal = c.KodeTujuan
    INNER JOIN @Kota AS k ON k.Kode = j.KodeTujuan
    WHERE c.Kode NOT LIKE '%' + CAST(j.KodeTujuan AS VARCHAR(MAX)) + '%'
)
SELECT NamaAsal, NamaTujuan, Jalur, Jarak
FROM JalurCTE WHERE Jarak > 0
ORDER BY NamaAsal, NamaTujuan, Jarak
OPTION (MAXRECURSION 0)

Kita bisa memodifikasi query SELECT untuk menampilkan hasil pada baris paling bawah supaya bisa menampilkan jalur terpendek (shortest path) antara dua kota.
SELECT j1.NamaAsal, j1.NamaTujuan, j1.Jalur, j1.Jarak
FROM JalurCTE AS j1
INNER JOIN (
  SELECT NamaAsal, NamaTujuan, MIN(Jarak) AS Jarak
    FROM JalurCTE WHERE Jarak > 0 GROUP BY NamaAsal, NamaTujuan) AS j2 ON
j2.NamaAsal = j1.NamaAsal AND j2.NamaTujuan = j1.NamaTujuan AND j2.Jarak = j1.Jarak
ORDER BY NamaAsal, NamaTujuan, Jarak