Bagaimana cara kerja CTE dan query rekursif di PostgreSQL?

Question

Accepted Answer

**CTE** (Common Table Expression), didefinisikan dengan **`WITH`**, adalah hasil set sementara bernama yang membuat query kompleks mudah dibaca. PostgreSQL sepenuhnya mendukung CTE, termasuk CTE **rekursif** (`WITH RECURSIVE`) untuk traversal data hierarki/pohon.

## CTE Dasar — pembangun query yang mudah dibaca

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTE memecah query kompleks menjadi langkah-langkah bernama dan mudah dibaca (berantai, masing-masing mereferensikan yang sebelumnya) — jauh lebih jelas daripada subquery bersarang.

## CTE Rekursif — traversal hierarki

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Sebuah **CTE rekursif** memiliki kasus dasar (baris awal) dan langkah rekursif (`UNION ALL` menggabungkan kembali ke CTE itu sendiri), beriterasi hingga tidak ada baris baru — sempurna untuk **pohon dan hierarki** (bagan organisasi, pohon kategori, bill-of-materials, jalur graf).

## Catatan tentang materialisasi CTE (spesifik Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Sebelum Postgres 12, CTE selalu merupakan "optimization fence" (dimaterialisasi); sekarang dapat diinline untuk performa lebih baik — `MATERIALIZED`/`NOT MATERIALIZED` mengontrol hal ini.

## Mengapa ini penting

CTE dan query rekursif adalah fitur PostgreSQL yang berharga untuk **keterbacaan dan data hierarki**, jadi memahaminya berguna untuk menulis query kompleks yang mudah dipelihara dan menangani data terstruktur pohon.

Manfaat sehari-hari adalah **keterbacaan**: query kompleks dengan banyak level subquery menjadi sulit dipelihara, sementara CTE memungkinkan Anda menguraikannya menjadi langkah-langkah logis berurutan bernama (dibaca dari atas ke bawah), secara dramatis meningkatkan kejelasan — membuat CTE alat penting untuk menulis SQL yang mudah dipelihara.

Kemampuan unggulan adalah **CTE rekursif** (`WITH RECURSIVE`), yang memungkinkan query **data hierarki dan terstruktur pohon** (bagan organisasi, pohon kategori, bill-of-materials, komentar berseri, traversal graf) — kelas query yang sangat sulit dalam SQL, membuat CTE rekursif solusi standar untuk traversal hierarki.

Memahami struktur CTE rekursif (kasus dasar + langkah rekursif yang menggabungkan kembali ke dirinya sendiri) adalah pengetahuan kunci untuk kebutuhan umum ini.

Detail spesifik Postgres yang berguna adalah perilaku **materialisasi CTE** (Postgres 12+ dapat menginline CTE untuk performa lebih baik, dapat dikontrol dengan `MATERIALIZED`/`NOT MATERIALIZED`) — relevan untuk optimisasi.

Karena query kompleks adalah hal umum (dan keterbacaan penting untuk pemeliharaan), dan karena data hierarki (yang CTE rekursif tangani dengan sangat baik) muncul sering (struktur organisasi, kategori bersarang), memahami CTE dan query rekursif adalah pengetahuan PostgreSQL yang praktis dan relevan yang meningkatkan keterbacaan query dan membuka query hierarki — kemampuan yang sering relevan untuk SQL yang bersih dan canggih serta topik umum, terutama bentuk rekursif untuk kebutuhan berulang dalam query data terstruktur pohon.