Questo è il secondo di una serie di articoli sulle sottoquery. In questo articolo, discutiamo le sottoquery nellelenco delle colonne dellistruzione SELECT. Altri articoli discutono i loro usi in altre clausole.
Tutti gli esempi per questa lezione sono basati su Microsoft SQL Server Management Studio e sul database AdventureWorks2012. Puoi iniziare a utilizzare questi strumenti gratuiti utilizzando la mia Guida Introduzione alluso di SQL Server.
Uso di sottoquery nellistruzione Select
Quando una sottoquery viene inserita nellelenco di colonne, viene utilizzata restituisce valori singoli. In questo caso, puoi pensare alla sottoquery come a una singola espressione di valore. Il risultato restituito non è diverso dallespressione “2 + 2.” Naturalmente, anche le sottoquery possono restituire testo, ma hai capito!
Quando si lavora con le sottoquery, listruzione principale a volte è chiamata query esterna. Le sottoquery sono racchiuse tra parentesi, questo le rende più facili da individuare .
Fai attenzione quando usi le sottoquery. Possono essere divertenti da usare, ma man mano che ne aggiungi altre alla tua query possono iniziare a rallentarla.
Sottoquery semplice per calcolare la media
Cominciamo con una semplice query per mostrare SalesOrderDetail e confrontiamola con la media complessiva SalesOrderDetail LineTotal. Listruzione SELECT che utilizzeremo è:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Questa query restituisce risultati come:
La sottoquery, mostrata in rosso sopra, viene eseguita per prima per ottenere il LineTotal medio.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Questo risultato viene quindi ricollegato allelenco delle colonne e la query continua. Ci sono diverse cose che voglio sottolineare :
- Le sottoquery sono racchiuse tra parentesi .
- Quando le sottoquery vengono utilizzate in unistruzione SELECT, possono restituire solo un valore. Questo dovrebbe avere senso, la semplice selezione di una colonna restituisce un valore per una riga e dobbiamo seguire lo stesso modello.
- In generale, la sottoquery viene eseguita solo una volta per lintera query e il suo risultato riutilizzato . Questo perché il risultato della query non varia per ogni riga restituita.
- È importante utilizzare alias per i nomi delle colonne per migliorare la leggibilità.
Sottoquery semplice in Expression
Come ci si potrebbe aspettare, il risultato di una sottoquery può essere utilizzato in altre espressioni. Partendo dallesempio precedente, utilizziamo la sottoquery per determinare di quanto il nostro LineTotal varia dalla media.
La varianza è semplicemente il LineTotal meno il totale della linea media. Nella seguente sottoquery, lho colorato di blu. Ecco la formula per la varianza:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
Listruzione SELECT racchiusa tra parentesi è la sottoquery. Come nellesempio precedente, questa query verrà eseguita una volta, restituirà un valore numerico, che viene quindi sottratto da ogni valore LineTotal.
Ecco la query nella forma finale:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Ecco il risultato:
Quando lavoro con sottoquery nelle istruzioni select di solito creo e prova prima la sottoquery. Le istruzioni SELECT possono complicarsi molto rapidamente. È meglio costruirli a poco a poco. Costruendo e testando i vari pezzi separatamente, aiuta davvero con il debug.
Query correlate
Ci sono modi per incorporare i valori della query esterna nelle clausole della sottoquery. Questi tipi di query sono denominati sottoquery correlate, poiché i risultati della sottoquery sono collegati, in qualche forma, ai valori nella query esterna. Le query correlate sono talvolta chiamate query sincronizzate.
Se hai problemi a sapere cosa significa correlazione, controlla questa definizione di Google:
Correlate: “have a mutual connection or connection, in cui una cosa influisce o dipende da unaltra. “
Un utilizzo tipico per una sottoquery correlata è quella di una delle colonne della query esterna nella clausola WHERE della query interna. Questo è buon senso in molti casi si desidera limitare la query interna a un sottoinsieme di dati.
Esempio di sottoquery correlata
Forniremo un esempio di sottoquery correlata riportando ogni Totale riga SalesOrderDetail e il totale riga medio per le vendite complessive Ordine.
Questa richiesta differisce notevolmente dai nostri esempi precedenti poiché la media che stiamo calcolando varia per ogni ordine di vendita.
È qui che entrano in gioco le sottoquery correlate. Possiamo utilizzare un valore dalla query esterna e incorporarlo nei criteri di filtro della sottoquery.
Prendiamo un guarda come calcoliamo il totale medio della linea. Per fare ciò ho messo insieme unillustrazione che mostra listruzione SELECT con una sottoquery.
Per elaborare ulteriormente il diagramma. Listruzione SELECT è costituita da due parti, la query esterna e la sottoquery. La query esterna viene utilizzata per recuperare tutte le righe SalesOrderDetail.La sottoquery viene utilizzata per trovare e riepilogare le righe dei dettagli dellordine cliente per un SalesOrderID specifico.
Se dovessi verbalizzare i passaggi prenderemo, li riassumerò come:
- Ottieni SalesOrderID.
- Restituisci il totale di riga medio da tutti gli elementi SalesOrderDetail dove SalesOrderID corrisponde.
- Continua al successivo SalesOrderID nella query esterna e ripeti i passaggi 1 e 2.
La query che puoi eseguire nel database AdventureWork2012 è:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Di seguito sono riportati i risultati della query:
Ce ne sono un paio elementi da evidenziare.
- Come puoi vedere, ho utilizzato alias di colonna per rendere i risultati della query più facili da leggere.
- Ho anche usato un alias di tabella, SOD, per query esterna. Ciò rende possibile utilizzare i valori della query esterna nella sottoquery. In caso contrario, la query non è correlata!
- Luso degli alias di tabella rende non ambigue le colonne di ciascuna tabella.
Analisi della sottoquery correlata
Proviamo ora a suddividerlo utilizzando SQL.
Per iniziare, supponiamo di ottenere solo il nostro esempio per SalesOrderDetailID 20. Il corrispondente SalesOrderID è 43661.
Ottenere il totale riga medio per questo elemento è facile
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Questo restituisce il valore 2181.765240.
Ora che abbiamo la media possiamo inseriscilo nella nostra query
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Utilizzando le sottoquery questo diventa
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
La query finale è :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Sottoquery correlata con una tabella diversa
Una sottoquery correlata, o se è per questo qualsiasi sottoquery, può utilizzare una tabella diversa da la query esterna. Ciò può tornare utile quando si lavora con una tabella “genitore”, come SalesOrderHeader, e si desidera includere nel risultato un riepilogo delle righe figlie, come quelle di SalesOrderDetail.
Restituiamo il OrderDate, TotalDue e numero di righe dei dettagli dellordine di vendita. A tale scopo, possiamo utilizzare il seguente diagramma per orientarci:
Per fare ciò includeremo una sottoquery correlata nella nostra istruzione SELECT per restituire il COUNT di righe SalesOrderDetail. Ci assicureremo di contare lelemento SalesOrderDetail corretto filtrando lID SalesOrderID della query esterna.
Ecco listruzione SELECT finale:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
I risultati sono:
Alcune cose da notare con questo esempio sono:
- La sottoquery sta selezionando i dati da una tabella diversa dalla query esterna.
- Ho usato table e alias di colonna per facilitare la lettura dellSQL e dei risultati.
- Assicurati di k la tua clausola dove! Se dimentichi di includere il nome della tabella o gli alias nella clausola WHERE della sottoquery, la query non sarà correlata.
Sottoquery correlate rispetto a Inner join
È importante per capire che puoi ottenere gli stessi risultati utilizzando una sottoquery o un join. Sebbene entrambi restituiscano gli stessi risultati, ci sono vantaggi e svantaggi per ogni metodo!
Considera lultimo esempio in cui contiamo gli elementi pubblicitari per gli elementi SalesHeader.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Questa stessa query può essere eseguita utilizzando un INNER JOIN insieme a GROUP BY come
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
Qual è più veloce?
Scoprirai che molte persone diranno di evitare le sottoquery poiché sono più lente. Sosterranno che la sottoquery correlata deve “essere eseguita” una volta per ogni riga restituita nella query esterna, mentre INNER JOIN deve solo fare un passaggio attraverso i dati.
Io stesso? Dico check out il piano di query. Ho seguito il mio consiglio per entrambi gli esempi precedenti e ho scoperto che i piani sono gli stessi!
Questo non vuol dire che i piani cambierebbero se ci fossero più dati, ma il mio punto è che non dovresti solo fare supposizioni. La maggior parte degli ottimizzatori SQL DBMS sono davvero bravi a capire il modo migliore per eseguire la tua query. Prenderanno le tue sintassi, come una sottoquery o INNER JOIN, e le useranno per creare un piano di esecuzione effettivo.
Quale è più facile da leggere?
A seconda di cosa ti senti a tuo agio, potresti trovare lesempio INNER JOIN più facile da leggere rispetto alla query correlata. Personalmente, in questo esempio, mi piace la sottoquery correlata in quanto sembra più diretta. È più facile per me vedere cosa viene contato.
Nella mia mente, INNER JOIN è meno diretto. Per prima cosa devi vedere che tutte le righe dei dettagli di vendita vengono restituite a mano e quindi riepilogate. Non lo capisci davvero finché non leggi lintera dichiarazione.
Qual è il migliore?
Fammi sapere cosa ne pensi. Vorrei sapere se preferisci utilizzare la sottoquery correlata o lesempio INNER JOIN.