Her Yönüyle CTE(Common Table Expression) Kavramı

Öncelikle CTE'nin tanımını yapalım. CTE(Common Table Expression), geçici tablo yada türemiş tablo gibi davranan, DB'de object (tablo, sp, view vs..) gibi depolonmayan ve tek bir SELECT, INSERT, UPDATE, DELETE veya CREATE VIEW ifadelerinin derhal peşine(!) kullanılan geçici sonuç kümelerine denir. Türetilmiş tabloların aksine, CTE'ler sorgu içinde kendine referans edilebilir ve yine kendisini birden fazla kez çağırabilir. (Varsayılan olarak üst limit 100 defadır ancak bu sayıyı isteğe göre arttırabilirsiniz.) En basit haliyle CTE nin yazım şekli şöyledir;

 

WITH CTE_ismi(sütun isimleri)

 

AS

(cte_sorgusu)

 

SELECT|INSERT|UPDATE|DELETE...FROM...CTE_ismi

 

Nasılki bir stored procedure, view ya da tablo tanımlarken "create view" şeklinde cümleye başlıyoruz, CTE tanımlarken'de cümlemiz WITH anahtar sözcüğü ile başlar.

Örnek uygulamalara başlamadan önce aşağıdaki 2 tabloyu veritabanınıza kayıt etmenizi rica ediyorum.

   

CREATE TABLE tblEmployee

(

Id int not null primary key,

Name varchar(50),

Gender varchar(10),

DepartmentID int

)

 

GO

 

INSERT INTO tblEmployee(Id, Name, Gender, DepartmentID) VALUES

 

(1, 'John', 'Male', 3),

(2, 'Mike', 'Male', 2),

(3, 'Pam', 'Female',1),

(4, 'Todd', 'Male', 4),

(5, 'Sara', 'Female', 1),

(6, 'Ben', 'Male',3)

 

CREATE TABLE tblDepartment

(

DeptId int not null primary key,

DeptName varchar(50)

)

 

GO

 

INSERT INTO tblDepartment(DeptId, DeptName) VALUES

(1, 'IT'),

(2, 'Payroll'),

(3, 'HR'),

(4, 'Admin')

 

 

 

 

Resim-1'de belirtilen sorguya dikkat edecek olursak CTE tanımlarken kontrol etmemiz gereken en önemli husus, AS'den sonra gelen parantez içi ana sorgu'dan dönen kolon sayısı ile, CTE içinde tanımladığınız kolon sayısının aynı olmasıdır ve tabi bunların karşılıklı sıralaması da benzer olmalıdır çünkü birbirleriyle eşlenik bir hal alıyorlar. Siz içerdeki sorguyu çalıştırarak geçici bir sorgu kümesi oluşturuyorsunuz ve bunu CTE ile tanımlanan bölüme atıyorsunuz. Daha sonra ise tanımladığınız CTE'yi çağırarak bunu işleme sokuyorsunuz. Dikkat edilmesi gereken diğer bir husus ise CTE yi çağırdıktan sonra şayet kendisini bir tablo gibi işleme sokacaksanız(join gibi), bu hususta CTE tanımlarken yazdığınız parantez içi değerlerinin aşağıdaki çağırma işleminde olduğu gibi kolon isimlerinin aynı olmasıdır. Zira CTE bu aşamadan sonra bir tablo gibi davranacaktır ve sizin yukarda tanımlamış olduğunuz kolon isimlerine bağlı oluyor olmanız beklenmektedir.

Önemli not: CTE tanımladıktan sonra parantez içine sütun adı yazmak mecburu değildir. Bunu yazarak sadece algıda seçicilik vurgulanıyor.

 

 

Şimdi dilerseniz, makalenin en başında italik harflerle yapmış olduğum CTE nin tanımından yola çıkarak bir örnek yapalım.  

 

En baştaki CTE tanım cümlemizden yola çıkacak olursak; CTE ler yaratıldıktan sonra derhal çağrılmalıdır ve araya başka herhangi bir sorgu girmemelidir. Aksi takdirde ana sorgunuz çalışmayacaktır. Resim-2'deki örneğe bakacak olursak araya yazmış olduğum 'Merhaba' Select'i bu durumla benzeşen bir örnektir ve yazım şekli olarak bilinmesinde fayda olacağını düşünüyorum.

 

 

Multiple CTE Kavramı

 

Bir CTE içersinde birden fazla CTE sorgusu aralara virgül koyularak yazılabilir. Daha sonra bu sorgular SET operatörleri ile (UNION ALL, UNION, EXCEPT yada INTERSECT gibi) birleştirilir.Örneğin;

 

WITH EmployeesCountBy_Payrol_IT(DepartmentName, Total)

AS

(

SELECT d.DeptName, COUNT(Id) as TotalEmployees

FROM tblEmployee e JOIN tblDepartment d on e.DepartmentID= d.DeptId

WHERE d.DeptName IN ('Payroll', 'IT')

GROUP BY d.DeptName

),

EmployeesCountBy_HR_Admin(DepartmentName, Total)

AS

(

SELECT d.DeptName, COUNT(e.Id) as TotalEmployees

FROM tblEmployee e JOIN tblDepartment d on e.DepartmentID= d.DeptId

WHERE d.DeptName IN ('HR','Admin')

GROUP BY d.DeptName

)

 

SELECT* FROM EmployeesCountBy_HR_Admin

UNION

SELECT* FROM EmployeesCountBy_Payrol_IT

 

 

Bu şekilde aralara virgül koyarak birden fazla CTE sorgusu yazmanız mümkün.

 

 

 Bir CTE güncellenebilir mi ?

 

Bu sorunun cevabı hem evet hem de hayır'dır. Şayet CTE sorgunuz içersinde yazan tablolardan sadece biri bu update'den etkileniyor ise pekâla olabilir ancak birden fazla tablo bu durumdan etkileniyor ise güncelleme olmaz. Örneğin;

WITH Employees_Name_Gender

AS

(

SELECT Id, Name, Gender

FROM tblEmployee

)

 

SELECT* FROM Employees_Name_Gender

 

 

Yukarıdaki sorgunun sonuç kümesi için Resim-3'ü inceleyiniz.

 

Şimdi bu sonuç kümesini CTE sorgusu üzerinden update edelim:

 

WITH Employees_Name_Gender

AS

(

SELECT Id, Name, Gender FROM tblEmployee

)

UPDATE Employees_Name_Gender

SET Gender = 'Female' WHERE Id = 1

 

Sonuç aşağıdaki gibi olacaktır.

 

Bakınız Resim-4.

 

Gördüğünüz gibi sorgu sonucumuz başarılı şekilde geldi.

 

Peki. 2 tabloyu birleştirip update işlemi yapalım birde:

 

Bakınız Resim-5.

 

Bu sorgudan da anlaşılacağı üzere 2 adet ana tabloya sahip bir CTE'de update işleminden etkilenen tablo sayısı 1 adet olduğu için işlemimiz başarılı bir şekilde gerçekleşti.

 

Peki sorgumuzu Resim-6'daki gibi güncellersek sonuç ne olur ?

 

Gördüğünüz gibi sorgumuz çalışmadı. Çünkü update sorgumdan farklı 2 tablo etkileniyor..

Şimdide farklı bir update örneğine bakacağız. Resim-7'yi inceleyecek olursak;

 

Bu örnekte where koşulunda belirttiğimiz Id = 1 eklentisi PK gibi değil tıpkı bir etiket(label) gibi çalışır ve departman tablosunu gözönüne alarak, bölümü HR olanların tümünü IT ye çevirir.

 

CTE için Update işlemlerini toparlayacak olursak;

 

1) Eğer update işleminin bağlı olduğu tablo adedi bir ise dolayısı ile etkilenecek tablo adedi de haliyle 1 ise, UPDATE işlemi problemsiz çalışır.

2) Şayet CTE sorgusunda birden fazla tablo var ise ve UPDATE işlemi bu birden fazla tabloyu etkiliyor ise UPDATE işlemine izin verilmez ve sorgu sonucu hata ekranı gelir.

3) Eğer bir CTE birden fazla tablo sorgusu içeriyorsa ve UPDATE den etkilenecek tablo adedi 1 ise, UPDATE sorunsuz çalışır ancak istenilen sonucu alamazsınız. (Son örneğimizde olduğu gibi..)

 

Recursive CTE Yapısı

 

Recursive CTE, sorgu içersinde kendisini çağıran CTE tipine denir. Bu tip örnekler daha çok çalışan-yönetici arasındaki hiyerarşiyi kurmak için kullanılırlar.

 

 

Elimizde örnek veri olması açısından aşağıdaki sorguyu çalıştırmanızı rica ediyorum.

 

CREATE TABLE Employee

(  

Id int not null primary key,  

Name varchar(50),

ManagerID int  

)  

 

GO

 

INSERT INTO Employee(Id, Name, ManagerID) VALUES

(1, 'Tom',  2),

(2, 'Josh',  NULL),  

(3, 'Mike',2),  

(4, 'John',  3),  

(5, 'Pam',  1),  

(6, 'Mary', 3),

(7, 'James',  1),

(8, 'Sam', 5),

(9, 'Simon',1)

 

 

Resim-8'de görmüş olduğunuz şekil bir self-join örneğidir. Biz şimdi bu örnek üzerinden CTE kullanarak bir çalışan-yönetici hiyerarşisi kuracağız. En tepe yöneticiden başlayarak aşşağıya doğru sıralı olarak numaralandırma yapacağız.  

 

 

Resim-9'daki şekli inceleyecek olursak; içerdeki sorguda CTE nin çağrıldığını görüyoruz (CTE nin Recursive özelliğinden ötürü) ve hiyerarşik olarak sayısal değer ataması yapabilmek için ana tabloyla çağırdığımız CTE yi birleştiriyoruz. NULL olan satır, hiyerarşinin tepe noktasında olacağı içinde başlangıç olarak 1 değerini atıyoruz. Bir diğer önemli husus; [Level] anahtar sözcüğü dışarıdan bir sütun olarak eklendiği için yazım kuralı olarak '[]' aralığında ifade edilmiştir.

CTE ile ilgili olarak benim anlatacaklarım bu kadar. Umuyorumki faydalı bir yazı olmuştur. Bir sonraki yazımda görüşmek üzere.