Detta är det andra i en serie artiklar om underfrågor. I den här artikeln diskuterar vi underfrågor i SELECT-uttalandets kolumnlista. Andra artiklar diskuterar deras användning i andra klausuler.
Alla exempel för den här lektionen är baserade på Microsoft SQL Server Management Studio och AdventureWorks2012-databasen. Du kan komma igång med dessa gratisverktyg med hjälp av min guide Komma igång med SQL Server.
Använda underfrågor i Select Statement
När en underfråga placeras i kolumnlistan används den till returnera enstaka värden. I det här fallet kan du tänka på underfrågan som ett enda värdeuttryck. Det returnerade resultatet skiljer sig inte från uttrycket ”2 + 2.” Naturligtvis kan underfrågor också returnera text, men du förstår poängen!
När du arbetar med underfrågor kallas huvuduttrycket ibland den yttre frågan. Underfrågorna är inom parentes, detta gör det lättare att upptäcka dem .
Var försiktig när du använder underfrågor. De kan vara roliga att använda, men när du lägger till mer i din fråga kan de börja sakta ner din fråga.
Enkel underfråga för att beräkna genomsnitt
Låt oss börja med en enkel fråga för att visa SalesOrderDetail och jämföra det med det totala genomsnittliga SalesOrderDetail LineTotal. SELECT-uttalandet vi använder är:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Denna fråga returnerar resultat som:
Underfrågan, som visas i rött ovan, körs först för att få den genomsnittliga LineTotal.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Detta resultat kopplas sedan tillbaka till kolumnlistan och frågan fortsätter. Det finns flera saker jag vill påpeka :
- Underfrågor finns inom parentes .
- När underfrågor används i en SELECT-sats kan de bara returnera ett värde. Detta bör vara meningsfullt, helt enkelt att välja en kolumn returnerar ett värde för en rad, och vi måste följa samma mönster.
- I allmänhet körs underfrågan bara en gång för hela frågan och resultatet återanvänds . Detta beror på att frågeresultatet inte varierar för varje rad som returneras.
- Det är viktigt att använda alias för kolumnnamnen för att förbättra läsbarheten.
Enkel underfråga i uttryck
Som du förväntar dig kan resultatet för en underfråga användas i andra uttryck. Med hjälp av föregående exempel, låt oss använda underfrågan för att avgöra hur mycket vår LineTotal varierar från genomsnittet.
Variansen är helt enkelt LineTotal minus den genomsnittliga linjen totalt. I följande underfråga har jag färgat det blått. Här är formeln för variansen:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
SELECT-satsen i parentes är underfrågan. Liksom det tidigare exemplet kommer denna fråga att köras en gång, returnera ett numeriskt värde som sedan subtraheras från varje LineTotal-värde.
Här är frågan i slutlig form:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Här är resultatet:
När jag arbetar med underfrågor i utvalda uttalanden bygger jag vanligtvis och testa underfrågan först. VÄLJ uttalanden kan bli komplicerade mycket snabbt. Det är bäst att bygga upp dem lite efter lite. Genom att bygga och testa de olika delarna separat hjälper det verkligen med felsökning.
Korrelerade frågor
Det finns sätt att införliva de yttre frågens värden i underfrågan. Dessa typer av frågor kallas korrelerade underfrågor, eftersom resultaten från underfrågan är kopplade, i någon form, till värden i den yttre frågan. Korrelerade frågor kallas ibland synkroniserade frågor.
Om du har problem med att veta vad korrelat betyder, kolla in den här definitionen från Google:
Korrelera: ”har en ömsesidig relation eller anslutning, där en sak påverkar eller beror på en annan. ”
En typisk användning för en korrelerad underfråga används en av de yttre frågens kolumner i den inre frågens WHERE-sats. Detta är sunt förnuft i många fall du vill begränsa den inre frågan till en delmängd data.
Exempel på korrelerad undersökning
Vi ger ett korrelerat exempel på undersökningar genom att rapportera varje SalesOrderDetail LineTotal och de genomsnittliga LineTotal för den totala försäljningen Order.
Denna begäran skiljer sig avsevärt från våra tidigare exempel eftersom genomsnittet vi beräknar varierar för varje försäljningsorder.
Det är här korrelerade underfrågor spelar in. Vi kan använda en värde från den yttre frågan och införliva det i underfrågans filterkriterier.
Låt oss ta en titta på hur vi beräknar den genomsnittliga linjetotalen. För att göra detta har jag sammanställt en illustration som visar SELECT-uttalandet med underfråga.
För att ytterligare utarbeta diagram. SELECT-satsen består av två delar, den yttre frågan och underfrågan. Den yttre frågan används för att hämta alla SalesOrderDetail-rader.Underfrågan används för att hitta och sammanfatta detaljrader för försäljningsorder för ett specifikt SalesOrderID.
Om jag skulle ordföra stegen vi ska ta, jag skulle sammanfatta dem som:
- Skaffa SalesOrderID.
- Returnera den genomsnittliga linjetotalen från alla SalesOrderDetail-artiklar där SalesOrderID matchar.
- Fortsätt till nästa SalesOrderID i den yttre frågan och upprepa steg 1 och 2.
Frågan du kan köra i AdventureWork2012-databasen är:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Här är resultaten av frågan:
Det finns ett par objekt att påpeka.
- Du kan se att jag använde kolumnalias för att göra sökresultaten enklare att läsa.
- Jag använde också ett tabellalias, SOD, för yttre fråga. Detta gör det möjligt att använda de yttre frågens värden i underfrågan. Annars är frågan inte korrelerad!
- Med hjälp av tabellaliasen blir det otvetydigt vilka kolumner som är från varje tabell.
Att bryta ner den korrelerade underfrågan
Låt oss nu försöka bryta ner detta med SQL.
Till att börja med antar vi att vi bara kommer att få vårt exempel på SalesOrderDetailID 20. Motsvarande SalesOrderID är 43661.
Att få den genomsnittliga linjetotalen för det här objektet är enkelt
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Detta returnerar värdet 2181.765240.
Nu när vi har det genomsnitt vi kan koppla in den i vår fråga
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Med hjälp av underfrågor blir detta
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Slutfrågan är :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Korrelerad underfråga med en annan tabell
En korrelerad underfråga, eller för den delen, vilken underfråga som helst, kan använda en annan tabell än den yttre frågan. Detta kan vara till nytta när du arbetar med en ”överordnad” -tabell, till exempel SalesOrderHeader, och om du vill inkludera en sammanfattning av underordnade rader, till exempel de från SalesOrderDetail.
Låt oss returnera OrderDate, TotalDue och antal detaljhandelsrader. För att göra detta kan vi använda följande diagram för att få vårt lager:
För att göra detta kommer vi att inkludera en korrelerad underfråga i vårt SELECT-uttalande för att returnera COUNT av SalesOrderDetail-rader. Vi ser till att vi räknar rätt SalesOrderDetail-artikel genom att filtrera på den yttre frågens SalesOrderID.
Här är det slutliga SELECT-uttalandet:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Resultaten är:
Några saker att notera med detta exempel är:
- Underfrågan väljer data från en annan tabell än den yttre frågan.
- Jag använde tabell och kolumnalias för att göra det lättare att läsa SQL och resultat.
- Var noga med att dubbelkontrollera k din var klausul! Om du glömmer att inkludera tabellnamnet eller alias i underfrågan WHERE-satsen, kommer frågan inte att korreleras.
Korrelerade underfrågor kontra inre sammanfogningar
Det är viktigt för att förstå att du kan få samma resultat med antingen en underfråga eller gå med. Även om båda ger samma resultat finns det fördelar och nackdelar med varje metod!
Tänk på det sista exemplet där vi räknar rader för SalesHeader-artiklar.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Samma fråga kan göras med en INNER JOIN tillsammans med GROUP BY som
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
Vilken är snabbare?
Du kommer att upptäcka att många säger att undvika underfrågor eftersom de är långsammare. De kommer att argumentera för att den korrelerade underfrågan måste ”exekvera” en gång för varje rad som returneras i den yttre frågan, medan INNER JOIN bara behöver göra en genomgång av data.
Själv? Jag säger kolla in frågeplanen. Jag följde mitt eget råd för båda exemplen ovan och fann att planerna var desamma!
Det betyder inte att planerna skulle förändras om det fanns mer data, men min poäng är att du inte bara borde göra antaganden. De flesta SQL DBMS-optimerare är riktigt bra på att räkna ut det bästa sättet att utföra din fråga. De tar dina syntaxer, till exempel en underfråga, eller INNER JOIN, och använder dem för att skapa en faktisk genomförandeplan.
Vilken är lättare att läsa?
Beroende på vad du är bekväm med kan du hitta INNER JOIN-exemplet lättare att läsa än den korrelerade frågan. i det här exemplet gillar jag den korrelerade underfrågan eftersom den verkar mer direkt. Det är lättare för mig att se vad som räknas.
I mitt sinne är INNER JOIN mindre direkt. Först måste du se att alla försäljningsdetaljerader returneras och sedan sammanfattas. Det förstår du inte förrän du har läst hela uttalandet.
Vilken är bättre?
Låt mig veta vad du tycker. Jag vill höra om du föredrar att använda den korrelerade underfrågan eller exemplet INNER JOIN.