Satırları bağıntılı alt sorgularla karşılaştırma

Tamamlandı

Bağıntılı sorgular, dış sorgu tarafından işlenen her satır için alt sorgunun bir kez yürütülmesine neden olan bir bağımlılık oluşturarak dış sorgudaki sütunlara başvuran alt sorgulardır. Bu verimsiz gibi görünebilir ancak bağıntılı sorgular, aksini ifade etmek zor veya imkansız olan satır satır karşılaştırmalar ve hesaplamalar için kullanışlıdır.

Bağıntılı alt sorgu yürütmeyi anlama

Bağıntılı alt sorgu dış sorgudaki bir veya daha fazla sütuna başvurur ve ikisi arasında mantıksal bağımlılık oluşturur. Bir kez yürütülen ve sabit bir sonuç döndüren normal bir alt sorgudan farklı olarak, bağıntılı bir alt sorgu, dış sorgunun işlediği her satır için bir kez art arda yürütülür.

Bunu iç içe döngü gibi düşünün: Dış sorgudaki her satır için veritabanı, bu satırın değerlerini kullanarak alt sorguyu değerlendirir. Bu davranış, güçlü satır satır karşılaştırmaları sağlar, ancak verimli sorgular yazmak için yürütme modelini anlamanız gerektiği anlamına da gelir.

Bu iki sorgu arasındaki farkı göz önünde bulundurun:

-- Non-correlated subquery (executes once)
SELECT ProductID, Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > (SELECT AVG(ListPrice) FROM SalesLT.Product);

-- Correlated subquery (executes per outer row)
SELECT p1.ProductID, p1.Name, p1.ListPrice
FROM SalesLT.Product AS p1
WHERE p1.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p1.ProductCategoryID  -- References outer query
);

İlişkili olmayan örnekte, alt sorgu tüm ürünler genelinde tek bir ortalama fiyat hesaplar. Bu değer bir kez hesaplanır ve her ürünün fiyatı bu sabit sayıyla karşılaştırılır.

Bağıntılı örnekte, alt sorgu, dış sorgudaki p1.ProductCategoryID'ye başvurur. Bu bir bağımlılık oluşturur: her ürün satırı için alt sorgu, söz konusu kategori için ortalama fiyatı hesaplar. "Bisikletler" kategorisindeki bir ürün bisiklet ortalamasına, "Aksesuarlar" içindeki bir ürün ise aksesuar ortalamasına göre karşılaştırılır.

Uyarı

Sorgu iyileştiricisi genellikle bağıntılı alt sorguları dahili olarak eşdeğer birleşimlere dönüştürür. Ancak, mantıksal bağıntılı davranışı anlamak, fiziksel yürütme farklı olsa bile doğru sorgular yazmanıza yardımcı olur.

Bağıntılı alt sorgularla filtreleme

Yan tümcesindeki bağıntılı WHERE alt sorgular, statik karşılaştırmalarla imkansız olabilecek satıra özgü filtreleme koşullarını etkinleştirir. Her satır, tek bir sabit değerle karşılaştırmak yerine, bu satırın özniteliklerine göre dinamik olarak hesaplanan bir değere göre değerlendirilir.

Bu düzen, gruplar içindeki aykırı değerleri tanımlamanız, kendi kategori eşiğini aşan kayıtları bulmanız veya bağlama göre değişen iş kuralları uygulamanız gerektiğinde kullanışlıdır. Aşağıdaki örnek, kategori ortalamasının üzerinde fiyatlanan ürünler bulur; bu da düşük fiyatlı bir aksesuarın pahalı olarak işaretlenebilirken, daha yüksek fiyatlı bir bisiklet tarafından işaretlenmeyebileceği anlamına gelir:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS Category
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > (
    SELECT AVG(p2.ListPrice)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
)
ORDER BY pc.Name, p.ListPrice DESC;

Davranışları kişisel taban çizgilerinden farklı olan müşterileri tanımlamak için aynı deseni uygulayabilirsiniz.

Aşağıdaki sorgu, olağan dışı satın alma düzenlerini veya yüksek değerli işlemleri tanımlamaya yardımcı olan kendi ortalama sipariş değerini aşan en az bir sipariş veren müşterileri bulur:

SELECT DISTINCT
    c.CustomerID,
    c.FirstName,
    c.LastName
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
WHERE soh.TotalDue > (
    SELECT AVG(soh2.TotalDue)
    FROM SalesLT.SalesOrderHeader AS soh2
    WHERE soh2.CustomerID = c.CustomerID
);

EXISTS bağıntılı alt sorgular ile kullanın

İşleç EXISTS bağıntılı bir alt sorguyla birlikte ilişkili bir tabloda eşleşen satırların olup olmadığını test eder ve basit bir doğru veya yanlış sonuç döndürür. Veritabanı altyapısı ilk eşleşen satırı bulur bulmaz aramayı durdurabildiğinden bu düzen son derece verimlidir. Gerçek verileri döndüren alt sorguların aksine, EXISTS yalnızca varlığını veya yokluğunu onaylaması yeterlidir.

"Hangi müşteriler sipariş verdi?" veya "Hangi ürünler hiç satılmamıştır?" gibi soruları yanıtlamanız gerektiğinde kullanın EXISTS . Gerçek değerler önemli olmadığından alt sorgu genellikle kullanır SELECT 1 :

-- Find customers who have placed at least one order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

-- Find customers who have never placed an order
SELECT CustomerID, FirstName, LastName
FROM SalesLT.Customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderHeader AS soh
    WHERE soh.CustomerID = c.CustomerID
);

EXISTS birden çok ölçütü birleştiren karmaşık koşulları denetlemeniz gerektiğinde daha da değerli hale gelir. Alt sorgunun içine herhangi bir filtreleme mantığı ekleyebilirsiniz ve dış sorgu yalnızca eşleşen en az bir ilgili satırın bulunduğu satırları içerir.

Aşağıdaki örneklerde, her ürünün fiyat eşiğini karşıladığı yüksek miktarlı siparişlere ve kategorilere sahip ürünleri bulma gösterilmektedir:

-- Find products that have been ordered in quantities greater than 10
SELECT p.ProductID, p.Name
FROM SalesLT.Product AS p
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.SalesOrderDetail AS sod
    WHERE sod.ProductID = p.ProductID
      AND sod.OrderQty > 10
);

-- Find categories where all products are priced above $100
SELECT pc.ProductCategoryID, pc.Name
FROM SalesLT.ProductCategory AS pc
WHERE NOT EXISTS (
    SELECT 1
    FROM SalesLT.Product AS p
    WHERE p.ProductCategoryID = pc.ProductCategoryID
      AND p.ListPrice <= 100
);

Tip

EXISTS genellikle, özellikle büyük tablolarda IN varlığı denetlerken alt sorgularda daha iyi performans gösterir. Optimizatör, EXISTS ile ilk eşleşmeyi bulduktan sonra durabilirken, IN eşleşen tüm değerleri alması gerekebilir.

içinde bağıntılı alt sorgularla değerleri hesaplama SELECT

Yan tümcesindeki bağıntılı SELECT alt sorgular, sonuç kümenizdeki her satır için ayrı bir değer hesaplar. Bu desen, ana satırın ayrıntılarıyla birlikte ilgili tablolardan toplanan veya türetilmiş değerleri, sonucu gruplara daraltmadan eklemenize olanak tanır.

Bu yaklaşım, kategorinin ortalama fiyatıyla birlikte her ürünü veya departmanın toplam saysıyla birlikte her çalışanı gösterme gibi bağlamsal bilgileri görüntülemeniz gerektiğinde kullanışlıdır. Alt sorgu, hesaplamayı filtrelemek için bu satırın değerlerini kullanarak satır başına bir kez yürütülür:

-- Show each product with its category's average price
SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS CategoryAvgPrice,
    p.ListPrice - (
        SELECT AVG(p2.ListPrice)
        FROM SalesLT.Product AS p2
        WHERE p2.ProductCategoryID = p.ProductCategoryID
    ) AS DifferenceFromAvg
FROM SalesLT.Product AS p;

Bu deseni, ilgili kayıtları saymak veya ilgili tablolardan belirli değerleri almak için de kullanabilirsiniz. Aşağıdaki sorgu, her müşterinin sipariş sayısını ve en son sipariş tarihini içeren ve her müşteri satırı için ayrı ayrı hesaplanan bir müşteri özeti oluşturur:

-- Show each customer with their order count
SELECT 
    c.CustomerID,
    c.FirstName,
    c.LastName,
    (
        SELECT COUNT(*)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS OrderCount,
    (
        SELECT MAX(soh.OrderDate)
        FROM SalesLT.SalesOrderHeader AS soh
        WHERE soh.CustomerID = c.CustomerID
    ) AS LastOrderDate
FROM SalesLT.Customer AS c;

Uyarı

Yan tümcesindeki bağıntılı SELECT alt sorgular tam olarak bir değer döndürmelidir. Alt sorgu birden çok satır döndürebiliyorsa, bunu , MAX()veya MIN()gibi SUM()bir toplama işlevine sarmalayın.

Bağıntılı alt sorgularla grup başına ilk N'yi bulma

Bağıntılı alt sorguların en pratik uygulamalarından biri, her grup içindeki en iyi N öğelerini bulmaktır. Bu düzen, "Her kategorideki en pahalı üç ürün hangileridir?" veya "her bölgede ilk beş satış temsilcisi kim?" gibi soruları yanıtlar.

Bağıntılı alt sorgu her satırı inceler ve aynı gruptaki diğer satır sayısını denetleyerek grubu için üst N'ye ait olup olmadığını belirler. Bu yaklaşım, pencere işlevleri kullanılamadığında veya pencere işlevlerinin ifade edemiyorsa karmaşık derecelendirme mantığına ihtiyacınız olduğunda iyi çalışır.

Aşağıdaki sorgu, kimlikleri kendi kategorisi için ilk 3'te görünen ürünleri seçerek kategori başına en pahalı üç ürünü bulur:

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID IN (
    SELECT TOP 3 p2.ProductID
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
    ORDER BY p2.ListPrice DESC
)
ORDER BY pc.Name, p.ListPrice DESC;

Alternatif bir yaklaşım, kaç öğenin geçerli satırdan daha yüksek olduğunu sayar. N öğeden daha az sayıda öğe daha yüksek bir değere sahipse, geçerli satır ilk N'dedir. Bu teknik, bağlantıları farklı şekilde işler ve N. konum için tüm öğelere ihtiyacınız olduğunda yararlı olabilir:

-- Find products that are in the top 3 by price within their category
SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE (
    SELECT COUNT(*)
    FROM SalesLT.Product AS p2
    WHERE p2.ProductCategoryID = p.ProductCategoryID
      AND p2.ListPrice > p.ListPrice
) < 3
ORDER BY pc.Name, p.ListPrice DESC;

Ardışık satırları karşılaştır

Bağıntılı alt sorgular, sıralama ölçütlerine göre önceki veya sonraki satırlardaki değerlere erişebilir, bu da dönem içi karşılaştırmaları ve eğilim analizini etkinleştirir. Bu düzen, ardışık kayıtlar arasındaki değişiklikleri hesaplamak için kullanışlıdır. Örneğin, her bir siparişi önceki sıralamayla karşılaştırmak veya değerlerin zaman içinde nasıl geliştiğini izlemek.

Alt sorgu, mantıksal dizideki geçerli satırdan önce (veya sonra) gelen satırları filtreleyerek ilgili bir satır bulur, ardından sonuçları hemen bitişik satırı alacak şekilde sıralar:

-- Show each order with the previous order's total
SELECT 
    soh.SalesOrderID,
    soh.OrderDate,
    soh.TotalDue,
    (
        SELECT TOP 1 soh2.TotalDue
        FROM SalesLT.SalesOrderHeader AS soh2
        WHERE soh2.CustomerID = soh.CustomerID
          AND soh2.OrderDate < soh.OrderDate
        ORDER BY soh2.OrderDate DESC
    ) AS PreviousOrderTotal
FROM SalesLT.SalesOrderHeader AS soh
ORDER BY soh.CustomerID, soh.OrderDate;

Tip

Ardışık satır karşılaştırmaları için, LAG() ve LEAD() gibi pencere işlevleri genellikle ilişkili alt sorgulardan daha verimli ve okunması daha kolaydır. Pencere işlevleri desteğinden daha karmaşık koşullara ihtiyacınız olduğunda bağıntılı alt sorgular kullanın.

Bağıntılı alt sorgular ve alternatifler arasında seçim yapma

Bağıntılı alt sorgular her zaman en iyi yaklaşım değildir. Aşağıdaki tablo doğru tekniği seçmenize yardımcı olur:

Bu yaklaşımı kullanın	Gerektiğinde...
Bağıntılı alt sorgular	Her satırı, bu satırın özniteliklerine göre dinamik olarak hesaplanan bir değerle karşılaştırın, ile EXISTS/NOT EXISTSvar olup olmadığını test edin veya karmaşık seçim mantığıyla satır başına tam olarak bir ilgili değer alın.
,'e Katılıyor	Birden çok tablodan sütunları alın veya satır başına hesaplamaların gerekmediği durumlarda, ilişkiler basitse.
Pencere işlevleri	LAG() / LEAD() ile artan toplamları, derecelendirmeleri hesaplayın veya önceki/sonraki satırlara erişin. Bu desenler için bağıntılı alt sorgulardan daha verimlidir.
CTE'ler	Aynı hesaplanan sonuda birden çok kez başvuruda bulunabilir veya karmaşık mantığı adlandırılmış, okunabilir adımlara bölebilirsiniz.

Performansla ilgili dikkat edilmesi gerekenler

Bağıntılı alt sorgular, doğru şekilde iyileştirilmediğinde performansı etkileyebilir. Alt sorgu dış sorgudaki her satır için bir kez yürütülür, çünkü kötü tasarlanmış bağıntılı sorgular büyük tablolarda binlerce veya milyonlarca alt sorgu yürütmesine neden olabilir.

Bağıntılı alt sorgu performansını iyileştirmek için şu yönergeleri izleyin:

• Bağıntı sütunlarında dizin oluşturma: Alt sorgunun WHERE dış sorguya geri bağlanan yan tümcesinde başvuruda bulunan sütunların dizine alındığından emin olun. Örneğin, alt sorgunuz üzerinde ProductCategoryIDfiltrelenirse, bu sütundaki bir dizin, veritabanının her dış satır için tablonun tamamını taramak yerine eşleşen satırları hızla bulmasını sağlar.

• Dizinlere ek sütunlar ekleyin: Alt sorgunuz diğer sütunlarda da filtreleniyor veya toplanmışsa bileşik bir dizin kullanmayı göz önünde bulundurun. üzerindeki (ProductCategoryID, ListPrice) bir dizin, tek bir dizin aramasında hem bağıntı aramasını hem de fiyat tabanlı filtrelemeyi veya toplamayı destekler.

• Alternatif yaklaşımları değerlendirin: Bağıntılı alt sorguların çoğu daha iyi performansa sahip birleşimler veya pencere işlevleri olarak yeniden yazılabilir. Grup başına en yüksek değeri buluyorsanız, pencere işlevi ROW_NUMBER(), genellikle her satır için MAX() seçen bağıntılı bir alt sorgudan daha iyi performans gösterir.

• Yürütme planlarını gözden geçirme: İyileştiricinin bağıntılı alt sorgunuzu nasıl işlediğini anlamak için gerçek yürütme planını kullanın SET STATISTICS IO ON ve inceleyin. İyileştirici bunu içsel olarak bir birleştirmeye dönüştürebilir veya yazıldıkça satır satır çalıştırabilir.

• Gerçekçi veri hacimleriyle test edin: Küçük test veri kümelerinde iyi performans gösteren bağıntılı alt sorgular, üretim boyutundaki tablolarla yavaşlayabilir. Üretime dağıtmadan önce her zaman temsili verilerle karşılaştırma.

Önemli

Büyük tablolarda bağıntılı alt sorgularla çalışırken yürütme planlarını her zaman gözden geçirin. İyileştirici bunları verimli bir şekilde dönüştürebilir, ancak karmaşık bağıntılar sorgu yeniden yazma işlemlerinden yararlanabilir.

Alt sorgular hakkında daha fazla bilgi için bkz. Alt Sorgular (Transact-SQL) ve EXISTS (Transact-SQL).