Dette er det andet i en serie af artikler om underforespørgsler. I denne artikel diskuterer vi underforespørgsler i SELECT-sætningens kolonneliste. Andre artikler diskuterer deres anvendelse i andre klausuler.
Alle eksemplerne til denne lektion er baseret på Microsoft SQL Server Management Studio og AdventureWorks2012-databasen. Du kan komme i gang ved hjælp af disse gratis værktøjer ved hjælp af min guide Kom godt i gang ved hjælp af SQL Server.
Brug af underforespørgsler i den valgte erklæring
Når en underforespørgsel placeres i kolonnelisten, bruges den til returnere enkeltværdier. I dette tilfælde kan du tænke på underforespørgslen som et enkelt værdiudtryk. Det returnerede resultat er ikke anderledes end udtrykket “2 + 2.” Selvfølgelig kan underforespørgsler også returnere tekst, men du forstår pointen!
Når du arbejder med underforespørgsler, kaldes hovedudtalelsen undertiden den ydre forespørgsel. Underforespørgsler er omgivet af parentes, hvilket gør dem lettere at få øje på .
Vær forsigtig, når du bruger underforespørgsler. De kan være sjove at bruge, men når du føjer mere til din forespørgsel, kan de begynde at bremse din forespørgsel.
Enkel underforespørgsel til beregning af gennemsnit
Lad os starte med en simpel forespørgsel for at vise SalesOrderDetail og sammenligne det med det samlede gennemsnit SalesOrderDetail LineTotal. Den SELECT-sætning, vi bruger, er:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Denne forespørgsel returnerer resultater som:
Underforespørgslen, der er vist med rødt ovenfor, køres først for at opnå den gennemsnitlige LineTotal.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Dette resultat sættes derefter tilbage i kolonnelisten, og forespørgslen fortsætter. Der er flere ting, jeg vil påpege :
- Underforespørgsler er omgivet af parenteser .
- Når underforespørgsler bruges i en SELECT-sætning, kan de kun returnere en værdi. Dette skal give mening, simpelthen at vælge en kolonne returnerer en værdi for en række, og vi skal følge det samme mønster.
- Generelt køres underforespørgslen kun én gang for hele forespørgslen, og resultatet genbruges . Dette skyldes, at forespørgselsresultatet ikke varierer for hver række, der returneres.
- Det er vigtigt at bruge aliasser til kolonnenavne for at forbedre læsbarheden.
Enkel underforespørgsel i udtryk
Som du måske forventer, kan resultatet for en underforespørgsel bruges i andre udtryk. Baseret på det foregående eksempel, lad os bruge underforespørgslen til at bestemme, hvor meget vores LineTotal varierer fra gennemsnittet.
Variansen er simpelthen LineTotal minus den gennemsnitlige linjetotal. I den følgende underforespørgsel har jeg farvet den blå. Her er formlen for variansen:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
SELECT-sætningen i parentes er underforespørgslen. Som det tidligere eksempel kører denne forespørgsel en gang, returnerer en numerisk værdi, som derefter trækkes fra hver LineTotal-værdi.
Her er forespørgslen i den endelige form:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Her er resultatet:
Når jeg arbejder med underforespørgsler i udvalgte udsagn, bygger jeg normalt og test underforespørgslen først. SELECT-udsagn kan blive komplicerede meget hurtigt. Det er bedst at opbygge dem lidt efter lidt. Ved at opbygge og teste de forskellige stykker separat hjælper det virkelig med fejlretning.
Korrelerede forespørgsler
Der er måder at inkorporere de ydre forespørgsels værdier i underforespørgslens klausuler. Disse typer forespørgsler kaldes korrelerede underforespørgsler, da resultaterne fra underforespørgslen i en eller anden form er forbundet med værdier i den ydre forespørgsel. Korrelerede forespørgsler kaldes undertiden synkroniserede forespørgsler.
Hvis du har problemer med at vide, hvad korrelat betyder, skal du tjekke denne definition fra Google:
Korrelere: “har et gensidigt forhold eller forbindelse, hvor en ting påvirker eller afhænger af en anden. “
En typisk brug for en korreleret underforespørgsel bruges en af de ydre forespørgsels kolonner i den indre forespørgsels WHERE-klausul. Dette er sund fornuft i mange tilfælde, du vil begræns den indre forespørgsel til et undersæt af data.
Eksempel på korreleret underforespørgsel
Vi giver et korreleret eksempler på underforespørgsel ved at rapportere tilbage hver SalesOrderDetail LineTotal og de gennemsnitlige LineTotal for det samlede salg Ordre.
Denne anmodning adskiller sig markant fra vores tidligere eksempler, da gennemsnittet, vi beregner, varierer for hver salgsordre.
Det er her korrelerede underforespørgsler kommer til spil. Vi kan bruge en værdi fra den ydre forespørgsel og inkorporere den i underforespørgslens filterkriterier.
Lad os tage en se på, hvordan vi beregner den gennemsnitlige linjetotal. For at gøre dette har jeg sammensat en illustration, der viser SELECT-sætningen med underforespørgsel.
For yderligere at uddybe diagram. SELECT-sætningen består af to dele, den ydre forespørgsel og underforespørgslen. Den ydre forespørgsel bruges til at hente alle SalesOrderDetail-linjer.Underforespørgslen bruges til at finde og sammenfatte detaljerede linjer for salgsordre for et specifikt SalesOrderID.
Hvis jeg skulle formulere trinnene vi skal tage, vil jeg sammenfatte dem som:
- Få SalesOrderID.
- Returner den gennemsnitlige linjetotal fra alle SalesOrderDetail-varer, hvor SalesOrderID matcher.
- Fortsæt med den næste SalesOrderID i den ydre forespørgsel og gentag trin 1 og 2.
Forespørgslen, du kan køre i AdventureWork2012-databasen, er:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Her er resultaterne af forespørgslen:
Der er et par elementer at påpege.
- Du kan se, at jeg brugte kolonnealiasser for at gøre forespørgselsresultaterne lettere at læse.
- Jeg brugte også et tabelalias, SOD, til ydre forespørgsel. Dette gør det muligt at bruge den ydre forespørgsels værdier i underforespørgslen. Ellers er forespørgslen ikke korreleret!
- Brug af tabelaliaserne gør det entydigt, hvilke kolonner der er fra hver tabel.
Opdeling af det korrelerede underforespørgsel
Lad os nu prøve at nedbryde dette ved hjælp af SQL.
For at starte, lad os antage, at vi bare får vores eksempel på SalesOrderDetailID 20. Den tilsvarende SalesOrderID er 43661.
At få den gennemsnitlige LineTotal for denne vare er let
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Dette returnerer værdien 2181.765240.
Nu hvor vi har det gennemsnit, vi kan sæt det i vores forespørgsel
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Ved hjælp af underforespørgsler bliver dette
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Den sidste forespørgsel er :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Korreleret underforespørgsel med en anden tabel
En korreleret underforespørgsel, eller for den sags skyld enhver underforespørgsel, kan bruge en anden tabel end den ydre forespørgsel. Dette kan være nyttigt, når du arbejder med en “overordnet” tabel, såsom SalesOrderHeader, og du vil medtage som resultat et resumé af underordnede rækker, såsom dem fra SalesOrderDetail.
Lad os returnere OrderDate, TotalDue og antallet af detaljerede salgsordrer. For at gøre dette kan vi bruge følgende diagram til at få vores pejling:
For at gøre dette inkluderer vi en korreleret underforespørgsel i vores SELECT-sætning for at returnere antallet af SalesOrderDetail-linjer. Vi sikrer, at vi tæller det korrekte SalesOrderDetail-element ved at filtrere på den ydre forespørgsels SalesOrderID.
Her er den endelige SELECT-sætning:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Resultaterne er:
Nogle ting at bemærke med dette eksempel er:
- Underforespørgslen vælger data fra en anden tabel end den ydre forespørgsel.
- Jeg brugte tabel og kolonnealiaser for at gøre det lettere at læse SQL og resultater.
- Sørg for at dobbeltklikke k din hvor klausul! Hvis du glemmer at inkludere tabelnavnet eller aliaserne i underforespørgslen WHERE-klausul, vil forespørgslen ikke blive korreleret.
Korrelerede underforespørgsler versus indre sammenføjninger
Det er vigtigt for at forstå, at du kan få de samme resultater ved hjælp af enten en underforespørgsel eller deltage. Selvom begge returnerer de samme resultater, er der fordele og ulemper ved hver metode!
Overvej det sidste eksempel, hvor vi tæller linjeposter til SalesHeader-varer.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Den samme forespørgsel kan udføres ved hjælp af en INNER JOIN sammen 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
Hvilken er hurtigere?
Du finder ud af, at mange mennesker siger, at de undgår underforespørgsler, da de er langsommere. De argumenterer for, at den korrelerede underforespørgsel skal “udføre” en gang for hver række, der returneres i den ydre forespørgsel, hvorimod INNER JOIN kun behøver at lade en gennemgå dataene.
Selv? Jeg siger tjek ud forespørgselsplanen. Jeg fulgte mit eget råd til begge eksemplerne ovenfor og fandt planerne at være de samme!
Det er ikke at sige, at planerne ville ændre sig, hvis der var flere data, men min pointe er, at du ikke bare skal antage antagelser. De fleste SQL DBMS-optimeringsmaskiner er virkelig gode til at finde ud af den bedste måde at udføre din forespørgsel på. De tager dine syntakser, såsom en underforespørgsel eller INNER JOIN, og bruger dem til at oprette en faktisk udførelsesplan.
Hvilken er det nemmere at læse?
Afhængigt af hvad du er fortrolig med, kan du finde INNER JOIN-eksemplet lettere at læse end den korrelerede forespørgsel. Personligt i dette eksempel kan jeg lide den korrelerede underforespørgsel, da den virker mere direkte. Det er lettere for mig at se, hvad der tælles.
I mit sind er INNER JOIN mindre direkte. Først skal du se, at alle salgsdetaljerækkene returneres og derefter sammenfattes. Du får det ikke rigtig, før du læser hele udsagnet.
Hvilken er bedre?
Fortæl mig, hvad du synes. Jeg vil gerne høre, om du foretrækker at bruge det korrelerede underforespørgsel eller INNER JOIN-eksemplet.