Sammenlign rader med korrelerte delforespørsler

Fullført

Korrelerte spørringer er underspørringer som refererer til kolonner fra den ytre spørringen, og skaper en avhengighet som gjør at delspørringen kjører én gang for hver rad som behandles av den ytre spørringen. Selv om dette kan høres ineffektivt ut, er korrelerte spørringer nyttige for rad-for-rad sammenligninger og beregninger som er vanskelige eller umulige å uttrykke på annen måte.

Forstå korrelert utførelse av delspørre.

En korrelert delspørring refererer til én eller flere kolonner fra den ytre spørringen, og skaper en logisk avhengighet mellom de to. I motsetning til en vanlig underspørring som kjører én gang og returnerer et fast resultat, kjører en korrelert delspørring gjentatte ganger, én gang for hver rad den ytre spørringen prosesserer.

Tenk på det som en nestelt løkke: for hver rad i den ytre spørringen evaluerer databasen underspørringen ved å bruke verdiene til den raden. Denne oppførselen muliggjør kraftige rad-for-rad-sammenligninger, men det betyr også at du må forstå utførelsesmodellen for å skrive effektive spørringer.

Tenk på hvordan disse to spørringene skiller seg:

-- 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 ikke-korrelerte eksempelet beregner delspørringen en enkelt gjennomsnittspris på tvers av alle produktene. Denne verdien beregnes én gang, og deretter sammenlignes hvert produkts pris med det faste tallet.

I det korrelerte eksempelet refererer p1.ProductCategoryID delspørringen fra den ytre spørringen. Dette skaper en avhengighet: for hver produktrad beregner delspørringen gjennomsnittsprisen for den spesifikke kategorien. Et produkt i kategorien «Sykler» sammenlignes med sykkelgjennomsnittet, mens et produkt i «Tilbehør» sammenlignes med tilbehørsgjennomsnittet.

Bemerkning

Spørringsoptimalisatoren omdanner ofte korrelerte delforespørsler til ekvivalente joins internt. Men å forstå den logisk korrelerte oppførselen hjelper deg å skrive riktige spørringer, selv når den fysiske utførelsen er annerledes.

Filter med korrelerte underspørringer

Korrelerte underspørringer i klausulen WHERE muliggjør rad-spesifikke filtreringsbetingelser som ville vært umulige med statiske sammenligninger. I stedet for å sammenligne med en enkelt fast verdi, evalueres hver rad mot en dynamisk beregnet verdi basert på den radens attributter.

Dette mønsteret er nyttig når du må identifisere uteliggere innen grupper, finne poster som overstiger terskelen for deres egen kategori, eller anvende forretningsregler som varierer etter kontekst. Følgende eksempel viser produkter priset over kategorigjennomsnittet, noe som betyr at et billig tilbehør kan bli merket som dyrt, mens en høyere sykkel kanskje ikke gjø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 bruke det samme mønsteret for å identifisere kunder hvis atferd avviker fra deres personlige utgangspunkt.

Følgende søk finner kunder som har lagt inn minst én bestilling som overstiger deres egen gjennomsnittlige ordreverdi, noe som hjelper til med å identifisere uvanlige kjøpsmønstre eller verdifulle transaksjoner:

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

Bruk EXISTS med korrelerte underspørringer

Operatoren EXISTS kombinert med en korrelert delspørring tester om det finnes samsvarende rader i en relatert tabell, og returnerer et enkelt sant eller usant resultat. Dette mønsteret er svært effektivt fordi databasemotoren kan slutte å søke så snart den finner den første matchende raden. I motsetning til underspørringer som returnerer faktiske data, EXISTS trenger den bare å bekrefte tilstedeværelse eller fravær.

Bruk EXISTS den når du trenger å svare på spørsmål som «hvilke kunder har lagt inn bestillinger?» eller «hvilke produkter har aldri blitt solgt?» Delspørringen bruker SELECT 1 vanligvis fordi de faktiske verdiene ikke spiller noen rolle:

-- 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 enda mer verdifullt når du må sjekke komplekse forhold som kombinerer flere kriterier. Du kan legge til hvilken som helst filtreringslogikk inne i underspørringen, og den ytre spørringen vil kun inkludere rader der det finnes minst én matchende relatert rad.

Følgende eksempler viser å finne produkter med store bestillinger og kategorier hvor hvert produkt oppfyller en prisgrense:

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

EXISTS Vanligvis overgår IN det med underforespørsler, spesielt når man sjekker om det finnes i store tabeller. Optimalisatoren kan stoppe etter å ha funnet det første treffet med EXISTS, mens IN det kan være nødvendig å hente alle matchende verdier.

Beregn verdier med korrelerte delforespørsler i SELECT

Korrelerte delforespørsler i klausulen SELECT beregner en egen verdi for hver rad i resultatsettet ditt. Dette mønsteret lar deg inkludere aggregerte eller avledede verdier fra relaterte tabeller sammen med detaljene i hovedraden, uten å dele resultatet inn i grupper.

Denne tilnærmingen er nyttig når du trenger å vise kontekstuell informasjon, som å vise hvert produkt sammen med gjennomsnittsprisen i kategorien, eller hver ansatt sammen med avdelingens totale antall ansatte. Underspørringen utføres én gang per rad, og bruker verdiene til den raden for å filtrere beregningen:

-- 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 også bruke dette mønsteret til å telle relaterte poster eller hente spesifikke verdier fra relaterte tabeller. Følgende spørring bygger et kundesammendrag som inkluderer hver kundes ordreantall og siste ordredato, beregnet individuelt for hver kunderad:

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

Bemerkning

Korrelerte delforespørsler i klausulen SELECT må returnere nøyaktig én verdi. Hvis delspørringen kunne returnere flere rader, pakk den inn i en aggregert funksjon som MAX(), MIN(), eller SUM().

Finn topp-N per gruppe med korrelerte underspørringer

En av de mest praktiske anvendelsene av korrelerte delspørringer er å finne de N beste elementene innenfor hver gruppe. Dette mønsteret besvarer spørsmål som «hva er de tre dyreste produktene i hver kategori?» eller «hvem er de fem beste selgerne i hver region?»

Den korrelerte delspørringen undersøker hver rad og avgjør om den hører hjemme i topp N for sin gruppe ved å sjekke hvor mange andre rader i samme gruppe som rangerer høyere. Denne tilnærmingen fungerer godt når vindusfunksjoner ikke er tilgjengelige, eller når du trenger kompleks rangeringslogikk som vindusfunksjoner ikke kan uttrykke.

Følgende søk finner de tre dyreste produktene per kategori ved å velge produkter hvis ID-er vises blant de tre beste i deres 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 tilnærming teller hvor mange elementer som rangerer høyere enn den nåværende raden. Hvis færre enn N elementer har høyere verdi, er den nåværende raden i topp N. Denne teknikken håndterer uavgjort annerledes og kan være nyttig når du trenger alle gjenstander som er likt for den n-te posisjonen:

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

Sammenlign påfølgende rader

Korrelerte delspørringer kan få tilgang til verdier fra tidligere eller påfølgende rader basert på ordenskriterier, noe som muliggjør period-over-periode-sammenligninger og trendanalyse. Dette mønsteret er nyttig for å beregne endringer mellom påfølgende poster, som å sammenligne hver rekkefølge med den forrige eller følge hvordan verdier utvikler seg over tid.

Underspørringen finner en relatert rad ved å filtrere etter rader som kommer før (eller etter) den nåværende raden i den logiske rekken, og ordner deretter resultatene for å få den umiddelbart tilstøtende 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

For sammenhengende rad-sammenligninger er vindusfunksjoner som LAG() og LEAD() vanligvis mer effektive og lesbare enn korrelerte underspørringer. Bruk korrelerte underspørringer når du trenger mer komplekse betingelser enn vindusfunksjoner støtter.

Velg mellom korrelerte underspørringer og alternativer

Korrelerte underforespørsler er ikke alltid den beste tilnærmingen. Tabellen nedenfor hjelper deg å velge riktig teknikk:

Bruk denne tilnærmingen	Når du må...
Korrelerte underspørringer	Sammenlign hver rad med en dynamisk beregnet verdi basert på den radens attributter, test for eksistens med EXISTS/NOT EXISTS, eller hent nøyaktig én relatert verdi per rad med kompleks seleksjonslogikk.
Medlemmer	Hente kolonner fra flere tabeller, eller når relasjoner er enkle uten beregninger per rad.
Vindusfunksjoner	Beregn løpende totaler, rangeringer, eller få tilgang til forrige/neste rader med LAG()/LEAD(). Mer effektivt enn korrelerte underspørringer for disse mønstrene.
CTE-er	Referer til det samme beregnede resultatet flere ganger, eller del opp kompleks logikk i navngitte, lesbare steg.

Ytelseshensyn

Korrelerte underforespørsler kan påvirke ytelsen hvis de ikke optimaliseres riktig. Fordi delspørringen kjører én gang for hver rad i den ytre spørringen, kan dårlig utformede korrelerte spørringer føre til tusenvis eller millioner av underspørringsutførelser på store tabeller.

Følg disse retningslinjene for å optimalisere ytelsen til korrelerte delspørringer:

• Lag indekser på korrelasjonskolonner: Sørg for at kolonnene som refereres til i delspørringens WHERE klausul som lenker tilbake til den ytre spørringen, er indeksert. For eksempel, hvis delspørringen din filtrerer på ProductCategoryID, lar en indeks i den kolonnen databasen raskt finne matchende rader i stedet for å skanne hele tabellen for hver ytre rad.

• Inkluder flere kolonner i indekser: Hvis delspørringen din også filtrerer eller aggregerer på andre kolonner, vurder en sammensatt indeks. En indeks på (ProductCategoryID, ListPrice) støtter både korrelasjonsoppslag og prisbasert filtrering eller aggregering i en enkelt indekssøk.

• Evaluer alternative tilnærminger: Mange korrelerte underspørringer kan omskrives som joins eller vindusfunksjoner med bedre ytelse. Hvis du finner maksimal verdi per gruppe, overgår en vindusfunksjon ofte ROW_NUMBER() en korrelert delspørring som velger MAX() for hver rad.

• Gjennomgå utførelsesplaner: Bruk SET STATISTICS IO ON og undersøk den faktiske utførelsesplanen for å forstå hvordan optimalisatoren behandler din korrelerte delspørring. Optimalisatoren kan konvertere det til en intern join, eller den kan kjøre det rad for rad som skrevet.

• Test med realistiske datavolumer: Korrelerte delforespørsler som fungerer godt på små testdatasett kan bli trege med tabeller i produksjonsstørrelse. Benchmark alltid med representative data før du ruller ut i produksjon.

Viktig!

Gå alltid gjennom gjennomføringsplaner når du jobber med korrelerte underspørringer på store tabeller. Optimalisatoren kan transformere dem effektivt, men komplekse korrelasjoner kan ha nytte av spørringsomskrivinger.

For mer informasjon om underforespørsler, se Underforespørsler (Transact-SQL) ogEXISTS (Transact-SQL).