Dette er det andre i en serie artikler om delspørsmål. I denne artikkelen diskuterer vi underspørsmål i SELECT-setningens kolonneliste. Andre artikler diskuterer bruken av dem i andre ledd.
Alle eksemplene for denne leksjonen er basert på Microsoft SQL Server Management Studio og AdventureWorks2012-databasen. Du kan komme i gang med disse gratisverktøyene ved å bruke guiden min Komme i gang ved hjelp av SQL Server.
Bruke underspørsmål i Select Statement
Når et underspørsmål er plassert i kolonnelisten, brukes det til å returnere enkeltverdier. I dette tilfellet kan du tenke på undersøket som et enkelt verdiuttrykk. Resultatet som returneres er ikke annerledes enn uttrykket «2 + 2.» Selvfølgelig kan underspøringer også returnere tekst, men du skjønner poenget!
Når du arbeider med underspørringer, kalles hovedutsagnet noen ganger det ytre spørringen. Underspørringer er lukket i parentes, dette gjør dem lettere å få øye på .
Vær forsiktig når du bruker undersøk. De kan være morsomme å bruke, men når du legger til mer i spørringen, kan de begynne å bremse søket.
Enkelt undersøk for å beregne gjennomsnitt
La oss starte med et enkelt spørsmål for å vise SalesOrderDetail og sammenligne det med det totale gjennomsnittet av SalesOrderDetail LineTotal. SELECT-setningen vi vil bruke er:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Denne forespørselen returnerer resultater som:
Delspørringen, som vises i rødt over, kjøres først for å oppnå gjennomsnittlig LineTotal.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Dette resultatet settes deretter tilbake i kolonnelisten, og spørringen fortsetter. Det er flere ting jeg vil påpeke :
- Underforespørsler er lagt inn i parentes .
- Når underforespørsler brukes i en SELECT-setning, kan de bare returnere en verdi. Dette skal være fornuftig, bare ved å velge en kolonne returneres en verdi for en rad, og vi må følge det samme mønsteret.
- Generelt kjøres undersøket bare én gang for hele spørringen, og resultatet blir gjenbrukt . Dette er fordi søkeresultatet ikke varierer for hver rad som returneres.
- Det er viktig å bruke aliaser for kolonnenavnene for å forbedre lesbarheten.
Enkelt underspørsmål i uttrykk
Som du kan forvente, kan resultatet for en underspørsel brukes i andre uttrykk. Basert på det forrige eksemplet, la oss bruke undersøket til å bestemme hvor mye vår LineTotal varierer fra gjennomsnittet.
Variansen er ganske enkelt LineTotal minus den gjennomsnittlige linjetotalen. I det følgende spørringen har jeg farget den blå. Her er formelen for variansen:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
SELECT-setningen som er innestengt i parentes er underspørringen. Som det tidligere eksemplet, vil dette spørsmålet kjøre en gang, returnere en numerisk verdi, som deretter trekkes fra hver LineTotal-verdi.
Her er spørringen i endelig 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 arbeider med delspørsmål i utvalgte utsagn, bygger jeg vanligvis og test undersøket først. SELECT-utsagn kan bli komplisert veldig raskt. Det er best å bygge dem opp litt etter litt. Ved å bygge og teste de forskjellige delene hver for seg, hjelper det virkelig med feilsøking.
Korrelerte spørringer
Det er måter å innlemme verdiene til det ytre spørringen i underspørringens ledd. Disse typene spørringer kalles korrelerte underspørsler, siden resultatene fra undersøket i noen form er koblet til verdier i det ytre spørringen. Korrelerte spørsmål kalles noen ganger synkroniserte søk.
Hvis du har problemer med å vite hva korrelat betyr, kan du sjekke ut denne definisjonen fra Google:
Korrelere: «ha et gjensidig forhold eller forbindelse, der en ting påvirker eller avhenger av en annen. ”
En typisk bruk for et korrelert underspørsmål brukes en av de ytre spørringens kolonner i den indre spørringens WHERE-ledd. Dette er sunn fornuft i mange tilfeller du vil begrense det indre spørringen til et delsett av data.
Eksempel på korrelert undersøkelse
Vi gir et eksempel på et korrelert undersøk ved å rapportere tilbake hver SalesOrderDetail LineTotal, og gjennomsnittlig LineTotal for det totale salg Bestilling.
Denne forespørselen skiller seg betydelig fra våre tidligere eksempler, siden gjennomsnittet vi beregner varierer for hver salgsordre.
Dette er hvor korrelerte underspørsmål spiller inn. Vi kan bruke en verdi fra den ytre spørringen og innlemme den i filterkriteriene til underspørringen.
La oss ta en se på hvordan vi beregner den gjennomsnittlige linjetotalen. For å gjøre dette har jeg satt sammen en illustrasjon som viser SELECT-setningen med underspørring.
For å videre utdype diagram. SELECT-setningen består av to deler, den ytre spørringen og underspørringen. Den ytre spørringen brukes til å hente alle SalesOrderDetail-linjer.Delspørringen brukes til å finne og oppsummere detaljordelinjer for salgsordre for en bestemt SalesOrderID.
Hvis jeg skulle verbalisere trinnene vi skal ta, vil jeg oppsummere dem som:
- Få SalesOrderID.
- Returner den gjennomsnittlige linjetotalen fra alle SalesOrderDetail-varer der SalesOrderID samsvarer.
- Fortsett til neste SalesOrderID i den ytre spørringen og gjenta trinn 1 og 2.
Søket du kan kjø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 resultatene av spørringen:
Det er et par elementer å påpeke.
- Du kan se at jeg brukte kolonnealiaser for å gjøre søkeresultatene lettere å lese.
- Jeg brukte også et tabellalias, SOD, for ytre spørring. Dette gjør det mulig å bruke verdiene i det ytre spørringen i undersøket. Ellers er ikke spørringen korrelert!
- Ved å bruke tabellaliasene blir det entydig hvilke kolonner som er fra hver tabell.
Å bryte ned det korrelerte spørringen
La oss nå prøve å bryte ned dette ved hjelp av SQL.
For å starte, la oss anta at vi bare får eksemplet vårt for SalesOrderDetailID 20. Den tilsvarende SalesOrderID er 43661.
Det er enkelt å få gjennomsnittlig linjetotal for denne varen
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Dette returnerer verdien 2181.765240.
Nå som vi har gjennomsnittet vi kan koble den til spørringen vår
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Ved å bruke underspørringer blir dette
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Det endelige spørsmålet er :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Korrelert underforespørsel med en annen tabell
Et korrelert underforespørsel, eller for den saks skyld ethvert underspørsmål, kan bruke en annen tabell enn den ytre spørringen. Dette kan være nyttig når du jobber med en «overordnet» tabell, for eksempel SalesOrderHeader, og du vil inkludere et sammendrag av underordnede rader som for eksempel fra SalesOrderDetail.
La oss returnere OrderDate, TotalDue og antall detaljordelinjer for salgsordre. For å gjøre dette kan vi bruke følgende diagram for å få peiling:
For å gjøre dette inkluderer vi et korrelert underspørsmål i vår SELECT-setning for å returnere ANTALL SalesOrderDetail-linjer. Vi vil sikre at vi teller riktig SalesOrderDetail-element ved å filtrere på SalesOrderID på den ytre spørringen.
Her er den endelige SELECT-setningen:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Resultatene er:
Noen ting å merke seg med dette eksemplet er:
- Delspørringen velger data fra en annen tabell enn den ytre spørringen.
- Jeg brukte tabell og kolonnealiaser for å gjøre det lettere å lese SQL og resultatene.
- Sørg for å dobbeltsjekke k hvor klausul! Hvis du glemmer å ta med tabellnavnet eller aliasene i underspørringen WHERE-setningen, blir ikke spørringen korrelert.
Korrelerte underspørringer kontra indre sammenføyninger
Det er viktig for å forstå at du kan få de samme resultatene ved hjelp av enten et underspørsmål eller delta. Selv om begge gir de samme resultatene, er det fordeler og ulemper med hver metode!
Tenk på det siste eksemplet der vi teller ordrelinjer for SalesHeader-varer.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Det samme spørsmålet kan gjøres ved hjelp av 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 raskere?
Du vil oppdage at mange mennesker vil si for å unngå underspørsler ettersom de er tregere. De vil hevde at det korrelerte undersøket må «utføre» en gang for hver rad som returneres i den ytre spørringen, mens INNER JOIN bare trenger å få en til å gå gjennom dataene.
Selv? Jeg sier sjekk ut spørringsplanen. Jeg fulgte mitt eget råd for begge eksemplene ovenfor og fant planene til å være de samme!
Det er ikke å si at planene ville endres hvis det var mer data, men poenget mitt er at du ikke bare burde gjøre antakelser. De fleste SQL DBMS-optimaliserere er veldig flinke til å finne ut den beste måten å utføre spørringen på. De tar syntaksen din, for eksempel et underspørsmål, eller INNER JOIN, og bruker dem til å lage en faktisk utførelsesplan.
Hvilken er enklere å lese?
Avhengig av hva du er komfortabel med, kan du finne INNER JOIN-eksemplet lettere å lese enn det korrelerte spørsmålet. Personlig, i dette eksemplet liker jeg det korrelerte underspørsmålet ettersom det virker mer direkte. Det er lettere for meg å se hva som telles.
I mitt sinn er INNER JOIN mindre direkte. Først må du se at alle salgsdetaljeradene blir returnert for å bli oppsummert. Du får ikke dette før du har lest hele uttalelsen.
Hvilken er bedre?
Gi meg beskjed om hva du synes. Jeg vil gjerne høre om du foretrekker å bruke det korrelerte undersøket eller INNER JOIN-eksemplet.