PolarDB：CTE

Ikhtisar

CTE adalah set hasil sementara bernama yang ada dalam ruang lingkup satu pernyataan SQL untuk menyederhanakan pernyataan tersebut. CTE didefinisikan dengan menggunakan kata kunci WITH. CTE dapat diklasifikasikan menjadi dua jenis berikut:

• CTE Rekursif: Sebuah CTE rekursif merujuk pada dirinya sendiri dalam pernyataan SELECT. CTE rekursif dapat digunakan untuk melakukan kueri rekursif.

• CTE Non-rekursif: CTE non-rekursif tidak merujuk pada dirinya sendiri dalam pernyataan.

Sintaks

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

CTE Rekursif

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

CTE Non-rekursif

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Batasan

• Bagian rekursif dari CTE tidak boleh mengandung fungsi agregat, fungsi jendela dan klausa berikut: GROUP BY, ORDER BY, dan DISTINCT.

• Dalam bagian rekursif dari CTE, CTE hanya dapat dirujuk di klausa FROM tetapi tidak dalam subquery. CTE hanya dapat dirujuk oleh dirinya sendiri satu kali.

• Dalam bagian rekursif dari CTE, CTE tidak dapat digunakan sebagai tabel kanan dalam klausa LEFT JOIN.

Catatan Penggunaan

• CTE rekursif harus diawali dengan kata kunci WITH RECURSIVE. Jika tidak, kesalahan berikut akan muncul: ERROR 1146 (42S02): Table 'cte_name' doesn't exist.

• Kata kunci WITH RECURSIVE juga dapat digunakan untuk mendefinisikan CTE non-rekursif, namun kata kunci RECURSIVE tidak memiliki efek dalam hal ini.

• CTE rekursif terdiri dari dua klausa yang digabungkan oleh kata kunci UNION ALL atau UNION, seperti yang ditunjukkan dalam contoh berikut:

  SELECT ...      -- bagian non-rekursif, mengembalikan set baris awal
  UNION [ALL]
  SELECT ...      -- bagian rekursif, mengembalikan set baris tambahan				

  Klausa pertama disebut bagian non-rekursif dari CTE, yang menghasilkan data awal dan tidak dapat merujuk pada CTE itu sendiri. Klausa kedua disebut bagian rekursif, yang menghasilkan baris tambahan dan merujuk pada CTE itu sendiri. Kueri dieksekusi secara iteratif hingga bagian rekursif dari CTE tidak mengembalikan data baru.

• Tipe data yang dikembalikan oleh CTE rekursif ditentukan sebagai nullable oleh bagian non-rekursif dari CTE.

• Dalam setiap iterasi, bagian rekursif dari CTE hanya dapat merujuk pada data yang dihasilkan oleh iterasi sebelumnya.

• Nilai variabel dalam CTE ditentukan berdasarkan nama dan posisi variabel dalam setiap iterasi. Contohnya adalah sebagai berikut:

  WITH RECURSIVE cte AS
  (
    SELECT 1 AS n, 1 AS p, -1 AS q
    UNION ALL
    SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
  )
  SELECT * FROM cte;

  Dalam CTE di atas, nilai variabel n, p, dan q ditentukan dalam bagian rekursif berdasarkan nama mereka setelah nilainya diatur dalam bagian non-rekursif. Setelah klausa dalam bagian rekursif dijalankan, nilai variabel ditentukan berdasarkan posisi mereka dalam proyek. Hasilnya adalah sebagai berikut:

  +------+------+------+
  | n    | p    | q    |
  +------+------+------+
  |    1 |    1 |   -1 |
  |    2 |   -2 |    2 |
  |    3 |    4 |   -4 |
  |    4 |   -8 |    8 |
  |    5 |   16 |  -16 |
  +------+------+------+

• Anda dapat menggunakan kata kunci LIMIT setelah klausa UNION dalam bagian rekursif dari CTE untuk menentukan jumlah eksekusi CTE.

Parameter

Parameter cte_max_recursion_depth menentukan jumlah maksimum iterasi pada bagian rekursif dari CTE rekursif, dengan nilai default sebesar 500.