Använda fristående eller korrelerade underfrågor

  • 3 minuter

Tidigare tittade vi på fristående underfrågor; där den inre frågan är oberoende av den yttre frågan, körs en gång och returnerar dess resultat till den yttre frågan. T-SQL stöder också korrelerade underfrågor, där den inre frågan refererar till kolumnen i den yttre frågan och konceptuellt körs en gång per rad.

Arbeta med korrelerade underfrågor

Precis som fristående underfrågor är korrelerade underfrågor SELECT-instruktioner kapslade i en yttre fråga. Korrelerade underfrågor kan också vara skalära eller flervärdesunderfrågor. De används vanligtvis när den inre frågan behöver referera till ett värde i den yttre frågan.

Men till skillnad från fristående underfrågor finns det några särskilda överväganden när du använder korrelerade underfrågor:

  • Korrelerade underfrågor kan inte köras separat från den yttre frågan. Den här begränsningen komplicerar testning och felsökning.
  • Till skillnad från fristående underfrågor, som bearbetas en gång, körs korrelerade underfrågor flera gånger. Logiskt sett körs den yttre frågan först, och för varje rad som returneras bearbetas den inre frågan.

I följande exempel används en korrelerad underfråga för att returnera den senaste ordern för varje kund. Underfrågan refererar till den yttre frågan och refererar till dess CustomerID-värde i where-satsen. För varje rad i den yttre frågan hittar underfrågan det maximala order-ID:t för kunden som refereras till på den raden, och den yttre frågan kontrollerar om raden den tittar på är raden med order-ID:t.

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

Skriva korrelerade underfrågor

Tänk på följande riktlinjer för att skriva korrelerade underfrågor:

  • Skriv den yttre frågan för att acceptera rätt returresultat från den inre frågan. Om den inre frågan är skalär kan du använda likhets- och jämförelseoperatorer, till exempel =, <, >och <>, i WHERE-satsen. Om den inre frågan kan returnera flera värden använder du ett IN-predikat. Planera för att hantera NULL-resultat.
  • Identifiera kolumnen från den yttre frågan som ska refereras till av den korrelerade underfrågan. Deklarera ett alias för tabellen som är källan till kolumnen i den yttre frågan.
  • Identifiera kolumnen från den inre tabellen som ska jämföras med kolumnen från den yttre tabellen. Skapa ett alias för källtabellen som du gjorde för den yttre frågan.
  • Skriv den inre frågan för att hämta värden från källan, baserat på indatavärdet från den yttre frågan. Använd till exempel den yttre kolumnen i WHERE-satsen för den inre frågan.

Korrelationen mellan de inre och yttre frågorna inträffar när det yttre värdet refereras av den inre frågan för jämförelse. Det är den här korrelationen som ger underfrågan dess namn.

Arbeta med EXISTS

Förutom att hämta värden från en underfråga tillhandahåller T-SQL en mekanism för att kontrollera om några resultat skulle returneras från en fråga. EXIST-predikatet avgör om det finns några rader som uppfyller ett angivet villkor, men i stället för att returnera dem returneras TRUE eller FALSE. Den här tekniken är användbar för validering av data utan att medföra kostnader för att hämta och bearbeta resultaten.

När en underfråga är relaterad till den yttre frågan med hjälp av EXISTS-predikatet hanterar SQL Server resultatet av underfrågan på ett speciellt sätt. I stället för att hämta ett skalärt värde eller en lista med flera värden från underfrågan kontrollerar EXISTS helt enkelt om det finns några rader i resultatet.

Konceptuellt motsvarar ett EXISTS-predikat att hämta resultaten, räkna de rader som returneras och jämföra antalet med noll. Jämför följande frågor, som returnerar information om kunder som har gjort beställningar:

Den första exempelfrågan använder COUNT i en underfråga:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

Den andra frågan, som returnerar samma resultat, använder EXISTS:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

I det första exemplet måste underfrågan räkna varje förekomst av varje custid som finns i tabellen Sales.SalesOrderHeader och jämföra antalet resultat med noll, bara för att indikera att kunden har gjort beställningar.

I den andra frågan returnerar EXISTS TRUE för en custid så snart en relevant order har hittats i tabellen Sales.SalesOrderHeader . En fullständig redovisning av varje förekomst är onödig. Observera också att med formuläret EXISTS är underfrågan inte begränsad till att returnera en enda kolumn. Här har vi SELECT *. De returnerade kolumnerna är irrelevanta eftersom vi bara kontrollerar om några rader returneras alls, inte vilka värden som finns i dessa rader.

När det gäller logisk bearbetning är de två frågeformulären likvärdiga. Ur ett prestandaperspektiv kan databasmotorn behandla frågorna på olika sätt eftersom den optimerar dem för körning. Överväg att testa var och en för din egen användning.

Kommentar

Om du konverterar en underfråga med COUNT(*) till en med HJÄLP av EXISTS kontrollerar du att underfrågan använder select * och inte SELECT COUNT(*). SELECT COUNT(*) returnerar alltid en rad, så EXISTS returnerar alltid TRUE.

Ett annat användbart program för EXISTS är att negera underfrågan med NOT, som i följande exempel, som returnerar alla kunder som aldrig har gjort en beställning:

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);

SQL Server behöver inte returnera data om relaterade beställningar för kunder som har gjort beställningar. Om en custid hittas i tabellen Sales.SalesOrderHeader utvärderas NOT EXISTS till FALSE och utvärderingen slutförs snabbt.

Om du vill skriva frågor som använder EXISTS med underfrågor bör du överväga följande riktlinjer:

  • Nyckelordet EXISTS följer WHERE direkt. Inget kolumnnamn (eller annat uttryck) föregår det, såvida inte NOT också används.
  • I underfrågan använder du SELECT *. Inga rader returneras av underfrågan, så inga kolumner behöver anges.