63  SQL With

With ifadeleri Ortak Tablo İfadeleri (Common Table Expressions CTE) olarak ta adlandırılır. Niçin View veya sanal-türetilmiş tablolar (alt sorgulardan) yerine CTE’ler kullanmalıyız?

İki tür CTE vardır.

  1. Sıradan
  2. Özyinelemeli

63.1 SQL With Sıradan

Sıradan CTE’ler görünümler (views) veya türetilmiş tablolar (alt sorgulardan) gibi davranırlar ancak onlara kıyasla daha kolay anlaşılırlar.

Sıradan CTE’leri oluşturmak kolaydır. Aşağıda basit bir örnek verilmiştir. WITH ile başlayıp geçici sonuç kümesinin adını veriyoruz. Parantezlerin arasına sorgumuzu yazıyoruz. Ardından parantezlerden sonra, sanki görünüm veya tabloymuş gibi daha önce verdiğimiz ismi kullanarak seçim yapabiliriz.

WITH Name
(
)
select * from Name

Aşağıda, Employee tablosunun self join örneğini kullanan ve bu temp tablosunu sorgulayan bir örnek görebilirsiniz.

WITH EmployeeManager as
(
SELECT E.EmployeeID
      ,E.LastName AS EmployerLastName
      ,E.FirstName AS EmployerFirstName
      ,E.ReportsTo
      , M.EmployeeID as ManagerID
      , M.LastName AS ManagerLastName
      ,M.FirstName AS ManagerFirstName
  FROM Employee E 
  INNER JOIN Employee M
  ON E.ReportsTo = M.EmployeeID 
)
SELECT * FROM EmployeeManager WHERE ManagerLastName LIKE 'A%'

Yukarıdaki WITH sorgusu işlevsel olarak, aşağıdaki FROM alt sorgusuna (FROM-sub-query) eşdeğerdir.

SELECT * FROM  
(
SELECT E.EmployeeID
      ,E.LastName AS EmployerLastName
      ,E.FirstName AS EmployerFirstName
      ,E.ReportsTo
      , M.EmployeeID as ManagerID
      , M.LastName AS ManagerLastName
      ,M.FirstName AS ManagerFirstName
  FROM Employee E 
  INNER JOIN Employee M
  ON E.ReportsTo = M.EmployeeID 
)
WHERE ManagerLastName LIKE 'A%'

CTE’lerin ilk avantajı, EmployeeManager gibi geçici sonuç kümemize anlamlı isimler verebilmemizdir.

CTE’lerin ikinci avantajı, sonuç kümesi tanımının sorgu başında olması sebebiyle, group by ve diğer ifadelerin doğal olarak bunu takip etmesidir.

CTE’lerin üçüncü avantajı, WITH ifadesindeki önceki sonuç kümelerini kullanarak yeni SQL cümlelerini yinelemeli olarak oluşturabilmemizdir.

Örneğin, satış temsilcilerimizden hangisinin en fazla satıştan sorumlu olduğunu bulmak istiyoruz.

WITH SALES_EMP_CUS as
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
)
SELECT * FROM SALES_EMP_CUS

Sadece müşterileri destekleyen satış çalışanlarını bularak başlıyoruz. Sonuç kümemize CustomerId’yi de ekliyoruz. Burada c.SupportRepId IS NOT NULL bize bu değerleri veriyor.

Daha sonra müşteri ID bilgisi ile fatura tablosunu birleştirerek müşteriye göre toplam satış rakamlarını elde ediyoruz.

WITH SALES_EMP_CUS AS
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
), 
CUSTOMER_SALES_TOTALS AS
(
    SELECT CustomerId, SUM(Total) as total_sales
    FROM Invoice
    GROUP BY CustomerId
)
SELECT * FROM CUSTOMER_SALES_TOTALS

Daha sonra son sonucumuzu bulmak için önceki sonuçlara üçüncü sorguyu ekleriz.

WITH SALES_EMP_CUS AS
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
), 
CUSTOMER_SALES_TOTALS AS
(
    SELECT CustomerId, SUM(Total) as total_sales
    FROM Invoice
    GROUP BY CustomerId
)
--SELECT * FROM CUSTOMER_SALES_TOTALS
SELECT SEC.EmployeeId, SUM(CST.total_sales) AS TotalSalesForEmployee 
FROM SALES_EMP_CUS AS SEC 
INNER JOIN CUSTOMER_SALES_TOTALS AS CST ON CST.CustomerId = SEC.CustomerId
GROUP BY SEC.EmployeeId;

Eğer üçüncü sonuç kümesi üzerinde daha fazla çalışmamız gerekirse, onu aşağıdaki gibi WITH ifadelerinin içine koyabiliriz.

WITH SALES_EMP_CUS AS
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
), 
CUSTOMER_SALES_TOTALS AS
(
    SELECT CustomerId, SUM(Total) as total_sales
    FROM Invoice
    GROUP BY CustomerId
),
EMP_SALES AS
(
SELECT SEC.EmployeeId, SUM(CST.total_sales) AS TotalSalesForEmployee 
FROM SALES_EMP_CUS AS SEC 
INNER JOIN CUSTOMER_SALES_TOTALS AS CST ON CST.CustomerId = SEC.CustomerId
GROUP BY SEC.EmployeeId

)
SELECT * FROM EMP_SALES

63.2 With Özyinelemeli (Recursive)

Oracle, 1980’lerde özyinelemeli sorgular için CONNECT BY operatörünü sunmuştur. Ancak özyinelemeli CTE’ler daha kullanışlıdır. Ve bu kullanım SQL99’da standartlaştırılmıştır. Bkz. modern sql sitesinde özyinelemeli sorgu veritabanı desteği.

Özyinelemeli CTE, seçme kısmında kendisine atıfta bulunan bir CTE’dir. Aşağıdaki gibi iki parçalı sql olarak yazabiliriz. Aşağıdaki sorguda başlangıç-select ve ozyinelemeli-select parçaları var ve bunlar UNION veya UNION ALL operatörleri kullanılarak birleştirilir.

WITH RECURSIVE cte_name AS (
başlangıç-select
UNION [ALL]
özyinelemeli-select
)
cte_sorgusu

Daha iyi bir örnek aşağıdadır.

WITH RECURSIVE cte_isim AS (
   SELECT ... FROM TABLE -- başlangıç-select
UNION ALL
   SELECT ... FROM cte_isim -- özyinelemeli-select, cte_isim'e yapılan kendi referansına dikkat edin
) 
SELECT ... FROM cte_isim --cte_sorgusu

İlk başlangıç-select cümlemizi normal şekilde yazarız.

Daha sonra, özyinelemeli-select ve cte_sorgusu kısımlarımızın her ikisi de cte_isim’imize atıfta bulunur.

Örnek 1a: sayı sayma

sqlite dokümantasyonundan değiştirilmiştir. Aşağıdaki sorgu 100’e kadar sayar. Bu sorguda cte_isim’in num olduğunu ve üç kez başvurulduğunu unutmayın.

WITH RECURSIVE num(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM num
LIMIT 100
)
SELECT x FROM num;

Dikkat edilmesi gereken ilk şey, RECURSIVE anahtar kelimesini WITH’e ekledik. RECURSIVE, sqlite, Oracle ve SQL Server’daki yinelemeli CTE’ler için gerekli değildir ancak sisteminizi daha sonra inceleyecek diğer kişiler için özyineleme durumunu açık hale getirmek için eklemek iyi bir fikirdir.

Örnek 1b: Sayıları tekrar sayma

Aynı örnek, ancak sütun adları cte_name(c1,c2)’de verilmemiştir. Ancak ilk seçimde takma ad olarak verilmiştir.

WITH RECURSIVE num AS (
SELECT 1 as x
UNION ALL
SELECT x+1 FROM num
LIMIT 100
)
SELECT x FROM num;

Örnek 2 sayıların toplanması

Sayıları toplamaya yönelik bir örnek daha oluşturalım.

WITH RECURSIVE TotalSum AS (
  SELECT
    0 AS Count,
    0 AS Total
  UNION ALL
  SELECT
    Count + 1,
    Total + Count
  FROM TotalSum
  WHERE Count <= 100
)
SELECT * FROM TotalSum;

63.3 Önerilen okumalar