Aceasta este a doua dintr-o serie de articole despre subconsultări. În acest articol, discutăm subinterogări în lista de coloane a instrucțiunii SELECT. Alte articole discută despre utilizările lor în alte clauze.
Toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și baza de date AdventureWorks2012. Puteți începe să utilizați aceste instrumente gratuite folosind Ghidul meu Noțiuni introductive Utilizarea SQL Server.
Utilizarea subconsultărilor în instrucțiunea Select
Când o subinterogare este plasată în lista de coloane, este folosită pentru returnează valori unice. În acest caz, vă puteți gândi la subinterogare ca la o expresie de valoare unică. Rezultatul returnat nu diferă de expresia „2 + 2.” Bineînțeles că și subinterogările pot returna text, dar ai de gând!
Când lucrezi cu subinterogări, declarația principală se numește uneori interogare externă. Subinterogările sunt închise între paranteze, ceea ce le face mai ușor de localizat. .
Aveți grijă când utilizați subinterogări. Acestea pot fi distractive, dar pe măsură ce adăugați mai multe la interogare, acestea pot începe să vă încetinească interogarea.
Subinterogare simplă pentru a calcula media
Să începem cu o interogare simplă pentru a afișa SalesOrderDetail și să o comparăm cu media generală SalesOrderDetail LineTotal. Instrucțiunea SELECT pe care o vom folosi este:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Această interogare returnează rezultatele ca:
Subinterogarea, care este afișată cu roșu mai sus, este executată mai întâi pentru a obține valoarea totală linie.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Acest rezultat este apoi conectat înapoi la lista de coloane și interogarea continuă. Există câteva lucruri pe care vreau să le subliniez :
- Subcercetările sunt închise între paranteze .
- Atunci când subinterogările sunt utilizate într-o instrucțiune SELECT, acestea pot returna o singură valoare. Acest lucru ar trebui să aibă sens, simpla selectare a unei coloane returnează o valoare pentru un rând și trebuie să urmăm același model.
- În general, interogarea este executată o singură dată pentru întreaga interogare, iar rezultatul acesteia este reutilizat. . Acest lucru se datorează faptului că rezultatul interogării nu variază pentru fiecare rând returnat.
- Este important să utilizați aliasuri pentru numele coloanelor pentru a îmbunătăți lizibilitatea.
Subinterogare simplă în expresie
După cum vă puteți aștepta, rezultatul pentru o interogare poate fi folosit în alte expresii. Bazându-ne pe exemplul anterior, să folosim subconsultarea pentru a determina cât de mult variază LineTotal nostru față de medie.
Varianța este pur și simplu LineTotal minus totalul Line mediu. În următoarea interogare, am colorat-o în albastru. Iată formula pentru varianță:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
Instrucțiunea SELECT inclusă în paranteză este subinterogarea. La fel ca exemplul anterior, această interogare va rula o singură dată, returnând o valoare numerică, care este apoi scăzută din fiecare valoare LineTotal.
Iată interogarea în forma finală:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Iată rezultatul:
Când lucrez cu subinterogări în instrucțiunile selectate, de obicei le construiesc și testați mai întâi subinterogarea. Instrucțiunile SELECT se pot complica foarte repede. Cel mai bine este să le construiești încetul cu încetul. Construind și testând diferitele piese separat, ajută într-adevăr la depanare.
Interogări corelate
Există modalități de a încorpora valorile interogării externe în clauzele subinterogării. Aceste tipuri de interogări se numesc subinterogări corelate, deoarece rezultatele din interogare sunt conectate, într-o anumită formă, la valorile din interogarea externă. Interogările corelate sunt uneori denumite interogări sincronizate.
Dacă întâmpinați probleme în a ști ce înseamnă corelarea, consultați această definiție de la Google:
Corelați: „aveți o relație sau o conexiune reciprocă, în care un lucru afectează sau depinde de altul. ”
O utilizare tipică pentru o subinterogare corelată este utilizată una dintre coloanele interogării externe din clauza WHERE a interogării interioare. Acest lucru este de bun simț în multe cazuri pe care doriți să restricționează interogarea interioară la un subset de date.
Exemplu de interogare corelată
Vom furniza un exemplu de subinterogare corelat raportând înapoi fiecare SalesOrderDetail LineTotal și Media LineTotal pentru vânzările generale Comandă.
Această cerere diferă semnificativ de exemplele noastre anterioare, deoarece media pe care o calculăm variază pentru fiecare comandă de vânzare.
Aici intră în joc subconsultările corelate. Putem folosi o valoare din interogarea externă și încorporează-o în criteriile de filtrare ale interogării.
Să luăm o uitați-vă la modul în care calculăm media liniei totale. Pentru a face acest lucru, am realizat o ilustrație care arată instrucțiunea SELECT cu subinterogare.
Pentru a detalia în continuare diagramă. Instrucțiunea SELECT este formată din două porțiuni, interogarea externă și subinterogarea. Interogarea exterioară este utilizată pentru a prelua toate liniile SalesOrderDetail.Subinterogarea este utilizată pentru a găsi și a rezuma liniile de detalii ale comenzilor de vânzări pentru un anumit ID SalesOrder.
Dacă ar fi să verbalizez pașii vom lua, le-aș rezuma astfel:
- Obțineți SalesOrderID.
- Returnați totalul liniei medii din toate articolele SalesOrderDetail unde se potrivește SalesOrderID.
- Continuați cu următorul ID SalesOrder în interogarea externă și repetați pașii 1 și 2.
Interogarea pe care o puteți executa în baza de date AdventureWork2012 este:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Iată rezultatele interogării:
Există câteva elemente de subliniat.
- Puteți vedea că am folosit aliasuri de coloană pentru a facilita citirea rezultatelor interogării.
- De asemenea, am folosit un alias de tabel, SOD, pentru interogare exterioară. Acest lucru face posibilă utilizarea valorilor interogării externe în subinterogare. În caz contrar, interogarea nu este corelată!
- Folosind pseudonimele de tabel faceți fără echivoc ce coloane sunt din fiecare tabel.
Descompunerea subinterogării corelate
Să încercăm acum să descompunem acest lucru folosind SQL.
Pentru a începe, să presupunem că vom obține doar exemplul nostru pentru SalesOrderDetailID 20. Codul SalesOrderID corespunzător este 43661.
Pentru a obține Media totală pentru acest element este ușor
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Aceasta returnează valoarea 2181.765240.
Acum că avem media putem conectați-l la interogarea noastră
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Folosind subinterogări, acesta devine
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Interogarea finală este :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Subinterogare corelată cu un tabel diferit
O subinterogare corelată sau, de altfel, orice subinterogare, poate utiliza un tabel diferit de interogarea exterioară. Acest lucru poate fi util atunci când lucrați cu un tabel „părinte”, cum ar fi SalesOrderHeader, și doriți să includeți în rezultat un rezumat al rândurilor secundare, cum ar fi cele din SalesOrderDetail.
Să returnăm OrderDate, TotalDue și numărul de linii de detalii ale comenzilor de vânzare. Pentru a face acest lucru, putem folosi următoarea diagramă pentru a obține rulmenții noștri:
Pentru a face acest lucru, vom include o subinterogare corelată în instrucțiunea noastră SELECT pentru a returna COUNT de linii SalesOrderDetail. Ne vom asigura că numărăm elementul SalesOrderDetail corect prin filtrarea pe ID-ul SalesOrderDer. Iată declarația finală SELECT:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Rezultatele sunt:
Unele lucruri de observat cu acest exemplu sunt:
- Subinterogarea selectează date dintr-un alt tabel decât interogarea externă.
- Am folosit tabel și aliasuri de coloană pentru a ușura citirea SQL și a rezultatelor.
- Asigurați-vă că faceți dublu-chec k clauza dvs. unde! Dacă uitați să includeți numele tabelului sau pseudonimele în subinterogarea clauzei WHERE, interogarea nu va fi corelată.
Subcercări corelate versus îmbinări interioare
Este important pentru a înțelege că puteți obține aceleași rezultate utilizând fie o interogare, fie unire. Deși ambele returnează aceleași rezultate, există avantaje și dezavantaje pentru fiecare metodă!
Luați în considerare ultimul exemplu în care numărăm elementele rând pentru articolele SalesHeader.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Aceeași interogare poate fi făcută utilizând un INNER JOIN împreună cu GROUP BY ca
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
Care este mai rapid?
Veți descoperi că mulți oameni vor spune să evite subconsultările, deoarece acestea sunt mai lente. Vor susține că subinterogarea corelată trebuie să „se execute” o dată pentru fiecare rând returnat în interogarea externă, în timp ce INNER JOIN trebuie să facă doar o trecere prin date.
Eu însumi? planul de interogare. Am urmat propriul meu sfat pentru ambele exemple de mai sus și am constatat că planurile sunt aceleași!
Asta nu înseamnă că planurile se vor schimba dacă ar exista mai multe date, dar punctul meu de vedere este că nu ar trebui să faceți doar presupuneri. Majoritatea optimizatorilor DBMS SQL sunt foarte buni în a afla cel mai bun mod de a executa interogarea dvs. Vă vor lua sintaxele, cum ar fi o interogare sau INNER JOIN, și le vor folosi pentru a crea un planul de execuție efectiv.
Care dintre ele este mai ușor de citit?
În funcție de ceea ce vă simțiți bine, puteți găsi exemplul INNER JOIN mai ușor de citit decât interogarea corelată. Personal, în acest exemplu, îmi place subinterogarea corelată deoarece pare mai directă. Îmi este mai ușor să văd ce se contorizează.
În mintea mea, INNER JOIN este mai puțin direct. Mai întâi trebuie să vedeți că toate rândurile cu detalii de vânzare sunt returnate manual, apoi sintetizate. Nu primești cu adevărat acest lucru până când nu citești întreaga declarație.
Care este mai bine?
Spune-mi ce părere ai. Aș dori să aud dacă preferați să utilizați interogarea corelată sau exemplul INNER JOIN.