Sottoquery SQL: una guida completa

Le sottoquery SQL sono uno strumento potente nella gestione dei database e permettono un recupero dei dati più complesso ed efficiente. Questa guida ti accompagnerà nei fondamenti delle sottoquery SQL, offrendo spunti sulle loro applicazioni pratiche e sulle tecniche avanzate. Che tu sia alle prime armi o un professionista esperto, padroneggiare le sottoquery può migliorare sensibilmente le tue competenze in SQL.

Se sei nuovo a SQL, valuta di iniziare con il nostro corso Intermediate SQL per costruire basi solide. Inoltre, trovo che il SQL Basics Cheat Sheet, che puoi scaricare, sia un riferimento utile perché contiene tutte le funzioni SQL più comuni. Infine, voglio dirti che le sottoquery sono una tipica domanda da colloquio su SQL, quindi se stai preparando un colloquio, sei nel posto giusto per un ripasso.

Che cos'è una sottoquery SQL?

Una sottoquery rende le query SQL più modulari gestendo compiti che altrimenti richiederebbero più query separate.

Definizione e scopo

Una sottoquery SQL è una query annidata all'interno di un'altra query SQL, usata per eseguire operazioni che richiedono più passaggi o logiche complesse. Il ruolo delle sottoquery in SQL include quanto segue:

• Filtrare i record in base a dati provenienti da tabelle correlate.
• Aggregare dati ed effettuare calcoli in modo dinamico.
• Incrociare dati tra tabelle per ottenere insight specifici.
• Selezionare righe in modo condizionale senza richiedere join espliciti o logica esterna nel codice.

Sembra tanto, ma tutto avrà senso man mano che esploriamo questi aspetti nel tutorial.

Tipi di sottoquery

Potrebbe sorprenderti sapere che esistono diversi tipi di sottoquery. I vari tipi sono raggruppati e adatti a differenti esigenze di recupero dati. Puoi scegliere tra le seguenti sottoquery a seconda dell'operazione che vuoi eseguire:

Sottoquery scalari

Le sottoquery scalari restituiscono un singolo valore, ad esempio una riga e una colonna. Sono spesso usate dove ci si aspetta un solo valore, come in calcoli, confronti o assegnazioni nelle clausole SELECT o WHERE.

Nell'esempio seguente, la sottoquery scalare (SELECT AVG(salary) FROM employees) restituisce un singolo valore, lo stipendio medio, e lo confronta con lo stipendio di ciascun dipendente.

-- Example of Scalar Subquery 
-- Compares each salary to the average salary
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS average_salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Sottoquery di colonna

Le sottoquery di colonna restituiscono una singola colonna ma più righe. Queste sottoquery sono spesso usate con operatori come IN o ANY, dove la query esterna confronta valori provenienti da più righe.

Per esempio, la sottoquery seguente restituisce un elenco di ID reparto per i reparti situati a New York, che la query principale usa poi per filtrare i dipendenti in quei reparti.

-- Example of Column Subquery 
-- Filters based on departments in New York
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Sottoquery di riga

Le sottoquery di riga restituiscono una singola riga contenente più colonne. Sono tipicamente usate con operatori di confronto in grado di confrontare una riga di dati, come gli operatori = o IN, quando ci si aspetta più valori.

La sottoquery seguente recupera il reparto e il titolo di lavoro di un manager, e la query esterna trova i dipendenti con valori corrispondenti.

-- Example of Row Subquery 
-- Matches department and job title with a specific manager
SELECT employee_name
FROM employees
WHERE (department_id, job_title) = (SELECT department_id, job_title FROM managers WHERE manager_id = 1);

Sottoquery di tabella (tabelle derivate)

Le sottoquery di tabella, o tabelle derivate, restituiscono una tabella completa con più righe e colonne. Sono comunemente usate nella clausola FROM come tabella temporanea all'interno di una query.

Per esempio, la sottoquery seguente crea una tabella derivata degli stipendi medi per reparto, che viene poi usata nella query esterna per trovare i reparti con uno stipendio medio sopra una soglia specificata.

-- Example of Table Subquery 
-- Uses derived table for average department salary comparison
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM 
    (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE dept_avg.avg_salary > 50000;

Capire le sottoquery SQL

Dagli esempi sopra, abbiamo visto che una sottoquery è racchiusa tra parentesi. Esploriamo ora la meccanica di una sottoquery, inclusi la sua sintassi e l'ordine di esecuzione.

Sintassi e struttura

La sintassi di una sottoquery varia in base a dove è usata nell'istruzione SQL principale, ad esempio nelle clausole SELECT, FROM o WHERE. Le sottoquery sono tipicamente racchiuse tra parentesi ( ), a indicare una query separata. 

L'esempio seguente mostra una sottoquery all'interno di una clausola WHERE, che consente di filtrare i dati nella query principale in base ai risultati di una query annidata. 

-- Selects the main column to retrieve from the main table to query
SELECT column_name
FROM table_name
-- Applies a condition to filter rows based on the subquery result
WHERE column_name operator 
      -- Subquery retrieves data for comparison in the WHERE clause
      (SELECT column_name FROM table_name WHERE condition);  

Ordine di esecuzione

L'ordine di esecuzione delle sottoquery dipende dal fatto che siano correlate o non correlate.

Sottoquery non correlate

Le sottoquery non correlate sono indipendenti dalla query esterna ed eseguono per prime. Il risultato della sottoquery viene poi passato alla query esterna. Le sottoquery non correlate sono comunemente usate per calcoli e filtri a livello scalare o di colonna.

La query seguente segue il seguente ordine di esecuzione:

• La sottoquery (SELECT AVG(salary) FROM employees) viene eseguita per prima e calcola lo stipendio medio.

• La query esterna poi recupera i dipendenti il cui stipendio è superiore a questa media.

-- Retrieves names of employees with above-average salary
SELECT employee_name  
FROM employees
-- Subquery: calculates average salary across all employees
WHERE salary > (SELECT AVG(salary) FROM employees);  

Ti consiglio di seguire il corso Introduction to SQL Server di DataCamp per approfondire raggruppamenti e aggregazioni di dati, e le join tra tabelle.

Sottoquery correlate

Le sottoquery correlate dipendono dalla query esterna per parte dei loro dati, quindi vengono ricalcolate per ogni riga elaborata dalla query esterna. 

La query seguente viene eseguita in questo ordine:

• Per ogni riga in employees (con alias e1), la sottoquery (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) calcola lo stipendio medio per quello specifico reparto.

• La query esterna poi confronta lo stipendio di ciascun dipendente con la media del reparto e include solo chi guadagna di più.

-- Retrieves names of employees with above-average salary in their department
SELECT e1.employee_name  
FROM employees e1
 -- Subquery: calculates average salary for each department
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id); 

Applicazioni della sottoquery SQL

Le sottoquery SQL sono importanti per il recupero e la trasformazione dei dati, permettendoti di scrivere query complesse e gestire attività avanzate di analisi. Di seguito trovi applicazioni reali delle sottoquery nella gestione dei database.

Filtraggio dei dati

Le sottoquery sono utili quando filtri i dati in base a condizioni dinamiche, soprattutto quando il filtraggio richiede di confrontare valori tra più tabelle o di eseguire calcoli.

La sottoquery seguente recupera il category_id di "Product A", e la query principale trova tutti i prodotti in quella categoria.

-- Retrieves names of products in the same category as 'Product A'
SELECT product_name  
FROM products
 -- Subquery: finds category ID of 'Product A'
WHERE category_id = (SELECT category_id FROM products WHERE product_name = 'Product A'); 

Aggregazione dei dati

Le sottoquery sono usate anche per l'aggregazione dei dati, soprattutto quando si generano statistiche riepilogative o insight per report e analisi. La sottoquery (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) calcola le vendite medie per reparto. La query esterna poi filtra i reparti con vendite medie superiori a 50.000. 

-- Retrieves department IDs and their average sales
-- Filters for departments with average sales over 50,000
SELECT department_id, avg_sales  
FROM (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) AS dept_sales  -- Subquery: calculates average sales per department
WHERE avg_sales > 50000;  

Considerazioni sulle prestazioni e best practice

Sebbene le sottoquery siano potenti nella scrittura di query complesse, possono influire sulle prestazioni, soprattutto con dataset di grandi dimensioni. È importante considerare gli errori comuni e le best practice per migliorare le performance.

Ottimizzare le prestazioni delle sottoquery

Ottimizzare le prestazioni delle sottoquery garantisce tempi di esecuzione migliori e una maggiore reattività del database. Ecco alcuni modi per ottimizzarle.

• Indicizza le colonne rilevanti: per velocizzare il recupero dei dati, assicurati che le colonne usate nelle clausole WHERE e JOIN e nelle operazioni di confronto siano indicizzate.

• Limita l'uso di sottoquery correlate: quando possibile, usa operazioni di JOIN o CTE al posto delle sottoquery correlate, perché spesso possono elaborare i dati più velocemente usando operazioni su insiemi anziché elaborazioni riga per riga.

• Limita il numero di colonne nelle sottoquery: seleziona solo le colonne necessarie nelle sottoquery per ridurre il recupero dei dati, l'uso di memoria e permettere al database di ottimizzare l'esecuzione.

• Usa EXISTS invece di IN: se una sottoquery restituisce un dataset ampio, usare EXISTS invece di IN può migliorare le prestazioni. L'operatore EXISTS interrompe l'elaborazione quando trova una riga corrispondente, mentre IN continua a valutare l'intero risultato della sottoquery.

Evitare gli errori comuni

Se scrivi male le sottoquery, potresti incontrare problemi durante l'esecuzione. Vediamo come evitare questi errori.

• Evita sottoquery correlate non necessarie: le sottoquery correlate sono onerose in termini di risorse, quindi evitalo quando il risultato può essere ottenuto con una sottoquery non correlata o con una join.

• Fai attenzione ai valori NULL nelle sottoquery: i valori NULL possono portare a risultati inattesi, soprattutto nelle sottoquery che usano operatori di confronto come IN o =. Per evitare errori, valuta di usare COALESCE per gestire i null oppure assicurati che le colonne non siano nullable se usate per confronti.

• Evita di usare SELECT * nelle sottoquery: usare SELECT * può risultare inefficiente, perché recupera tutte le colonne anche se non ti servono. Questo aumenta l'uso di memoria e può rallentare l'esecuzione, specialmente con dataset grandi.

• Usa alias significativi: assegna nomi chiari a tabelle e sottoquery per migliorare la leggibilità.

Prova il nostro percorso di carriera SQL Server Developer, che ti darà le competenze per scrivere, risolvere problemi e ottimizzare le tue query con SQL Server. 

Tecniche avanzate di sottoquery SQL

Sebbene le sottoquery offrano un modo efficiente per scrivere query complesse, esistono metodi avanzati per gestire dati gerarchici. Vediamo le tecniche e le strategie avanzate per applicare le sottoquery SQL.

Sottoquery ricorsive

Le sottoquery ricorsive (note anche come CTE ricorsive) ti permettono di recuperare dati gerarchici, come strutture organizzative, categorie di prodotto o relazioni di tipo grafo, dove ogni elemento dei dati è collegato a un altro.

Supponiamo di avere una tabella employees con employee_id, manager_id ed employee_name. Vuoi recuperare la gerarchia dei dipendenti sotto un manager specifico.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Start with the specified manager
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
 -- Assuming the top-level manager has NULL as manager_id
    WHERE manager_id IS NULL 
    
    UNION ALL
    
    -- Recursive Query: Find employees who report to those in the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Nella query sopra:

• Quella che chiamo Anchor Query seleziona il manager di livello più alto (dove manager_id è NULL).

• La Recursive Query fa una join di employees con la stessa CTE (EmployeeHierarchy), trovando i dipendenti che riportano a ciascun dipendente già recuperato.

• La ricorsione continua finché non ci sono più dipendenti che riportano a quelli trovati.

Combinare sottoquery con altre funzionalità SQL

Puoi integrare le sottoquery con altre funzionalità SQL come le funzioni finestra, le istruzioni CASE e le funzioni di raggruppamento. Queste combinazioni consentono manipolazioni avanzate dei dati e reportistica più completa.

Combinare sottoquery con funzioni finestra

Le sottoquery possono essere usate per affinare il dataset su cui agiscono le funzioni finestra, rendendole utili per ranking, totali cumulativi e medie mobili. Supponiamo tu voglia classificare i prodotti per vendite all'interno di ciascuna regione. Puoi usare una sottoquery per selezionare i dati rilevanti e poi applicare una funzione finestra per il ranking.

 -- Ranks products by sales within each region
SELECT region, product_id, sales, 
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank 
-- Subquery: calculates total sales per product in each region
FROM (SELECT region, product_id, SUM(sales) AS sales  
      FROM sales_data
      GROUP BY region, product_id) AS regional_sales;

Uso delle sottoquery con le istruzioni CASE

Combinare le sottoquery con le istruzioni CASE può aiutarti ad applicare condizioni complesse basate su calcoli dinamici. La query seguente classifica i prodotti come “High”, “Medium” o “Low” in base alle loro vendite rispetto alla media della loro categoria.

 -- Categorize above-average sales, average sales, and below-average sales
SELECT product_id, category_id, sales,
       CASE 
           WHEN sales > (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'High' 
           WHEN sales = (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'Medium' 
           ELSE 'Low'
       END AS performance
FROM products AS p;

Sottoquery con funzioni di aggregazione per aggregazioni condizionali

Puoi anche calcolare aggregazioni condizionali usando sottoquery all'interno di funzioni di aggregazione. Supponiamo tu voglia calcolare il fatturato totale generato solo dai clienti attivi. Nell'esempio seguente, la sottoquery recupera tutti i clienti attivi. La query principale poi filtra gli ordini per includere solo quelli effettuati dai clienti attivi, calcolando il fatturato totale di questo gruppo.

 -- Calculates total revenue from active customers
SELECT SUM(order_total) AS active_customer_revenue 
FROM orders
  -- Subquery: retrieves IDs of active customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);

Quando si usano le sottoquery SQL

Le sottoquery SQL offrono applicazioni versatili nel mondo reale per l'analisi dei dati, motivo per cui sono usate così spesso. Di seguito alcune applicazioni specifiche per settore o di natura matematica delle sottoquery SQL. Per arrivare a queste idee, ho cercato di pensare a lavori di data analysis o data engineering che richiedono più passaggi, che è, secondo me, dove le sottoquery aiutano davvero.

Esempi specifici di settore

Le sottoquery possono offrire soluzioni utili nei settori finance, healthcare e retail. Ecco alcune idee:

• Valutazione del rischio per approvazioni di prestiti (Finanza): immagino le banche alle prese con metriche come il rapporto debito/reddito e i punteggi di credito. Annidando queste metriche in sottoquery, gli analisti possono dare un senso a indicatori finanziari complicati. Magari, una sottoquery può calcolare l'importo medio del prestito per clienti in specifiche fasce di reddito.

• Identificazione di pattern nelle diagnosi dei pazienti (Sanità): nella sanità, gestire i dati dei pazienti può essere un grosso lavoro. Le sottoquery possono aiutare a scomporre questa complessità. Immagino di usare sottoquery per tracciare la frequenza delle diagnosi in diversi gruppi d'età o fattori di rischio.

• Ottimizzazione del posizionamento dei prodotti in base alla cronologia degli acquisti (Retail): i retailer prosperano comprendendo i pattern di acquisto. Le sottoquery possono mostrare dati di acquisto annidati per identificare i prodotti spesso acquistati insieme. Questo può aiutare a posizionare strategicamente articoli complementari e aumentare le vendite.

Connessioni matematiche

Le sottoquery sono usate anche per identificare pattern e trend nei dati tramite connessioni matematiche e logiche. Ecco alcuni scenari in cui si applicano nelle matematiche.

• Medie mobili per analisi delle serie temporali: quando analizzi i trend nel tempo, le sottoquery semplificano il calcolo delle medie mobili. Le vedo definire finestre temporali specifiche nelle query annidate, rendendo più facile smussare i dati e individuare i trend.

• Rilevare outlier usando le deviazioni standard: individuare gli outlier è importante per molte cose, incluso il rilevamento di frodi. Le sottoquery rendono semplice calcolare metriche come le deviazioni standard all'interno di query annidate.

• Uso dei concetti della teoria degli insiemi: trovo interessante come le sottoquery rispecchino le operazioni della teoria degli insiemi come UNION e INTERSECT. Questa capacità è perfetta per attività come l'analisi della fidelizzazione dei clienti, dove capire sovrapposizioni e differenze tra gruppi di clienti può guidare strategie di marketing più intelligenti.

Conclusione

Padroneggiare le sottoquery SQL può migliorare notevolmente la tua capacità di gestire e analizzare i dati in modo efficiente. Comprendendone struttura, applicazioni e best practice, puoi ottimizzare le tue query SQL per prestazioni migliori. Inoltre, voglio dire che padroneggiare le sottoquery rende semplicemente più facile scrivere SQL, quindi vale la pena impararle.

Se ti interessa diventare un abile data analyst, dai un'occhiata al nostro percorso di carriera Associate Data Analyst in SQL per apprendere le competenze necessarie. Il corso Reporting in SQL è adatto anche se vuoi imparare a creare dashboard professionali usando SQL. Infine, ti consiglio di ottenere la SQL Associate Certification per dimostrare la tua padronanza dell'uso di SQL per l'analisi dei dati e distinguerti tra gli altri professionisti del dato.