SQLite CTE: WITH Sorgusu Kullanımı ve Örnekleri

CTE Dediğimiz Şey Aslında İsimlendirilmiş Bir Alt Sorgudur

SQLite'ta CTE (Common Table Expression), dışarı çıkarıp bir isim verdiğiniz alt sorgudur. SELECT içine başka bir SELECT gömmek yerine, sorgunun en başında WITH ile tanımlar, ona bir isim verir ve ana sorguda o ismi sanki bir tabloymuş gibi kullanırsınız.

Kalıp her zaman aynıdır:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

Yukarıdan aşağıya okuyun: önce customer_totals adında bir sonuç kümesi oluşturuluyor, ardından bu sonuç sorgulanıyor. CTE, yalnızca o tek sorgu süresince var olan geçici bir görünüm gibi davranır.

Aynı Sorgunun CTE'siz Hâli

Aşağıda aynı mantık alt sorgu kullanılarak yazılmış; böylece CTE'nin neyin yerini aldığını net olarak görebilirsiniz:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

Aynı sonuç. Ama okuma sırasına dikkat edin: gözünüz önce parantezlerin içine dalıp orada ne hesaplandığını çözmek, sonra dışarı çıkmak zorunda. CTE'li sürüm ise işin yapıldığı sıraya göre okunuyor — önce ara sonucu tanımla, sonra kullan. Küçük bir sorguda bu farkı pek hissetmezsiniz. Ama üç dört adımlı bir sorguda, göz gezdirebileceğiniz kod ile çözmek zorunda kaldığınız kod arasındaki farkı yaratır.

Tek Sorguda Birden Fazla CTE

Birden fazla CTE'yi virgülle ayırarak arka arkaya zincirleyebilirsiniz. Her CTE, kendisinden önce tanımlananlara erişebilir; böylece isimlendirilmiş adımlardan oluşan bir boru hattı kurmuş olursunuz:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

Tek bir WITH yazıyorsunuz, ardından CTE tanımlarını virgülle ayırıyorsunuz. İkinci CTE (big_spenders), ilk CTE'den (customer_totals) sanki bir tabloymuş gibi okuma yapıyor. Asıl SELECT ise en son CTE tanımının ardından geliyor.

Sık yapılan bir hata: ikinci CTE'nin önüne tekrar WITH yazmak. Sakın yapmayın — söz dizimi hatası alırsınız. Tek bir WITH, tüm CTE'leri kapsar.

Aynı CTE'yi Birden Fazla Kez Kullanmak

CTE'lerin alt sorgulara karşı asıl farkını ortaya koyduğu nokta tam da burası. Aynı ara sonucu iki farklı yerde kullanmanız gerekiyorsa, CTE bunu bir kez hesaplayıp iki kez referans almanıza olanak tanır:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

CTE burada iki kez kullanılıyor: önce ortalamayı hesaplamak için, sonra ana veri kaynağı olarak. CTE olmasaydı GROUP BY sorgusunu çoğaltmak zorunda kalırdınız ve sorguda yapılacak en ufak değişiklik için iki ayrı yeri güncellemeniz gerekirdi.

INSERT, UPDATE ve DELETE ile CTE kullanımı

CTE'ler sadece SELECT sorgularına özel değildir. INSERT, UPDATE veya DELETE ifadelerinin başına da WITH cümlesi koyarak isimlendirilmiş bir alt sorguyu yazma işlemlerinde kullanabilirsiniz:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

CTE hangi satırların işaretleneceğini tanımlar; INSERT ... SELECT ise onu kaynak olarak kullanır. Aynı yöntem DELETE FROM ... WHERE id IN (SELECT id FROM cte) şeklinde, hedefleme mantığının karmaşık olduğu aşamalı silme işlemlerinde de işe yarar.

SQLite'ta Ne Zaman CTE Kullanılır?

Pratik birkaç ipucu:

• Sorgu birden fazla mantıksal adımdan oluşuyorsa. Önce gruplama, sonra grup sonucuna göre filtreleme, ardından join — bu tam bir işlem hattıdır ve her adım için ayrı bir CTE okunabilirliği ciddi şekilde artırır.
• Aynı alt sorguyu tekrar tekrar yazacaksanız. Bir kez tanımlayın, iki kez referans verin.
• Alt sorgu bir isim hak ediyorsa. Alt sorgunun başına ne anlama geldiğini açıklayan bir yorum yazacak olsaydınız, CTE'nin adı zaten o yorumdur — üstelik bunu sözdizimi zorunlu kılar.
• Özyinelemeli (recursive) bir sorgu yazmak üzereyseniz. Bu yalnızca WITH RECURSIVE ile mümkün; sonraki sayfada ele alıyoruz.

CTE'ye gerek olmayan durumlar:

• Tek bir yerde kullanılan basit bir alt sorgu. WHERE id IN (SELECT id FROM ...) olduğu gibi yeterlidir.
• Performansın kritik olduğu ve mantığı satır içi yazmanın daha hızlı olduğunu zaten doğruladığınız sorgular. SQLite, CTE'leri bazı diğer veritabanlarına göre daha az katı bir "optimizasyon duvarı" olarak ele alır; ama sıcak yollarda EXPLAIN QUERY PLAN ile kontrol etmekte fayda var.

Uçtan Uca Bir Örnek

Hepsini birleştirelim — her müşterinin en büyük siparişini bulan ve bunu kendi ortalamasıyla karşılaştıran küçük bir rapor:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

İki ayrı CTE, her biri tek bir iş yapıyor. Asıl SELECT ise sonucu biçimlendiriyor. Sorguyu yukarıdan aşağıya okuyup her adımı tek başına anlayabiliyorsunuz — zaten CTE kullanmanın bütün amacı bu.

Sırada: Özyinelemeli (Recursive) CTE'ler

Buraya kadar gördüklerimiz hep klasik CTE'lerdi: bir kez değerlendirilen, isim verilmiş alt sorgular. SQLite ayrıca WITH RECURSIVE sözdizimini de destekler; burada CTE kendi kendine referans vererek hiyerarşilerde gezinebilir, dizi üretebilir veya graf üzerinde dolaşabilir. Bunu bir sonraki sayfada ele alacağız.