SQL'de CTE: Örneklerle Eksiksiz Kılavuz

SQL CTE Nasıl Oluşturulur

Bir CTE oluştururken, tanımı başlatmak için WITH anahtar sözcüğünü kullanırız. Bir CTE'nin genel sözdizimi şöyledir:

WITH cte_name (column1, column2, ...) AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Burada:

• WITH: CTE tanımını başlatır.
• cte_name: CTE'ye atanan ad (daha sonra referans vermek için kullanılır).
• İsteğe bağlı sütun listesi: CTE'nin sonuç kümesi için sütun adlarını belirtir.
• Ana sorgu: CTE'yi adıyla referans alır ve onu normal bir tablo gibi işler.

Bir örneğe bakalım. Employees adlı bir tablomuz olduğunu ve maaşı 50.000 $'ın üzerinde olan çalışanları seçmek istediğimizi varsayalım.

Adım 1: Temel sorguyu yazın

Önce temel SELECT sorgusunu yazarız:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Adım 2: CTE oluşturmak için sorguyu WITH ile sarmalayın

WITH anahtar sözcüğünü kullanarak CTE'ye bir ad verin.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Adım 3: CTE'yi ana sorguda kullanın

Son olarak, yukarıda tanımlanan CTE adını çağırarak bir SELECT ifadesinde CTE'ye başvurun.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Yukarıdaki adımları özetlemek gerekirse, WITH anahtar sözcüğünü kullanarak HighEarningEmployees adlı CTE'yi tanımladık. İç sorgu, geçici veri kümesini üretmek için kullanıldı. Ana sorgu, HighEarningEmployees'a referans vererek belirtilen EmployeeID, FirstName ve LastName sütunlarını görüntüler.

SQL CTE'ler Neden Faydalıdır

Yukarıdaki örnekten, basit sorgular bile aynı sonuçları verirken neden CTE kullandığımızı merak edebilirsiniz. Nedenler şunlardır:

Karmaşık sorguları basitleştirme

CTE'ler, karmaşık SQL ifadelerini daha küçük ve yönetilebilir parçalara ayırarak kodun okunmasını, yazılmasını ve bakımını kolaylaştırır. 

Diyelim ki üç tablomuz var: Orders, Customers ve Products. 2024'te satın alma yapan her müşterinin oluşturduğu toplam geliri bulmak istiyoruz. CTE kullanmadan sorguyu yazdığımızda, karmaşık ve okunması zor bir görünüm ortaya çıkar.

-- Standard SQL: Hard to read nested logic
SELECT 
    c.CustomerName, 
    SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
JOIN Customers c 
    ON o.CustomerID = c.CustomerID
JOIN Products p 
    ON o.ProductID = p.ProductID
WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Bir CTE kullanarak mantığı daha okunur bir biçimde ayırabiliriz. Önce "filtreleme ve birleştirme" adımını izole eder, ardından toplulaştırmayı yaparız.

-- Standard SQL: Cleaner with CTE
WITH OrderDetails AS (
    SELECT 
        o.OrderID, 
        c.CustomerName, 
        p.Price, 
        o.Quantity, 
        o.OrderDate
    FROM Orders o
    JOIN Customers c 
        ON o.CustomerID = c.CustomerID
    JOIN Products p 
        ON o.ProductID = p.ProductID
    WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Kodun yeniden kullanılabilirliği

CTE'ler, aynı sonuç kümesinin yeniden kullanılmasına olanak tanıyarak tekrarları önlemeye yardımcı olur. Bir toplamı (ör. sum) hesaplamanız ve ardından bu toplam üzerinden filtrelemeniz gerekiyorsa, CTE çok uygundur.

Her ürün kategorisi için ortalama ve toplam satışları hesaplamamız gerektiğini varsayalım. Hesaplamayı bir kez bir CTE'de tanımlarız:

WITH CategorySales AS (
    SELECT 
        Category, 
        SUM(SalesAmount) AS TotalSales, 
        AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select from the CTE where the pre-calculated TotalSales is high
SELECT 
    Category, 
    TotalSales, 
    AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Diğer uygulamalar

Sorguları sadeleştirmeye ve kodun yeniden kullanılabilirliğine ek olarak, CTE'lerin başka kullanım alanları da vardır. CTE'lerin tüm olası kullanım alanlarını ayrıntılı biçimde ele alamıyorum. Pratiğe devam etmek istiyorsanız, Data Manipulation in SQL kursumuz harika bir seçenek. Ancak burada başlıca diğer nedenlerden bazılarını listeleyeceğim:

• Sorgu Organizasyonu ve Okunabilirlik: CTE'ler sorguları mantıksal, sıralı adımlara bölerek SQL kodunun okunabilirliğini artırır. Sorgu sürecindeki her adım kendi CTE'siyle temsil edilebilir; bu da tüm sorgunun takip edilmesini kolaylaştırır.
• Hiyerarşik Veride Gezinme: CTE'ler, organizasyon yapıları, ebeveyn-çocuk ilişkileri veya iç içe seviyeler içeren herhangi bir veri modeli gibi hiyerarşik ilişkilerde gezinmeye yardımcı olabilir. Kendine yinelemeli CTE'ler, seviyeleri yinelemeli olarak dolaşmanıza izin verdiği için hiyerarşik verileri sorgulamada kullanışlıdır.
• Çok Seviyeli Toplulaştırmalar: CTE'ler, farklı ayrıntı seviyelerinde (ör. ay, çeyrek ve yıl bazında) satış rakamlarını hesaplamak gibi çok seviyeli toplulaştırmalar yapmaya yardımcı olabilir. Bu toplulaştırma adımlarını CTE'lerle ayırmak, her seviyenin bağımsız ve mantıklı bir şekilde hesaplanmasını sağlar.
• Birden Fazla Tablodan Veriyi Birleştirme: Birden fazla CTE, farklı tablolardan verileri birleştirmek için kullanılabilir; bu da nihai birleştirme adımını daha yapılandırılmış hale getirir. Bu yaklaşım, karmaşık join'leri sadeleştirir ve kaynak verinin daha iyi okunabilirlik için mantıksal olarak düzenlenmesini sağlar.

İleri Düzey SQL CTE Teknikleri

CTE'ler ileri düzey SQL tekniklerini destekler; bu da onları farklı kullanım senaryoları için çok yönlü ve kullanışlı kılar. Aşağıda CTE'lerin bazı ileri uygulamaları yer alır.

Tek bir sorguda birden çok CTE

Tek bir sorguda birden fazla CTE tanımlayabilirsiniz; bu da karmaşık dönüşümler ve hesaplamalara olanak tanır. Bu yöntem, her CTE'nin ayrı bir aşamayı temsil ettiği, birden fazla veri işleme aşaması gerektiren problemlerde faydalıdır.

Diyelim ki Sales adlı bir tabloda satış verilerimiz var ve her ürün için toplam satışları hesaplamak, toplam satışları ortalamanın üzerinde olan ürünleri belirlemek ve bu ürünleri toplam satışlarına göre sıralamak istiyoruz.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
), 
AverageSales AS (
    -- Step 2: Calculate the average of those totals
    -- Note: We can reference the previous CTE (ProductSales) here
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
), 
HighSalesProducts AS (
    -- Step 3: Filter products above the average
    SELECT ps.ProductID, ps.TotalSales
    FROM ProductSales ps
    CROSS JOIN AverageSales av
    WHERE ps.TotalSales > av.AverageTotalSales
)
-- Step 4: Rank the results
SELECT 
    ProductID, 
    TotalSales, 
    RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Yukarıdaki örnekte:

• İlk CTE (ProductSales) ürün başına toplam satışı hesaplar.
• İkinci CTE (AverageSales) tüm ürünlerdeki toplam satışların ortalamasını hesaplar.
• Üçüncü CTE (HighSalesProducts) toplam satışı ortalamanın üzerinde olan ürünleri filtreler.
• Son sorgu bu ürünleri toplam satışlarına göre sıralar.

UPDATE, DELETE ve MERGE İfadelerinde CTE'ler

UPDATE, DELETE ve MERGE işlemlerine dahil edildiğinde, CTE'ler özellikle karmaşık filtreler veya hiyerarşik verilerle çalışırken veri işleme görevlerini basitleştirebilir.

CTE'yi UPDATE ifadesiyle kullanma

Employees tablosunda bir EmployeeSalary sütunumuz olduğunu varsayalım. Şirkette 5 yıldan fazla çalışmış tüm çalışanlara %10 zam vermek istiyoruz.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    -- Standard SQL: Compare HireDate to 5 years before today
    WHERE HireDate <= CURRENT_DATE - INTERVAL '5' YEAR
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.10
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

LongTermEmployees CTE'si, 5 yıldan fazla çalışmış çalışanları belirler. UPDATE ifadesi bu CTE'yi kullanarak maaşları seçici biçimde artırır.

CTE'yi DELETE ifadesiyle kullanma

Şimdi, Products adlı bir tablomuz olduğunu ve son 2 yılda satılmamış tüm ürünleri silmek istediğimizi varsayalım. Ürünleri filtrelemek için bir CTE kullanabiliriz:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Standard SQL: Filter for dates older than 2 years ago
    WHERE LastSoldDate < CURRENT_DATE - INTERVAL '2' YEAR
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

OldProducts CTE'si, son iki yılda satılmamış ürünleri belirler; ardından DELETE ifadesi bu ürünleri kaldırmak için bu CTE'yi kullanır.

CTE'yi MERGE ifadesiyle kullanma

SQL'deki MERGE ifadesi, kaynak tablodaki verilere göre hedef tabloda koşullu güncelleme, ekleme veya silme yapılmasını sağlar. Aşağıdaki örnekte, MergedInventory CTE'si yeni ve mevcut envanter verilerini birleştirir. Ardından MERGE ifadesi, CTE verilerine göre mevcut ürünlerin miktarlarını günceller veya yeni ürünler ekler.

-- CTE to prepare the source data for the merge
WITH MergedInventory AS (
    SELECT 
        ni.ProductID, 
        ni.Quantity AS NewQuantity
    FROM NewInventoryData ni
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS target
USING MergedInventory AS source
    ON target.ProductID = source.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) 
    VALUES (source.ProductID, source.NewQuantity);

Kendine Yinelemeli Ortak Tablo İfadeleri (CTE'ler)

Kendine yinelemeli CTE'ler, tanımları içinde kendilerine referans veren özel bir CTE türüdür; bu da sorgunun yinelenen işlemler gerçekleştirmesine olanak tanır. Bu özellik, organizasyon şemaları gibi hiyerarşik verilerle çalışmak için idealdir.

Kendine yinelemeli CTE'lere giriş

Kendine yinelemeli CTE'ler, tanımı içinde kendisine referans veren özel bir CTE türüdür; bu sayede sorgu yinelenen işlemler yapabilir. Bu, organizasyon şemaları, dizin yapıları veya ürün montajları gibi hiyerarşik ya da ağaç yapısındaki verilerle çalışmak için idealdir. Kendine yinelemeli CTE veriyi yinelemeli olarak işler; özyinelemeli üye yeni satır döndürmeyene (sonlandırma koşulu) kadar adım adım sonuç üretir.

Çapa ve özyinelemeli üyeler

Kendine yinelemeli bir CTE iki ana bölümden oluşur:

• Çapa Üyesi: Özyinelemenin başladığı temel sorguyu tanımlayan kısım.
• Özyinelemeli Üye: CTE'nin kendisine referans veren ve "özyinelemeli" işlemleri yapmasını sağlayan kısım.

Employees adlı bir tablomuz olduğunu ve her satırın bir EmployeeID, EmployeeName ve ManagerID içerdiğini varsayalım. Belirli bir yönetici için tüm doğrudan ve dolaylı bağlıları bulmak istiyorsak, en üst düzey yöneticiyi belirleyen çapa üyesiyle başlarız.

Not: Standart SQL'de (PostgreSQL, MySQL, SQLite) RECURSIVE anahtar sözcüğünü kullanmanız gerekir.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    
    UNION ALL
    
    -- Recursive member: find employees who report to the current managers
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
-- Select the final result from the CTE
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Nasıl çalışır:

1. Çapa: Sorgu önce Çapa Üyesini çalıştırır ve kimliği 1 olan çalışanı bulur.
2. Özyineleme: Özyinelemeli Üye, ManagerID'si bir önceki adımda bulunan EmployeeID ile eşleşen çalışanları arar.
3. Döngü: Bu süreç (Seviye 1 Seviye 2'yi, Seviye 2 Seviye 3'ü bulur) yeni çalışan kalmayana kadar tekrar eder.

SQL'de CTE'lerin Olası Sorunları veya Sınırlamaları

CTE'lerin özelliklerini ve sınırlamalarını anlamak, mantıklı ve okunur sorgular yazmak için önemlidir. Farklı veritabanlarında CTE kullanımının bazı sınırlamalarına ve olası sorunlarına bakalım.

SQL Server ve Azure sınırlamaları

SQL Server veya Azure Synapse Analytics ile çalışırken SQL CTE'ler için ortama özgü bazı sınırlamalar vardır. Şunları içerir:

• SQL Server: Kendine yinelemeli CTE'ler için varsayılan maksimum yineleme seviyesi 100'dür. Bu sınır ayarlama yapılmadan aşılırsa hata oluşur. CTE tanımları doğrudan başka bir CTE tanımı içinde iç içe olamaz (ancak birden fazla CTE'yi art arda zincirleyebilirsiniz).
• Azure Synapse Analytics: Destek, belirli havuz türüne göre değişir. Kendine yinelemeli CTE'ler şu anda Dedicated SQL Pools'ta (eski adıyla SQL DW) desteklenmez. Ancak Serverless SQL Pools'ta desteklenir. Ayrıca bazı DML işlemleri (CTE'lerle UPDATE veya DELETE gibi) standart SQL Server'a kıyasla sözdizimi kısıtlamalarına sahip olabilir.

SQL Server ile çalışıyorsanız, DataCamp'in bu konuda birçok iyi kaynağı olduğunu bilin. Başlangıç için, veri analizi amacıyla SQL Server'ın temellerine hakim olmak üzere DataCamp'in Introduction to SQL Server kursunu almanızı öneririm. İşlemler ve hata yönetiminden zaman serisi analizine kadar her şeyi kapsayan SQL Server Developer kariyer yolumuzu deneyebilirsiniz. Hierarchical and Recursive Queries in SQL Server kursumuz ise CTE'leri içeren yöntemler de dahil olmak üzere SQL Server'da gelişmiş sorguların nasıl yazılacağını doğrudan ele alır.

Diğer olası sorunlar

CTE'ler karmaşık sorguları sadeleştirmede yararlı olsa da dikkat edilmesi gereken bazı yaygın tuzaklar vardır. Şunları içerir:

• Kendine Yinelemeli CTE'lerde Sonsuz Döngüler: Kendine yinelemeli bir CTE için sonlandırma koşulu sağlanmazsa, sorgu süresiz çalışarak sonsuz döngüye girebilir. Kendine yinelemeli CTE'nin sonsuza dek çalışmasını önlemek için, yinelemeli yineleme sayısını sınırlamak amacıyla OPTION (MAXRECURSION N) ipucunu kullanın; burada N belirlenen sınırdır.

  • Nasıl düzeltilir: SQL Server'da, yinelemeli yineleme sayısını sınırlamak için OPTION (MAXRECURSION N) ipucunu kullanın. PostgreSQL'de döngüleri otomatik olarak tespit etmek için CYCLE yan tümcesini kullanabilirsiniz.

• Performansla İlgili Hususlar: Özyineleme derinliği yüksekse veya büyük veri kümeleri işleniyorsa kendine yinelemeli CTE'ler kaynak tüketimi açısından yoğun olabilir. Performansı iyileştirmek için, her yinelemede işlenen veriyi sınırlayın ve aşırı özyineleme seviyelerini önlemek üzere uygun filtreleme uygulayın.

Performans: CTE'ler ve Alt Sorgular

Yaygın bir efsane, CTE'lerin doğası gereği alt sorgulardan daha hızlı olduğudur. Gerçekte, çoğu modern sorgu iyileştirici (SQL Server ve PostgreSQL gibi) standart CTE'leri "inline" eder; yani alt sorgularla tamamen aynı şekilde işler ve performans farkı olmaz.

Bununla birlikte, CTE'ler maddileştirme yoluyla performans artışı sağlayabilir; burada veritabanı CTE sonucunu bir kez hesaplar ve ana sorgu içinde birden çok kez başvurulması için önbelleğe alır.

CTE'leri öncelikle okunabilirlik için kullanmanızı öneririm. Performans kazanımları durumsaldır ve belirli veritabanınızın önbellekleme davranışına bağlıdır.

CTE'ler Ne Zaman, Diğer Teknikler Ne Zaman Kullanılır?

CTE'ler, tekrarlanan görevleri içeren sorguları sadeleştirmek için uygundur; ancak türetilmiş tablolar, görünümler ve geçici tablolar da benzer amaçlara hizmet eder. Aşağıdaki tablo, her yöntemin avantaj ve dezavantajlarını ve ne zaman kullanılacağını vurgular.

Sonuç

CTE'lerde ustalaşmak, her şeyde olduğu gibi, pratik gerektirir: Yetkin bir veri analisti olmak için DataCamp'in Associate Data Analyst in SQL kariyer yolunu denemenizi öneririm. Reporting in SQL kursu da etkili veri sunumu için karmaşık raporlar ve panolar oluşturmada yetkinleşmenize yardımcı olur. Son olarak, SQL'i iş problemlerini çözmek için kullanma becerinizi sergilemek ve diğer profesyoneller arasında öne çıkmak için SQL Associate Certification sertifikasını almanızı öneririm.