Usare sottoquery autonome o correlate
In precedenza, sono state citate le sottoquery autonome in cui la query interna è indipendente dalla query esterna, viene eseguita una sola volta e restituisce i risultati alla query esterna. T-SQL supporta anche sottoquery correlate, in cui la query interna fa riferimento alla colonna della query esterna e concettualmente viene eseguita una volta per riga.
Uso di sottoquery correlate
Analogamente alle sottoquery autonome, le sottoquery correlate sono istruzioni SELECT nidificate all'interno di una query esterna. Le sottoquery correlate possono anche essere sottoquery scalari o multivalore. Vengono in genere usate quando la query interna deve fare riferimento a un valore nella query esterna.
Tuttavia, a differenza delle sottoquery autonome, per il loro uso è necessario tenere presenti alcune considerazioni speciali:
- Le sottoquery correlate non possono essere eseguite separatamente dalla query esterna. Questa restrizione complica il test e il debug.
- A differenza delle sottoquery autonome, che vengono elaborate una sola volta, le sottoquery correlate verranno eseguite più volte. Logicamente, la query esterna viene eseguita per prima e per ogni riga restituita viene elaborata la query interna.
Nell'esempio seguente viene usata una sottoquery correlata per restituire l'ordine più recente per ogni cliente. La sottoquery fa riferimento alla query esterna e fa riferimento al relativo valore CustomerID della clausola WHERE. Per ogni riga della query esterna, la sottoquery trova l'ID ordine massimo per il cliente a cui viene fatto riferimento in tale riga e la query esterna controlla se la riga che sta esaminando è la riga con tale ID ordine.
SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
(SELECT MAX(SalesOrderID)
FROM SalesLT.SalesOrderHeader AS o2
WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;
Scrittura di sottoquery correlate
Per scrivere sottoquery correlate, prendere in considerazione le linee guida seguenti:
- Scrivere la query esterna per accettare il risultato restituito appropriato dalla query interna. Se la query interna è scalare, è possibile usare operatori di uguaglianza e di confronto, ad esempio =, <, >, e <>, nella clausola WHERE. Se la query interna può restituire più valori, usare un predicato IN. Pianificare la gestione dei risultati NULL.
- Identificare la colonna della query esterna a cui farà riferimento la sottoquery correlata. Dichiarare un alias per la tabella che rappresenta l'origine della colonna nella query esterna.
- Identificare la colonna della tabella interna che verrà confrontata con la colonna della tabella esterna. Creare un alias per la tabella di origine, come per la query esterna.
- Scrivere la query interna per recuperare i valori dalla relativa origine, in base al valore di input dalla query esterna. Ad esempio, usare la colonna esterna nella clausola WHERE della query interna.
La correlazione tra le query interne ed esterne si verifica quando la query interna fa riferimento al valore esterno per il confronto. È questa correlazione ad assegnare il nome alla sottoquery.
Uso di EXISTS
Oltre al recupero di valori da una sottoquery, T-SQL offre un meccanismo per verificare se una query restituisce risultati. Il predicato EXISTS determina se esistono righe che soddisfano una condizione specifica, ma anziché restituirle restituisce TRUE o FALSE. Questa tecnica è utile per convalidare i dati senza incorrere nel sovraccarico causato dal recupero e dall'elaborazione dei risultati.
Quando una sottoquery è correlata alla query esterna usando il predicato EXISTS, SQL Server gestisce i risultati della sottoquery in modo speciale. Invece di recuperare un valore scalare o un elenco multivalore dalla sottoquery, EXISTS controlla semplicemente se sono presenti righe nel risultato.
Concettualmente, un predicato EXISTS equivale al recupero dei risultati, al conteggio delle righe restituite e al confronto del conteggio con zero. Confrontare le query seguenti, che restituiranno informazioni dettagliate sui clienti che hanno inoltrato ordini:
La prima query di esempio usa COUNT in una sottoquery:
SELECT CustomerID, CompanyName, EmailAddress
FROM Sales.Customer AS c
WHERE
(SELECT COUNT(*)
FROM Sales.SalesOrderHeader AS o
WHERE o.CustomerID = c.CustomerID) > 0;
La seconda query, che restituisce gli stessi risultati, usa EXISTS:
SELECT CustomerID, CompanyName, EmailAddress
FROM Sales.Customer AS c
WHERE EXISTS
(SELECT *
FROM Sales.SalesOrderHeader AS o
WHERE o.CustomerID = c.CustomerID);
Nel primo esempio la sottoquery deve contare ogni occorrenza di custid presente nella tabella Sales.SalesOrderHeader e confrontare i risultati del conteggio con zero, semplicemente per indicare che il cliente ha inoltrato degli ordini.
Nella seconda query EXISTS restituisce TRUE per un oggetto custid non appena viene trovato un ordine pertinente nella tabella Sales.SalesOrderHeader. Il conteggio completo di ogni occorrenza non è necessario. Si noti anche che con il formato EXISTS, la sottoquery non è limitata alla restituzione di una singola colonna. In questo caso è presente SELECT *. Le colonne restituite sono irrilevanti perché viene verificato solo se vengono restituite righe, non i valori presenti in tali righe.
Dal punto di vista dell'elaborazione logica, i due moduli di query sono equivalenti. Dal punto di vista delle prestazioni, il motore di database può considerare le query in modo diverso quando le ottimizza per l'esecuzione. Provare a testarle entrambe per il proprio uso.
Nota
Se si converte una sottoquery che usa COUNT(*) in una che usa EXISTS, assicurarsi che la sottoquery usi SELECT * e non SELECT COUNT(*). SELECT COUNT(*) restituisce sempre una riga, quindi EXISTS restituirà sempre TRUE.
Un'altra applicazione utile di EXISTS è la negazione della sottoquery con NOT, come nell'esempio seguente, che restituirà qualsiasi cliente che non ha mai inoltrato un ordine:
SELECT CustomerID, CompanyName, EmailAddress
FROM SalesLT.Customer AS c
WHERE NOT EXISTS
(SELECT *
FROM SalesLT.SalesOrderHeader AS o
WHERE o.CustomerID = c.CustomerID);
SQL Server non deve restituire dati sugli ordini correlati per i clienti che hanno inoltrato gli ordini. Se viene trovato un oggetto custid nella tabella Sales.SalesOrderHeader, NOT EXISTS restituisce FALSE e la valutazione viene completata rapidamente.
Per scrivere query che usano EXISTS con sottoquery, prendere in considerazione le linee guida seguenti:
- La parola chiave EXISTS segue direttamente WHERE. Nessun nome di colonna, o altra espressione, la precede, a meno che non venga usato anche NOT.
- All'interno della sottoquery usare SELECT *. Non viene restituita alcuna riga dalla sottoquery, quindi non è necessario che siano specificate colonne.