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 scalari o a più valori. Vengono in genere usate quando la query interna deve fare riferimento a un valore nella query esterna.

Tuttavia, a differenza delle sottoquery autonome, ci sono alcune considerazioni particolari da tenere presenti quando si utilizzano sottoquery correlate:

• 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 eseguita 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 hai fatto 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. Confrontate le seguenti query, che restituiscono i dettagli dei clienti che hanno effettuato ordini:

La prima query di esempio usa COUNT in una sottoquery:

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM SalesLT.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

La seconda query, che restituisce gli stessi risultati, usa EXISTS:

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM SalesLT.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

Nel primo esempio, la sottoquery deve contare ogni occorrenza di ogni custid trovato nella tabella SalesLT.SalesOrderHeader e confrontare i risultati del conteggio con zero, semplicemente per indicare che il cliente ha effettuato ordini.

Nella seconda query EXISTS restituisce TRUE per un custid non appena viene trovato un ordine pertinente nella tabella SalesLT.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.

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 nella tabella SalesLT.SalesOrderHeader viene trovato un custid, NOT EXISTS restituisce FALSE e la valutazione viene completata rapidamente.

Per scrivere query che utilizzano EXISTS con subquery, considera le seguenti linee guida:

• 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 *. La subquery non restituisce alcuna riga, quindi non è necessario specificare alcuna colonna.