Jämför rader med korrelerade underfrågor

Slutförd

Korrelerade frågor är underfrågor som refererar till kolumner från den yttre frågan, vilket skapar ett beroende som gör att underfrågan körs en gång för varje rad som bearbetas av den yttre frågan. Även om detta kan låta ineffektivt är korrelerade frågor användbara för jämförelser och beräkningar rad för rad som är svåra eller omöjliga att uttrycka på annat sätt.

Förstå hur korrelerade underfrågor utförs

En korrelerad underfråga refererar till en eller flera kolumner från den yttre frågan, vilket skapar ett logiskt beroende mellan de två. Till skillnad från en vanlig underfråga som körs en gång och returnerar ett fast resultat körs en korrelerad underfråga upprepade gånger, en gång för varje rad de yttre frågeprocesserna.

Tänk på det som en kapslad loop: för varje rad i den yttre frågan utvärderar databasen underfrågan med hjälp av radens värden. Det här beteendet möjliggör kraftfulla jämförelser rad för rad, men det innebär också att du måste förstå exekveringsläget för att skriva effektiva frågor.

Tänk på hur dessa två frågor skiljer sig åt:

-- 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
);

I det icke-korrelerade exemplet beräknar underfrågan ett enda genomsnittspris för alla produkter. Det här värdet beräknas en gång och sedan jämförs varje produkts pris med det fasta talet.

I det korrelerade exemplet refererar subfrågan till p1.ProductCategoryID från den yttre frågan. Detta skapar ett beroende: för varje produktrad beräknar underfrågan genomsnittspriset för den specifika kategorin. En produkt i kategorin "Cyklar" jämförs med cykelgenomsnittet, medan en produkt i "Tillbehör" jämförs med tillbehörsgenomsnittet.

Anmärkning

Frågeoptimeraren omvandlar ofta korrelerade underfrågor till motsvarande kopplingar internt. Men att förstå det logiska korrelerade beteendet hjälper dig att skriva korrekta frågor, även när den fysiska utförandet skiljer sig åt.

Filtrera med korrelerade underfrågor

Korrelerade underfrågor i WHERE -satsen möjliggör radspecifika filtreringsvillkor som skulle vara omöjliga med statiska jämförelser. I stället för att jämföras med ett enda fast värde utvärderas varje rad mot ett dynamiskt beräknat värde baserat på radens attribut.

Det här mönstret är användbart när du behöver identifiera extremvärden inom grupper, hitta poster som överskrider tröskelvärdet för den egna kategorin eller tillämpa affärsregler som varierar beroende på kontext. I följande exempel hittas produkter som är prissatta över deras kategorigenomsnitt, vilket innebär att ett lågpristillbehör kan flaggas som dyrt medan en cykel med högre priser kanske inte gör det:

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;

Du kan använda samma mönster för att identifiera kunder vars beteende skiljer sig från deras personliga baslinje.

Följande fråga hittar kunder som har gjort minst en beställning som överskrider sitt eget genomsnittliga ordervärde, vilket hjälper dig att identifiera ovanliga inköpsmönster eller transaktioner med högt värde:

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
);

Använda EXISTS med korrelerade underfrågor

Operatorn EXISTS i kombination med en korrelerad underfråga testar om det finns matchande rader i en relaterad tabell och returnerar ett enkelt sant eller falskt resultat. Det här mönstret är mycket effektivt eftersom databasmotorn kan sluta söka så snart den hittar den första matchande raden. Till skillnad från underfrågor som returnerar faktiska data EXISTS behöver bara bekräfta närvaro eller frånvaro.

Använd EXISTS när du behöver svara på frågor som "vilka kunder har gjort beställningar?" eller "vilka produkter har aldrig sålts?" Underfrågan används SELECT 1 vanligtvis eftersom de faktiska värdena inte spelar någon roll:

-- 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 blir ännu mer värdefull när du behöver kontrollera komplexa villkor som kombinerar flera kriterier. Du kan lägga till valfri filtreringslogik i underfrågan, och den yttre frågan innehåller endast rader där minst en matchande relaterad rad finns.

Följande exempel visar hur du hittar produkter med order med hög kvantitet och kategorier där varje produkt uppfyller ett priströskelvärde:

-- 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
);

Tips/Råd

EXISTS överträffar IN vanligtvis med underfrågor, särskilt vid kontroll av förekomsten i stora tabeller. Optimeraren kan stoppas efter att ha hittat den första matchningen med EXISTS, men IN kan behöva hämta alla matchande värden.

Beräkna värden med korrelerade underfrågor i SELECT

Korrelerade underfrågor i SELECT-satsen beräknar ett separat värde för varje rad i din resultatuppsättning. Med det här mönstret kan du inkludera aggregerade eller härledda värden från relaterade tabeller tillsammans med huvudradens information, utan att komprimera resultatet till grupper.

Den här metoden är användbar när du behöver visa sammanhangsberoende information, till exempel att visa varje produkt tillsammans med kategorins genomsnittliga pris, eller varje anställd tillsammans med sin avdelnings totala personalstyrka. Underfrågan körs en gång per rad med hjälp av radens värden för att filtrera beräkningen:

-- 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;

Du kan också använda det här mönstret för att räkna relaterade poster eller hämta specifika värden från relaterade tabeller. Följande fråga skapar en kundsammanfattning som innehåller varje kunds orderantal och senaste orderdatum, beräknat individuellt för varje kundrad:

-- 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;

Anmärkning

Korrelerade underfrågor i SELECT -satsen måste returnera exakt ett värde. Om underfrågan kan returnera flera rader omsluter du den i en mängdfunktion som MAX(), MIN()eller SUM().

Hitta top-N per grupp med korrelerade underfrågor

En av de mest praktiska tillämpningarna av korrelerade underfrågor är att hitta de översta N objekten i varje grupp. Det här mönstret besvarar frågor som "vilka är de tre dyraste produkterna i varje kategori?" eller "vilka är de fem främsta säljarna i varje region?"

Den korrelerade underfrågan undersöker varje rad och avgör om den hör till den översta N för gruppen genom att kontrollera hur många andra rader i samma grupp som rangordnas högre. Den här metoden fungerar bra när fönsterfunktioner inte är tillgängliga eller när du behöver komplex rangordningslogik som fönsterfunktioner inte kan uttrycka.

Följande fråga hittar de tre dyraste produkterna per kategori genom att välja produkter vars ID visas i topp 3 för deras kategori:

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;

En alternativ metod räknar hur många objekt som rankas högre än den aktuella raden. Om färre än N-objekt har ett högre värde är den aktuella raden högst upp N. Den här tekniken hanterar banden på olika sätt och kan vara användbar när du behöver alla objekt som knyts för Nth-positionen:

-- 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;

Jämför rader i följd

Korrelerade underfrågor kan komma åt värden från tidigare eller efterföljande rader baserat på ordningskriterier, vilket möjliggör jämförelser mellan perioder och trendanalyser. Det här mönstret är användbart för att beräkna ändringar mellan på varandra följande poster, till exempel genom att jämföra varje order med den tidigare ordningen eller spåra hur värden utvecklas över tid.

Underfrågan hittar en relaterad rad genom att filtrera efter rader som kommer före (eller efter) den aktuella raden i den logiska sekvensen och beordrar sedan resultatet att hämta den omedelbart intilliggande raden:

-- 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;

Tips/Råd

För efterföljande radjämförelser är fönsterfunktioner som LAG() och LEAD() vanligtvis mer effektiva och läsbara än korrelerade underfrågor. Använd korrelerade underfrågor när du behöver mer komplexa villkor än stöd för fönsterfunktioner.

Välj mellan korrelerade underfrågor och alternativ

Korrelerade underfrågor är inte alltid den bästa metoden. Följande tabell hjälper dig att välja rätt teknik:

Använd den här metoden	När du behöver...
Korrelerade underfrågor	Jämför varje rad med ett dynamiskt beräknat värde baserat på den radens attribut, testa för existens med EXISTS/NOT EXISTSeller hämta exakt ett relaterat värde per rad med komplex markeringslogik.
Joins	Hämta kolumner från flera tabeller eller när relationer är enkla utan beräkningar per rad.
Fönsterfunktioner	Beräkna löpande summor, rangordningar eller åtkomst till föregående/nästa rader med LAG()/LEAD(). Effektivare än korrelerade underfrågor för dessa mönster.
CTE:er	Referera till samma beräknade resultat flera gånger eller dela upp komplex logik i namngivna, läsbara steg.

Prestandaöverväganden

Korrelerade underfrågor kan påverka prestanda när de inte optimeras korrekt. Eftersom underfrågan körs en gång för varje rad i den yttre frågan kan dåligt utformade korrelerade frågor resultera i tusentals eller miljontals underfrågor i stora tabeller.

Följ dessa riktlinjer för att optimera korrelerade underfrågors prestanda:

• Skapa index för korrelationskolumner: Se till att de kolumner som refereras i underfrågans sats WHERE som länkar tillbaka till den yttre frågan indexeras. Om din underfråga till exempel filtrerar på ProductCategoryIDkan ett index i kolumnen snabbt hitta matchande rader i stället för att söka igenom hela tabellen efter varje yttre rad.

• Inkludera ytterligare kolumner i index: Om din underfråga även filtrerar eller aggregerar på andra kolumner bör du överväga ett sammansatt index. Ett index på (ProductCategoryID, ListPrice) stöder både korrelationssökning och prisbaserad filtrering eller aggregering i en enda indexsökning.

• Utvärdera alternativa metoder: Många korrelerade underfrågor kan skrivas om som kopplingar eller fönsterfunktioner med bättre prestanda. Om du hittar det maximala värdet per grupp överträffar en fönsterfunktion med ROW_NUMBER() ofta en korrelerad underfråga som väljer MAX() för varje rad.

• Granska körningsplaner: Använd SET STATISTICS IO ON och granska den verkliga körningsplanen för att förstå hur optimeraren bearbetar din korrelerade underfråga. Optimeraren kan omvandla den till en koppling internt, eller så kan den köras rad för rad som skrivet.

• Testa med realistiska datavolymer: Korrelerade underfrågor som fungerar bra på små testdatauppsättningar kan bli långsamma med tabeller i produktionsstorlek. Testa alltid med representativa data innan du lanserar till produktion.

Viktigt!

Granska alltid exekveringsplaner när du arbetar med korrelerade underfrågor på stora tabeller. Optimeraren kan transformera dem effektivt, men komplexa korrelationer kan dra nytta av frågeomskrivningar.

Mer information om underfrågor finns i Underfrågor (Transact-SQL) och EXISTS (Transact-SQL).