CTE ve Recursive CTE | Ücretsiz SQL: Sıfırdan İleri Seviyeye Kursu

Giriş — Subquery Spagettisine Son

Karmaşık sorgular yazarken bir noktada subquery'lerin iç içe geçtiği, okuması zor, bakımı imkansız bir spagetti ortaya çıkar. Bir subquery'nin sonucunu başka bir subquery'de kullanmak istiyorsun, ama SQL buna doğrudan izin vermiyor — ya aynı subquery'yi kopyalıyorsun ya da üst üste sarmalanmış 4-5 katman oluşturuyorsun.

İşte CTE (Common Table Expression), tam olarak bu sorunu çözmek için tasarlanmış. "Geçici isimlendirilmiş sonuç kümesi" diyebilirsin — bir sorgunun sonucuna isim verip, onu aynı sorgunun devamında tablo gibi kullanıyorsun.

🎯 Analoji: CTE'yi bir yemek tarifindeki "hazırlık" aşaması gibi düşün. Tüm yemeği tek seferde anlatmak yerine: "Önce sosu hazırlayın: ... Sonra harcı hazırlayın: ... Şimdi sosu ve harcı birleştirin." Her adım kendi başına anlaşılır, son adımda hepsini birleştirirsin. CTE de SQL'de aynı şeyi yapar — karmaşık sorguyu adımlara böler.

CTE Temelleri — WITH Sözdizimi

Temel Syntax

WITH cte_adı AS (
    -- Sorgu
    SELECT ...
)
SELECT * FROM cte_adı;

WITH kelimesiyle başlar, CTE'ye bir isim verilir ve parantez içinde sorgu yazılır. Sonra bu isim bir tablo gibi kullanılır.

İlk Örnek

-- ❌ Subquery versiyonu — okunması zor
SELECT * FROM (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) customer_spending
WHERE total_spent > 10000;

-- ✅ CTE versiyonu — okunması kolay
WITH customer_spending AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_spending WHERE total_spent > 10000;

Aynı sonuç ama CTE versiyonu çok daha okunabilir. İsim verilen sorgu, bağımsız bir blok olarak anlaşılabilir.

CTE'nin Avantajları

Okunabilirlik: Karmaşık sorguları mantıksal bloklara ayırır
Yeniden kullanılabilirlik: Aynı CTE'yi sorgu içinde birden fazla kez kullanabilirsin
Bakım kolaylığı: Bir bloğu değiştirmek, tüm sorguyu etkilemez
Recursive sorgular: Hiyerarşik veri yapılarında (ağaç, organizasyon şeması) olmazsa olmaz

Birden Fazla CTE

Virgülle ayırarak birden fazla CTE tanımlayabilirsin:

WITH 
-- CTE 1: Müşteri harcamaları
customer_spending AS (
    SELECT 
        customer_id, 
        SUM(total_amount) AS total_spent,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
),
-- CTE 2: Müşteri segmentleri
customer_segments AS (
    SELECT 
        customer_id,
        total_spent,
        order_count,
        CASE 
            WHEN total_spent >= 50000 THEN 'VIP'
            WHEN total_spent >= 20000 THEN 'Premium'
            WHEN total_spent >= 5000  THEN 'Standart'
            ELSE 'Yeni'
        END AS segment
    FROM customer_spending  -- ← İlk CTE'yi kullanıyor!
)
-- Ana sorgu: Segment bazlı özet
SELECT 
    segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(total_spent), 0) AS avg_spent,
    SUM(order_count) AS total_orders
FROM customer_segments
GROUP BY segment
ORDER BY avg_spent DESC;

+----------+----------------+-----------+--------------+
| segment  | customer_count | avg_spent | total_orders |
+----------+----------------+-----------+--------------+
| VIP      |             15 |     75000 |         1200 |
| Premium  |             85 |     32000 |         4250 |
| Standart |            320 |     12000 |         6400 |
| Yeni     |            580 |      2500 |         1740 |
+----------+----------------+-----------+--------------+

Dikkat: İkinci CTE (customer_segments), birinci CTE'yi (customer_spending) kaynak olarak kullanıyor. CTE'ler sırasıyla tanımlanır ve önceki CTE'lere referans verebilir.

CTE'yi Birden Fazla Kez Kullanma

Subquery'nin en büyük dezavantajı: aynı hesaplamayı birden fazla yerde kullanman gerektiğinde kopyalaman gerekir. CTE bu sorunu çözer:

WITH monthly_revenue AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE YEAR(order_date) = 2024
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT 
    m.month,
    m.revenue,
    -- Aynı CTE'yi farklı amaçlarla kullan
    (SELECT AVG(revenue) FROM monthly_revenue) AS yearly_avg,
    m.revenue - (SELECT AVG(revenue) FROM monthly_revenue) AS diff_from_avg,
    (SELECT MAX(revenue) FROM monthly_revenue) AS best_month_revenue,
    (SELECT MIN(revenue) FROM monthly_revenue) AS worst_month_revenue
FROM monthly_revenue m
ORDER BY m.month;

monthly_revenue CTE'si 5 kez kullanılıyor — subquery ile yazsaydın aynı GROUP BY sorgusunu 5 kez tekrarlaman gerekirdi.

CTE vs Subquery vs Temporary Table

Özellik	CTE	Subquery	Temporary Table
Okunabilirlik	✅ Yüksek	❌ İç içe geçince düşük	✅ Yüksek
Yeniden kullanım	✅ Aynı sorgu içinde	❌ Her yerde tekrar yaz	✅ Session boyunca
Recursive	✅ Destekler	❌ Desteklemez	❌ Desteklemez
Performans	Genellikle inline edilir	Genellikle inline edilir	Disk/bellek kullanır
Kapsam	Tek sorgu	Tek sorgu	Session
Index	❌	❌	✅ Eklenebilir

💡 İpucu: CTE genellikle okunabilirlik aracıdır — performans açısından subquery ile eşdeğerdir (MySQL optimizer CTE'yi inline eder). Ama recursive CTE'ler farklıdır — onların subquery alternatifi yoktur.

CTE ile INSERT, UPDATE, DELETE

CTE sadece SELECT ile değil, DML ifadeleriyle de kullanılabilir:

-- CTE ile INSERT
WITH high_spenders AS (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 50000
)
INSERT INTO vip_customers (customer_id, added_date)
SELECT customer_id, CURDATE() FROM high_spenders;

-- CTE ile UPDATE
WITH inactive_customers AS (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING MAX(order_date) < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
)
UPDATE customers 
SET status = 'inactive'
WHERE customer_id IN (SELECT customer_id FROM inactive_customers);

-- CTE ile DELETE
WITH duplicate_orders AS (
    SELECT order_id,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id, order_date, total_amount
               ORDER BY order_id
           ) AS rn
    FROM orders
)
DELETE FROM orders 
WHERE order_id IN (
    SELECT order_id FROM duplicate_orders WHERE rn > 1
);

Recursive CTE — Hiyerarşik Verilerle Çalışma

Recursive CTE, kendi kendini çağıran bir CTE'dir. Hiyerarşik (ağaç yapısı) verileri sorgulamak için kullanılır: organizasyon şemaları, kategori ağaçları, dosya sistemi yapıları gibi.

Recursive CTE Yapısı

WITH RECURSIVE cte_adı AS (
    -- 1. Anchor (Başlangıç) sorgusu
    SELECT ... FROM tablo WHERE koşul
    
    UNION ALL
    
    -- 2. Recursive (Tekrarlayan) sorgu
    SELECT ... FROM tablo
    JOIN cte_adı ON ...  -- Kendi kendine referans!
)
SELECT * FROM cte_adı;

İki bölümden oluşur:

Anchor member: İlk çalışan sorgu — kök düğümleri (root nodes) bulur
Recursive member: Kendini çağıran sorgu — alt düğümleri bulur

Örnek 1: Organizasyon Şeması

-- employees tablosunda manager_id sütunu var
-- Her çalışanın yöneticisi başka bir çalışan

-- CEO'dan başlayarak tüm hiyerarşiyi çek
WITH RECURSIVE org_chart AS (
    -- Anchor: CEO (manager_id NULL olan)
    SELECT 
        employee_id, 
        first_name, 
        manager_id, 
        0 AS level,
        CAST(first_name AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: Alt çalışanları bul
    SELECT 
        e.employee_id, 
        e.first_name, 
        e.manager_id, 
        oc.level + 1,
        CONCAT(oc.path, ' → ', e.first_name)
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT 
    CONCAT(REPEAT('  ', level), first_name) AS hierarchy,
    level,
    path
FROM org_chart
ORDER BY path;

+------------------------+-------+------------------------------------+
| hierarchy              | level | path                               |
+------------------------+-------+------------------------------------+
| Ahmet                  |     0 | Ahmet                              |
|   Mehmet               |     1 | Ahmet → Mehmet                     |
|     Ali                |     2 | Ahmet → Mehmet → Ali               |
|     Zeynep             |     2 | Ahmet → Mehmet → Zeynep            |
|   Fatma                |     1 | Ahmet → Fatma                      |
|     Can                |     2 | Ahmet → Fatma → Can                |
|     Deniz              |     2 | Ahmet → Fatma → Deniz              |
|       Ece              |     3 | Ahmet → Fatma → Deniz → Ece       |
+------------------------+-------+------------------------------------+

Bu sorgu CEO'dan başlayıp tüm organizasyonu ağaç yapısında gösteriyor. level sütunu hiyerarşi derinliğini, path ise CEO'dan o kişiye kadar yolu gösteriyor.

Örnek 2: Kategori Ağacı

E-ticaret sitelerinde kategoriler genellikle ağaç yapısındadır:

-- categories tablosu: category_id, category_name, parent_id

WITH RECURSIVE category_tree AS (
    -- Kök kategoriler (parent_id NULL)
    SELECT 
        category_id, 
        category_name, 
        parent_id, 
        0 AS depth,
        CAST(category_name AS CHAR(500)) AS full_path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Alt kategoriler
    SELECT 
        c.category_id, 
        c.category_name, 
        c.parent_id, 
        ct.depth + 1,
        CONCAT(ct.full_path, ' > ', c.category_name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT 
    CONCAT(REPEAT('  ', depth), category_name) AS category,
    full_path,
    depth
FROM category_tree
ORDER BY full_path;

+---------------------------+-------------------------------------------+-------+
| category                  | full_path                                 | depth |
+---------------------------+-------------------------------------------+-------+
| Elektronik                | Elektronik                                |     0 |
|   Bilgisayar              | Elektronik > Bilgisayar                   |     1 |
|     Dizüstü               | Elektronik > Bilgisayar > Dizüstü         |     2 |
|     Masaüstü               | Elektronik > Bilgisayar > Masaüstü        |     2 |
|   Telefon                 | Elektronik > Telefon                      |     1 |
| Giyim                     | Giyim                                     |     0 |
|   Erkek                   | Giyim > Erkek                             |     1 |
|   Kadın                   | Giyim > Kadın                             |     1 |
+---------------------------+-------------------------------------------+-------+

Örnek 3: Belirli Bir Yöneticinin Tüm Ekibi

-- Mehmet'in (employee_id = 5) altındaki tüm çalışanlar
WITH RECURSIVE team AS (
    SELECT employee_id, first_name, manager_id, 0 AS level
    FROM employees
    WHERE employee_id = 5  -- Mehmet
    
    UNION ALL
    
    SELECT e.employee_id, e.first_name, e.manager_id, t.level + 1
    FROM employees e
    JOIN team t ON e.manager_id = t.employee_id
)
SELECT * FROM team WHERE level > 0;  -- Mehmet'in kendisi hariç

Örnek 4: Sayı Dizisi Üretme

Recursive CTE ile sayı dizisi oluşturabilirsin — tarih aralığı, test verisi gibi durumlar için kullanışlı:

-- 1'den 10'a kadar sayılar
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

-- Son 30 günün tarih listesi
WITH RECURSIVE date_range AS (
    SELECT CURDATE() AS dt
    UNION ALL
    SELECT dt - INTERVAL 1 DAY 
    FROM date_range 
    WHERE dt > CURDATE() - INTERVAL 30 DAY
)
SELECT dt FROM date_range ORDER BY dt;

Tarih dizisi özellikle "sipariş olmayan günleri de göster" raporlarında çok kullanışlı:

-- Son 30 gün — sipariş olmayan günler dahil
WITH RECURSIVE date_range AS (
    SELECT CURDATE() - INTERVAL 29 DAY AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_range WHERE dt < CURDATE()
)
SELECT 
    dr.dt AS order_date,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS daily_revenue
FROM date_range dr
LEFT JOIN orders o ON DATE(o.order_date) = dr.dt
GROUP BY dr.dt
ORDER BY dr.dt;

Recursive CTE'de Güvenlik — Sonsuz Döngü Riski

Recursive CTE yanlış yazılırsa sonsuz döngüye girebilir. MySQL varsayılan olarak 1000 iterasyondan sonra durur:

-- Varsayılan limit
SHOW VARIABLES LIKE 'cte_max_recursion_depth';
-- cte_max_recursion_depth = 1000

-- Limiti artır (gerekiyorsa)
SET SESSION cte_max_recursion_depth = 5000;

-- Ya da sorgu bazlı:
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 3000  -- 1000'den büyük
)
SELECT COUNT(*) FROM numbers;
-- cte_max_recursion_depth artırılmazsa hata verir

⚠️ Dikkat: Recursive CTE'de WHERE koşulunu unutma! Koşul olmadan veya yanlış koşulla sonsuz döngüye girer. cte_max_recursion_depth limiti güvenlik ağıdır ama buna güvenme — koşulun doğru yazıldığından emin ol.

Gerçek Dünya Örneği — Kapsamlı Müşteri Analizi

WITH 
-- Adım 1: Müşteri sipariş özeti
customer_orders AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent,
        AVG(total_amount) AS avg_order,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order,
        DATEDIFF(CURDATE(), MAX(order_date)) AS days_since_last
    FROM orders
    GROUP BY customer_id
),
-- Adım 2: Segmentasyon
customer_rfm AS (
    SELECT 
        customer_id,
        order_count,
        total_spent,
        days_since_last,
        -- Recency: Son sipariş ne zaman?
        NTILE(5) OVER (ORDER BY days_since_last ASC) AS recency_score,
        -- Frequency: Ne sıklıkta sipariş veriyor?
        NTILE(5) OVER (ORDER BY order_count DESC) AS frequency_score,
        -- Monetary: Ne kadar harcıyor?
        NTILE(5) OVER (ORDER BY total_spent DESC) AS monetary_score
    FROM customer_orders
),
-- Adım 3: Segment etiketleme
customer_segments AS (
    SELECT 
        *,
        recency_score + frequency_score + monetary_score AS rfm_total,
        CASE 
            WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 THEN 'Şampiyon'
            WHEN recency_score >= 4 AND frequency_score >= 3 THEN 'Sadık Müşteri'
            WHEN recency_score >= 3 AND monetary_score >= 4 THEN 'Yüksek Değerli'
            WHEN recency_score <= 2 AND frequency_score >= 3 THEN 'Risk Altında'
            WHEN recency_score <= 2 AND frequency_score <= 2 THEN 'Kayıp'
            ELSE 'Gelişen'
        END AS segment
    FROM customer_rfm
)
-- Final: Segment özeti
SELECT 
    segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(total_spent), 0) AS avg_lifetime_value,
    ROUND(AVG(order_count), 1) AS avg_orders,
    ROUND(AVG(days_since_last), 0) AS avg_days_inactive
FROM customer_segments
GROUP BY segment
ORDER BY avg_lifetime_value DESC;

Bu RFM (Recency, Frequency, Monetary) analizi, pazarlamada altın standarttır. 4 CTE adım adım inşa edildi — her biri kendi başına anlaşılır, hepsi birlikte güçlü bir analiz oluşturuyor.

PostgreSQL Farklılıkları

-- PostgreSQL'de MATERIALIZED hint
WITH customer_spending AS MATERIALIZED (
    -- PostgreSQL bu CTE'yi temporary table gibi saklar
    -- Birden fazla kez kullanılıyorsa performans artışı
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_spending WHERE total_spent > 10000;
-- MySQL'de MATERIALIZED hint yok — optimizer kendisi karar verir

-- PostgreSQL'de recursive CTE limit: yok (dikkatli ol!)
-- MySQL'de cte_max_recursion_depth = 1000 (varsayılan)

Özet

CTE (WITH ... AS) karmaşık sorguları okunabilir bloklara ayırır — subquery'nin temiz alternatifi
Birden fazla CTE virgülle ayrılarak tanımlanır — her biri öncekini kullanabilir
Aynı CTE, sorgu içinde birden fazla kez kullanılabilir — subquery'de tekrar yazman gerekirdi
CTE, SELECT, INSERT, UPDATE, DELETE ile kullanılabilir
Recursive CTE (WITH RECURSIVE) hiyerarşik verileri sorgulamak için — organizasyon şeması, kategori ağacı
Recursive CTE iki bölümden oluşur: anchor (başlangıç) + recursive (tekrarlayan) member
MySQL'de cte_max_recursion_depth varsayılan 1000 — sonsuz döngü koruması
Performans açısından CTE genellikle subquery ile eşdeğer — asıl avantajı okunabilirlik

Sıkça Yapılan Hatalar

Recursive CTE'de sonlandırma koşulunu unutmak — WHERE koşulu olmadan sonsuz döngü. cte_max_recursion_depth seni kurtarır ama hatayı düzelt.
CTE'nin performans sihri beklentisi — CTE genellikle subquery'ye çevrilir (inline), ayrı bir tablo oluşturulmaz. Performans artışı değil, okunabilirlik artışı sağlar.
CTE'de UNION yerine UNION ALL kullanmamak — Recursive CTE'de UNION ALL kullanılmalı. UNION tekrar kontrolü yapar ve performansı düşürür, ayrıca sonsuz döngü tespitini zorlaştırır.
CTE kapsamını yanlış anlamak — CTE sadece tanımlandığı sorgu içinde geçerli. Bir sonraki sorguda aynı CTE'yi kullanamazsın — tekrar tanımlaman gerekir.
Recursive CTE'de veri tiplerini eşlememek — Anchor ve recursive member'daki sütun tipleri uyumlu olmalı. CAST kullanarak tip uyumunu sağla.