PostgreSQL'de CTE'ler ve recursive sorgular nasıl çalışır?

Question

IT Interview Practice · Accepted Answer

Bir **CTE** (Common Table Expression), **`WITH`** ile tanımlanan, karmaşık sorguları okunabilir kılan adlandırılmış geçici bir sonuç kümesidir. PostgreSQL, hiyerarşik/ağaç verilerini geçişlemek için **recursive** CTE'ler (`WITH RECURSIVE`) dahil olmak üzere CTE'leri tam olarak destekler.

## Temel CTE'ler — okunabilir sorgu oluşturma

```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'ler karmaşık bir sorguyu adlandırılmış, okunabilir adımlara böler (zincirlenmiş, her biri önceki olanları referans alır) — iç içe alt sorgulardan çok daha net.

## Recursive CTE'ler — hiyerarşileri geçişleme

```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
```

Bir **recursive CTE**, bir temel duruma (başlangıç satırları) ve bir recursive adıma (CTE'nin kendisine geri bağlanan `UNION ALL`) sahiptir, yeni satır kalmayana kadar yinelenir — **ağaçlar ve hiyerarşiler** için mükemmeldir (kuruluş şemaları, kategori ağaçları, malzeme listesi, grafik yolları).

## CTE materyalleştirmesi hakkında bir not (Postgres'e özgü)

```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)
```

Postgres 12 öncesinde, CTE'ler her zaman bir "optimizasyon engeli" (materyalleştirilmiş) idi; şimdi daha iyi performans için satır içi alınabilirler — `MATERIALIZED`/`NOT MATERIALIZED` bunu kontrol eder.

## Neden önemli

CTE'ler ve recursive sorgular, **okunabilirlik ve hiyerarşik veri** için değerli PostgreSQL özellikleridir, bu nedenle bunları anlamak, bakımı yapılabilir karmaşık sorguları yazmak ve ağaç yapısındaki veriler ile başa çıkmak için faydalıdır.

Ygünlük faydası **okunabilirlik**tir: çoklu alt sorgu seviyeleri olan karmaşık sorgular bakımı yapması zor hale gelir, oysa CTE'ler bunları adlandırılmış, sıralı mantıksal adımlara (yukarıdan aşağıya okunur) ayırmanızı sağlar, okunabilirliği çarpıcı şekilde iyileştirir — CTE'leri bakımı yapılabilir SQL yazmanın önemli bir aracı haline getirir.

Öne çıkan yetenek, **recursive CTE'ler**dir (`WITH RECURSIVE`), **hiyerarşik ve ağaç yapısındaki veriler** sorgulamayı mümkün kılar (organizasyon şemaları, kategori ağaçları, malzeme listeleri, konu açılı yorumlar, grafik geçişi) — başka türlü SQL'de çok zor olan bir sorgu sınıfı, recursive CTE'leri hiyerarşik geçişleme için standart çözüm haline getirir.

Recursive CTE yapısını (temel durum + kendisine geri bağlanan recursive adım) anlamak, bu yaygın ihtiyaç için anahtar bilgidir.

Faydalı bir Postgres'e özgü detay, **CTE materyalleştirmesi** davranışıdır (Postgres 12+ daha iyi performans için CTE'leri satır içi alabilir, `MATERIALIZED`/`NOT MATERIALIZED` ile kontrol edilebilir) — optimizasyon için geçerlidir.

Karmaşık sorgular yaygın olduğundan (ve bakım için okunabilirlik önemli olduğundan) ve hiyerarşik veri (recursive CTE'ler iyi bir şekilde benzersiz olarak ele aldığı) sık sık ortaya çıktığından (org yapıları, iç içe kategoriler), CTE'leri ve recursive sorguları anlamak, sorgu bakımını iyileştiren ve hiyerarşik sorgulamayı açan, temiz ve sofistike SQL için sık sık geçerli bir yetenek olan değerli ve pratik PostgreSQL bilgisidir — özellikle tree yapısındaki verileri sorgulamak için tekrarlanan ihtiyaç için recursive form, sık karşılaşılan bir konudur.

Temel CTE'ler — okunabilir sorgu oluşturma

Recursive CTE'ler — hiyerarşileri geçişleme

CTE materyalleştirmesi hakkında bir not (Postgres'e özgü)

Neden önemli

İlgili sorular