Common Table Expression(CTE)

TSQL Kod Geliştiriciler bir query yazarken bazen aynı kod bloğunu birden fazla yerde kullanmak isteyebilirler. Bazen bu query’yi çok karmaşıklaştırabilir. Tekrar kullanılmak istenilen kod bloğunu Common Table Expression(CTE) oluşturarak ve bu CTE’ye bir alias vererek kod bloğunun sonraki kısımlarında bu alias’ı sanki bir tabloymuş gibi çağırabilir, join işlemlerine sokabiliriz.

Aynı işlemi view kullanarak da gerçekleştirebilirsiniz. Eğer uygulamanız bu kod bloğunu sürekli kullanacaksa view oluşturmak daha mantıklıdır. Ama uygulamanın sürekli kullanacağı bir kod bloğu değilse view yerine CTE’yi tercih edebiliriz.

CTE’de sadece SELECT ifadesi değil INSERT,UPDATE ve DELETE’lerde kullanılabilir.

CTE’lerin içinde ORDER BY(Sorguda TOP ifadesi varsa kullanılabilir),INTO,FOR BROWSE ve OPTION kullanılamaz.

Aşağıda AdventureWorks veritabanı üzerinde çalıştırılan basit bir CTE örneğini görebilirsiniz. AS() arasındaki kod bloğuna CTE_Ornek ismini verdik ve daha sonra bu CTE’yi bir tabloymuş gibi sorguladık.

WITH CTE_Ornek (SalesPersonID, SalesOrderID, SalesDay)
AS
(
    SELECT SalesPersonID, SalesOrderID, DAY(OrderDate) AS SalesDay
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesDay
FROM CTE_Ornek
GROUP BY SalesDay, SalesPersonID
ORDER BY SalesDay,SalesPersonID;

CTE tanımlarken yukarıdaki örnekte gördüğünüz gibi CTE isminden sonra parantez içersinde 3 tane kolon tanımladık ve CTE’yi oluşturan kod bloğunda da 3 tane kolon okuduk. Select içeriğinde dönecek kolonları CTE’yi tanımlarken tanımlamasak bile çalışacaktır fakat tanımlamak kodun okunabilirliği açısından daha iyi olacaktır.

Bir CTE içersinde aşağıdaki gibi birden fazla CTE tanımlanabilir. “,” ile ayrılır ve bu iki cte’yi birleştirirken

UNION ALL, UNION, INTERSECT, veya EXCEPT ifadelerinden birini kullanmanız gerekir.

;WITH CTE_Ornek (SalesPersonID, SalesOrderID, SalesDay)
AS
(
    SELECT SalesPersonID, SalesOrderID, DAY(OrderDate) AS SalesDay
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
),
CTE_Ornek2 (SalesPersonID, SalesOrderID, SalesDay)
AS
(
    SELECT SalesPersonID, SalesOrderID, DAY(OrderDate) AS SalesDay
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT * FROM CTE_Ornek
UNION
SELECT * FROM CTE_Ornek2

CTE’lerin en sık kullanıldıkları alan recursive CTE’dir. Recursive CTE kendi kendini referans eder. Genelde hiyerarşiyi belirlemek için kullanılır. Bir örnek yapmak için hiyerarşik bilgilerin tutulduğu Personel isminde bir tablo oluşturalım ve içine birkaç kayıt ekleyelim.

USE [Test]
GO
CREATE TABLE [dbo].[Personel](
[PersonelID] [smallint] NOT NULL,
[Ad] [nvarchar](30) NOT NULL,
[Soyad] [nvarchar](40) NOT NULL,
[Unvan] [nvarchar](50) NOT NULL,
[DepartmanID] [smallint] NOT NULL,
[YoneticiID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Personel] ([PersonelID], [Ad], [Soyad], [Unvan], [DepartmanID], [YoneticiID]) VALUES (1, N'Nurullah', N'ÇAKIR', N'Veritabanı Tabanı Birimi Takım Lideri', 1, 2)
GO
INSERT [dbo].[Personel] ([PersonelID], [Ad], [Soyad], [Unvan], [DepartmanID], [YoneticiID]) VALUES (2, N'Hayriye', N'SULUGÖZ', N'Veritabanı Müdürü', 1, NULL)
GO
INSERT [dbo].[Personel] ([PersonelID], [Ad], [Soyad], [Unvan], [DepartmanID], [YoneticiID]) VALUES (3, N'Faruk', N'Erdem', N'Veritabanı Birimi Uzman Personel', 1, 1)
GO
INSERT [dbo].[Personel] ([PersonelID], [Ad], [Soyad], [Unvan], [DepartmanID], [YoneticiID]) VALUES (4, N'Kemal', N'Sevim', N'Veritabanı Birimi Personel', 1, 3)

Tabloda kişilerin ID’si ve  kişilerin yöneticilerinin ID’si bizim için önemli olacak. Böyle bir hiyerarşi tablomuz varken ilgili personelin en üst sekmeye kadar yöneticilerinin listesine ihtiyacımız olabilir. Normalde bir select sorgusu ile bir üst yöneticisini bulabiliriz ama yöneticisinin yöneticisi şeklinde en üst basamağa kadar bütün yöneticilerine ihtiyacımız olursa bu işlemi recursive CTE ile yapmamız gerekir. Insert işlemlerinden de gördüğünüz üzere Kemal Sevim isimli kişi bu tablodaki hiyerarşide en altta bulunuyor. Aşağıdaki scrip’te Personel ID kısmına Kemal Sevim’in personel ID’sini yazalım ve üst yöneticilerini görelim. Daha sonra Faruk Erdem’in ID değerini yazalım ve onu görelim.

with CTE as
(
      select PersonelID,YoneticiID,Unvan from dbo.Personel
      where PersonelID=4
      union all
      select prs.PersonelID,prs.YoneticiID,prs.Unvan from dbo.Personel prs join CTE
      on prs.PersonelID=CTE.YoneticiID
)
select * from CTE

Gördüğünüz gibi Kemal Sevim’in üstündeki yöneticileri sırasıyla listeledik.

Aynı işlemi Faruk Erdem için yapalım.

Gördüğünüz gibi Faruk Erdem için yaptığımızda Kemal Sevim gelmedi. Çünkü Kemal Sevim, Faruk Erdem’in yöneticisi değil. TSQL kod geliştiriciler Recursive CTE’leri en çok bu amaçla kullanırlar.