Türetilmiş Tablolar Kullanmak – Bölüm 37

Bu bölümde, dış sorgunun FROM ifadesinde türetilmiş tablolar oluşturarak sorgular yazacağız. Ayrıca, türetilmiş tablo içerisinde tanımlanan tablo ifadesinden sonuç döndürmeyi de öğreneceğiz.

Türetilmiş Tablolarla Sorgu Yazmak

Önceki bölümlerde alt sorgular hakkında bilgi edindik. Alt sorguda olduğu gibi dış sorguda FROM ifadesinde türetilmiş tablolar oluşturulabilir. Adlandırılmış ifade ile oluşturulan türetilmiş tablolar normal bir tabloya mantıksal olarak eşdeğerdir ve alt sorgudan farklı olarak dış sorguda herhangi bir yerde bir tablo olarak kullanılabilir. Türetilmiş tablolar kullanarak daha modüler T-SQL ifadeleri yazabilir ve karmaşık sorguları daha kullanışlı hale getirebilirsiniz. Türetilmiş tablolar kullanarak sütun takma adlarının kullanılamaması gibi mantıksal işlem sırasının bazı kısıtlamaları için de geçici çözümler sağlanabilir.

Türetilmiş tablo oluşturmak için parantez içine iç sorgu tanımlanır ardından türetilmiş tablo için AS ile bir takma ad oluşturun:

SELECT <outer query column list>
FROM (SELECT <inner query column list>
 FROM <table source>) AS <derived table alias> 

Aşağıdaki örnekte, türetilmiş tablo kullanılarak farklı müşteriler tarafından verilen bir yıllık sipariş verileri getirilmiştir. İç sorguda sipariş kümesi oluşturulur ve türetilmiş tablonun yıl sütununa yerleştirir. Dış sorgu türetilmiş tabloyu ve sonuçları özetler:

SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
 FROM Sales.Orders) AS derived_year
GROUP BY orderyear; 

Dönen sonuç:

orderyear	cust_count
2006	67
2007	86
2008	81

(3 row(s) affected)

Sorguda türetilmiş tablolar kullanırken aşağıdakileri göz önünde bulundurun:

• Türetilmiş tablolar veri tabanında saklanmaz. Bu nedenle türetilmiş tabloları kullanarak sorguları yazmak için, veri kaynağı nesnelere erişim dışında özel güvenlik ayrıcalığı gerektirmez.
• Dış sorgunun yürütülmesi sırasında türetilmiş tablo oluşturulur ve sona erdiğinde yok edilir.
• Aynı sorgu farklı şekilde yazıldığında türetilmiş tabloların performans üzerinde herhangi bir etkisi olmayabilir. Sorgu çalıştırıldığında ifade ortaya çıkarılır ve kullanılan veri tabanı nesnelerindeki ifadeler kullanılarak değerlendirilir.

Türetilmiş Tablolar için Yönergeler

Türetilmiş tablolar kullanan sorgular yazarken aşağıdaki yönergeleri dikkate almakta fayda var:

• Türetilmiş tablonun tanımlandığı iç içe SELECT ifadesi için takma ad tanımlanmalıdır. Dış sorguda, aynı FROM ile birleştirilen diğer tabloların takma adlarıyla kullanıldığı gibi türetilmiş tablonun takma adı da SELECT ifadesinde kullanılabilir.
• Best practice olarak türetilmiş tablonun kullanılan tüm sütunlarına SELECT ifadesinde takma adlar tanımlanmalıdır. İfadedeki her takma ad birbirinden farklı olmalıdır. Sütun takma adları sütunlarla birlikte veya ifadeye harici olarak tanımlanabilir. Bir sonraki bölümde bunun örneklerini göreceğiz.
• Türetilmiş tablo ifadesinin tanımlandığı SELECT ifadesinde TOP, OFFSET/FETCH veya FOR XML deyimleri  kullanılmadığı sürece ORDER BY deyimi kullanılamaz. Neticede türetilmiş tablo tarafından sağlanan sıralama düzeni yok. Sonuçları dış sorguda sıralayabilirsiniz.
• Türetilmiş tablonın tanımlandığı SELECT ifadesi, parametreleri yerel değişken şeklinde alabilecek şekilde yazılabilir. SELECT ifadesi bir stored procedure’e gömülüyse, bağımsız değişkenler stored procedure için parametre olarak yazılabilir. Bunun örneklerini daha sonraki bölümlerde göreceğiz.
• Dış sorguda gömülü olan türetilmiş tablo ifadeleri, başka bir türetilmiş tablo ifadelerini içerebilir. Fakat karmaşıklığı artıracağı ve okunabilirliği azaltacağı için yapılması önerilmez.
• Bir türetilmiş tablo, dış sorguda defalarca kullanılamaz. Aynı sonuçların manipüle edilmesi gerekiyorsa her seferinde türetilmiş tablo ifadesinin tanımlanması gerekir (tabloların JOIN operatörünün her iki tarafında tanımlanması gerektiği gibi).

Türetilmiş Tablo Sütunları İçin Takma Adlar Kullanma

Takma adlar oluşturmak için iki yöntem bulunur (satır içi veya harici).

Takma adları satır içi (sütunu belirtirken) tanımlamak için aşağıdaki syntax’ı kullanın.

SELECT <outer query column list>
FROM (SELECT <col1> AS <alias>, <col2> AS
<alias>...
 FROM <table_source>);

Aşağıdaki örnekte YEAR işlevi ve custid sütun sonuçları için takma adları satır içi belirtilmiştir:

SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
 FROM Sales.Orders) AS derived_year
GROUP BY orderyear; 

İç sorgudan dönen sonuç aşağıdaki gibidir:

orderyear	custid
2006	85
2006	79
2006	34

İç sorgudan gelen sonuç dış sorguya verilir. Dış sorgu, gelen sütun değerleri üzerinde çalışarak yeni bir sonuç üretir:

orderyear	cust_count
2006	67
2007	86
2008	81

Türetilmiş tablolarda harici takma adları, kullanmak için aşağıdaki syntax’ı kullanın:

SELECT <outer query column list>
FROM ( SELECT <col1>, <col2>..
 FROM <table_source>) AS <derived_table_alias>(<col1_alias>, <col2_alias>); 

Aşağıdaki örnekte orderyear ve custid sütunları için harici takma adı tanımlaması kullanmıştır:

SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate), custid
 FROM Sales.Orders) AS derived_year(orderyear, custid)
GROUP BY orderyear; 

Not: Harici takma adlar kullanılırken iç sorgu ayrı yürütülürse, takma adlar dış sorguya döndürülmez. Test edilebilirlik ve okunabilirlik açısından harici takma adlar yerine satır içi takma adları kullanmanız önerilir.

Bağımsız Değişkenleri Türetilmiş Tablolara Aktarma

SQL Server’da türetilmiş tablolar, çağrılan yordamlarla (T-SQL batch, fonksiyon veya stored procedure gibi) iletilen bağımsız değişkenler alabilir. Türetilmiş tablolar yerel değişkenlerle kullanılabilir. Runtime sırasında bu değişken değerleri, batch ile sağlanan değerlerle veya sorguyu çalıştıran stored procedure’e inpur olarak verilen değerlerle değiştirilebilir. Böylece aynı sorguyu her seferinde farklı değerlerle yeniden yazmak yerine bu şekilde tekrar kullanılabilir.

Not: İlerleyen bölümlerde parametre alan fonksiyon ve stored procedure kullanımını ele alacağız.

Örneğin aşağıdaki batch’te, çalışan kimliği için yerel değişken (@ ile belirtilen) tanımlanmış. Ardından, SQL Server 2008 ve sonraki sürümlerde mümkün olan, aynı ifadede değişkene bir değer atanmıştır. Sorguda @emp_id değişkeni oluşturulup türetilmiş tablo ifadesinde kullanılmıştır:

DECLARE @emp_id INT = 9; --değişken tanımlanması ve değer atanması
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (
 SELECT YEAR(orderdate) AS orderyear, custid
 FROM Sales.Orders
 WHERE empid=@emp_id --değeri türetilmiş tabloya geçirmek için kullanılan değişken
) AS derived_year
GROUP BY orderyear;
GO 

Sonuçlar:

orderyear	custid
2006	5
2007	16
2008	16

Not: İlerleyen bölümlerde değişken tanımlama, T-SQL batch ve stored procedure’ler hakkında daha fazla bilgi edineceğiz.

İç içe Türetilmiş Tablolar ve Tekrar Kullanılması

Türetilmiş tablonun kendisi bir sorgu ifadesi olduğundan kendi içerisinde bir türetilmiş tablo kullanabilir. Kod bakımı ve okunabilirlik nedeniyle önerilmez.

Örneğin, aşağıdaki sorguda türetilmiş tablo bir diğerinin içine yerleştirilmiştir:

SELECT orderyear, cust_count
FROM (
 SELECT orderyear, COUNT(DISTINCT
custid) AS cust_count
 FROM (
 SELECT YEAR(orderdate) AS orderyear ,custid
 FROM Sales.Orders) AS derived_table_1
 GROUP BY orderyear) AS derived_table_2
WHERE cust_count > 80; 

Mantıksal olarak, önce en içteki sorgu işlenir ve sonuçları derived_table_1 olarak döndürür:

orderyear	custid
2006	85
2006	79
2006	34

Ardından, ortadaki sorgu çalışır ve sonuçları gruplandırıp bir araya getirerek derived_table_2 olarak döndürür:

orderyear	cust_count
2006	67
2007	86
2008	81

Son olarak dış sorgu çalışır ve çıktıyı filtreler:

orderyear	cust_count
2007	86
2008	81

Görüldüğü üzere, türetilmiş tabloları iç içe yerleştirirken karmaşık düzeyi artmaktadır.

Aynı türetilmiş tabloyu dış sorguda birden çok ifadede kullanmak zor olabilir. Tablo ifadesi FROM deyiminde tanımlandığından sorgunun sonraki aşamaları tabloyu görebilir, ancak aynı FROM ifadesinde başka yerde kullanılamaz.

Örneğin, bir FROM ifadesinde tanımlanan türetilmiş tablo WHERE ifadesinde kullanılabilir, ancak onu tanımlayan aynı FROM ifadesindeki JOIN deyiminde kullanılamaz. Türetilmiş tablo ayrı tanımlanmalı ve tanımın birden çok kopyası tutulmalıdır. Türetilmiş tablo tanımının ayrı kopyalarını tutmaya gerek kalmadan yeniden kullanılmasını sağlayan alternatif bir yapı olan CTE yapısını sonraki bölümde ele alacağız.