Piyé carane CTEs lan recursive queries bisa gumana ing PostgreSQL?

Question

IT Interview Practice · Accepted Answer

A **CTE** (Common Table Expression), didefinisikake karo **`WITH`**, yaiku set asil sementara sing dijenengi sing ndadekake query sing rumit gampang dibaca. PostgreSQL ndhukung sepenuhé CTEs, kalebu **recursive** CTEs (`WITH RECURSIVE`) kanggo nglakoni data hierarkis/pohon.

## CTEs Dasar — bangun query sing bisa 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
```

CTEs membelah query sing rumit dadi langkah-langkah sing dijenengi lan gampang dibaca (kapisanan, saben iku referensi sing sadurungé) — luwih jelas tinimbang subquery sing pepet.

## Recursive CTEs — jalari 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
```

A **recursive CTE** duwe kasus dasar (baris permulaan) lan langkah recursive (`UNION ALL` nggabung bali menyang CTE dhewe), nglakoni nganti ora ana baris anyar — sempurna kanggo **pohon lan hierarki** (bagan organisasi, pohon kategori, bill-of-materials, jalur grafik).

## Cathetan babagan CTE materialization (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)
```

Kajaba Postgres 12, CTEs mesthi "optimization fence" (materialized); saiki bisa inlined kanggo performa luwih apik — `MATERIALIZED`/`NOT MATERIALIZED` kontrol iki.

## Ora penting mengapa

CTEs lan recursive queries minangka fitur PostgreSQL sing berharga kanggo **keterbacaan lan data hierarkis**, dadi pangerten babagan bab-bab iki migunani kanggo nulis query rumit sing bisa dijaga lan nangani data berstruktur pohon.

Mafaat saben dina yaiku **keterbacaan**: query rumit karo macem-macem tingkat subquery dadi angel dijaga, dene CTEs ngidini sampeyan memecah bab iki dadi langkah logis sing dijenengi, berurutan (waca nang-nang nang sidhang), ndadekake kejelasan banget — ndadekake CTEs alat penting kanggo nulis SQL sing bisa dijaga.

Kemampuan sing nandingake yaiku **recursive CTEs** (`WITH RECURSIVE`), sing ndadekake query **data hierarkis lan berstruktur pohon** (bagan organisasi, pohon kategori, bill-of-materials, komentar berbenang, jalur grafik) — jenis query sing boten gampang banget ing SQL, ndadekake recursive CTEs solusi standar kanggo jalari hierarki.

Pangerten struktur recursive CTE (kasus dasar + langkah recursive nggabung bali menyang dhewe) yaiku pangetan penting kanggo kabutuhan iki.

Detail spesifik Postgres sing berguna yaiku perilaku **CTE materialization** (Postgres 12+ bisa inlined CTEs kanggo performa luwih apik, bisa dikontrol karo `MATERIALIZED`/`NOT MATERIALIZED`) — relevan kanggo optimasi.

Sabab query rumit umum (lan keterbacaan penting kanggo pemeliharaan), lan sabab data hierarkis (sing recursive CTEs nangani karo apik) katon asring (struktur organisasi, kategori pepet), pangerten CTEs lan recursive queries yaiku pangetan PostgreSQL sing berharga lan praktis sing nduweni panguripan pangerten query lan mbukak jalari hierarkis — kemampuan sing asring relevan kanggo SQL sing resik, sophisticated, lan topik umum, utamané bentuk recursive kanggo kabutuhan berulang kanggo query data berstruktur pohon.

CTEs Dasar — bangun query sing bisa dibaca

Recursive CTEs — jalari hierarki

Cathetan babagan CTE materialization (spesifik Postgres)

Ora penting mengapa

Pitakon sing gegandhengan