Üç kat iç içe alt sorgusu olan bir sorguyu okumak, bir bulmacayı içeriden dışarıya çözmeye benzer. Programlamada böyle anlarda ara sonuca bir ad verir ve onu bir değişkene koyarız. SQL'de bunun karşılığı CTE'dir (common table expression, ortak tablo ifadesi): sorguyu adlandırılmış adımlara böleriz ve her adım öncekileri kullanabilir. Üstelik bir CTE kendine de başvurabilir; bu, hiyerarşiler ve diziler için güçlü bir araçtır.
WITH: ara sonuca ad vermek
CTE, ana sorgudan önce WITH ad AS (sorgu) biçiminde yazılır ve yalnızca o sorgu çalışırken var olur. Alt sorguya göre büyük üstünlüğü, aynı CTE'ye birkaç kez başvurulabilmesidir. Aşağıda customer_totals iki yerde kullanılıyor: hem birleştirmede hem de ortalama tutarı hesaplayan alt sorguda.
WITH customer_totals AS (
SELECT o.customer_id, SUM(o.quantity * p.price) AS total
FROM orders AS o
JOIN products AS p ON p.id = o.product_id
GROUP BY o.customer_id
)
SELECT c.name, ROUND(ct.total, 2) AS total
FROM customer_totals AS ct
JOIN customers AS c ON c.id = ct.customer_id
WHERE ct.total > (SELECT AVG(total) FROM customer_totals)
ORDER BY ct.total DESC;Beklenen çıktı
name | total Anar Mustafayev | 1723 Emre Yılmaz | 941.99 Zəhra Hüseynli | 935.99
CTE zinciri: adım adım sorgu
Tek bir WITH içinde virgülle ayrılmış birkaç CTE yazılabilir ve her biri kendinden öncekilere başvurabilir. Böylece bir “veri hattı” oluşur: 1) her siparişin tutarını ve ülkesini hesapla, 2) ülkelere göre topla, 3) her ülkenin toplam gelirdeki payını bul.
WITH lines AS (
SELECT c.country, o.quantity * p.price AS amount
FROM orders AS o
JOIN products AS p ON p.id = o.product_id
JOIN customers AS c ON c.id = o.customer_id
),
by_country AS (
SELECT country, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
FROM lines
GROUP BY country
)
SELECT country, orders, revenue,
ROUND(100.0 * revenue / (SELECT SUM(revenue) FROM by_country), 1) AS share_pct
FROM by_country
ORDER BY revenue DESC;Beklenen çıktı
country | orders | revenue | share_pct Azerbaijan | 7 | 2843.49 | 72.1 Türkiye | 3 | 973.59 | 24.7 United Kingdom | 1 | 69.98 | 1.8 Russia | 1 | 55 | 1.4
Özyinelemeli CTE: kendine başvuran sorgu
Özyinelemeli CTETanım
WITH RECURSIVE ile yazılan ve iki bölümden oluşan CTE: başlangıç bölümü (anchor) ilk satırları üretir, UNION ALL sonrasındaki özyinelemeli bölüm ise CTE'nin kendisine başvurarak önceki adımın satırlarından yenilerini kurar. Özyinelemeli bölüm yeni satır döndürmediğinde süreç durur.
WITH RECURSIVE numbers(n) AS (
SELECT 1 -- anchor
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5 -- step + stop
)
SELECT n, n * n AS square
FROM numbers;Beklenen çıktı
n | square 1 | 1 2 | 4 3 | 9 4 | 16 5 | 25
Böyle bir dizi ne işe yarar? Raporda boş grupları da göstermeye. Örneğin puanların 10'ar puanlık aralıklara dağılımı: GROUP BY yalnızca verisi olan aralıkları döndürür. Aralıkları özyinelemeli bir CTE ile üretip LEFT JOIN yaparsak kimsenin düşmediği 40–49 aralığı da 0 ile görünür.
WITH RECURSIVE buckets(low) AS (
SELECT 40
UNION ALL
SELECT low + 10 FROM buckets WHERE low < 90
)
SELECT b.low || '-' || (b.low + 9) AS score_range,
COUNT(e.id) AS enrollments
FROM buckets AS b
LEFT JOIN enrollments AS e ON e.score BETWEEN b.low AND b.low + 9
GROUP BY b.low
ORDER BY b.low;Beklenen çıktı
score_range | enrollments 40-49 | 0 50-59 | 1 60-69 | 2 70-79 | 5 80-89 | 4 90-99 | 8
Hiyerarşide gezinmek
Özyinelemeli CTE'lerin klasik kullanımı ağaç yapılarıdır: organizasyon şeması, kategoriler ve alt kategoriler, klasörler. Her çalışanın satırında yöneticisinin id değeri (manager_id) tutulur. Başlangıç bölümü yöneticisi olmayan müdürü alır, özyinelemeli bölüm ise her adımda bir düzey aşağı iner ve yolu (path) uzatır.
CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, role TEXT,
manager_id INTEGER REFERENCES staff(id));
INSERT INTO staff VALUES
(1, 'Samir', 'Director', NULL),
(2, 'Ramin', 'Head of Science', 1),
(3, 'Nərmin', 'Head of Humanities', 1),
(4, 'Ülviyyə', 'Physics teacher', 2),
(5, 'Elnur', 'Informatics teacher', 2),
(6, 'Sara', 'English teacher', 3);
WITH RECURSIVE chain(id, name, level, path) AS (
SELECT id, name, 0, name
FROM staff
WHERE manager_id IS NULL
UNION ALL
SELECT s.id, s.name, c.level + 1, c.path || ' > ' || s.name
FROM staff AS s
JOIN chain AS c ON s.manager_id = c.id
)
SELECT level, path
FROM chain
ORDER BY path;Beklenen çıktı
level | path 0 | Samir 1 | Samir > Nərmin 2 | Samir > Nərmin > Sara 1 | Samir > Ramin 2 | Samir > Ramin > Elnur 2 | Samir > Ramin > Ülviyyə
ORDER BY path ağacı klasör düzeninde sıralar: her yöneticinin altında onun çalışanları.| VTYS | Sıradan CTE | Özyinelemeli CTE |
|---|---|---|
| SQLite | WITH | WITH RECURSIVE |
| PostgreSQL | WITH | WITH RECURSIVE |
| MySQL 8.0+ | WITH | WITH RECURSIVE |
| SQL Server | WITH | WITH (RECURSIVE sözcüğü yazılmaz) |
;WITH yazılır.Alıştırma
course_stats adlı bir CTE oluştur: her ders için course_id, kayıt sayısı (students) ve ortalama puan (avg_score). Sonra onu courses ile birleştir ve en az 3 öğrencisi olan derslerin adını, öğrenci sayısını ve virgülden sonra 1 basamağa yuvarlanmış ortalama puanını ortalamaya göre azalan sırada göster.
WITH course_stats AS (
-- course_id, students, avg_score
)
SELECT c.title, cs.students, ROUND(cs.avg_score, 1) AS avg_score
FROM course_stats AS cs
JOIN courses AS c ON c.id = cs.course_id
-- filter and sortBeklenen çıktı
title | students | avg_score Python Basics | 3 | 92.7 Algebra | 4 | 87.3 Geometry | 3 | 81.3 Mechanics | 4 | 80
Alıştırma
Özyinelemeli bir CTE ile 0, 100, 200, 300, 400 fiyat aralıklarını (low) üret. Her aralığa düşen ürün sayısını (products) göster: ürün, low <= price < low + 100 olduğunda o aralıktadır. Boş aralıklar da 0 ile görünmeli. Sonucu low değerine göre sırala.
WITH RECURSIVE brackets(low) AS (
SELECT 0
UNION ALL
-- next bracket, stop at 400
)
SELECT b.low, COUNT(p.id) AS products
FROM brackets AS b
-- LEFT JOIN products
GROUP BY b.low
ORDER BY b.low;Beklenen çıktı
low | products 0 | 6 100 | 1 200 | 0 300 | 1 400 | 0
Unutma
- CTE (
WITH ad AS (...)), yalnızca tek bir sorgu için var olan adlandırılmış bir ara sonuçtur. - Aynı CTE'ye birkaç kez başvurulabilir; virgülle ayrılmış CTE'ler öncekileri kullanabilir.
- Özyinelemeli CTE = başlangıç bölümü +
UNION ALL+ kendine başvuran bölüm + durma koşulu. - Özyinelemeli CTE'ler sayı dizileri, boş grupları gösteren raporlar ve hiyerarşiler (organizasyon şeması, kategoriler) için kullanılır.
- SQL Server'da özyinelemeli CTE
RECURSIVEsözcüğü olmadan yazılır ve varsayılan olarak 100 adımla sınırlıdır.