Imparare a scrivere una subquery correlata in SQL in 5 minuti

Se avete familiarità con la famosa matrioska russa, allora le subquery correlate di SQL dovrebbero essere un gioco da ragazzi: le subquery sono solo query annidate all'interno di query. Una subquery SQL è spesso chiamata query "interna"; la query principale è solitamente chiamata query "esterna". Questo articolo spiega tutto quello che c'è da sapere sulle subquery correlate.

Che cos'è esattamente una subquery SQL correlata?

Una sottoquery SQL correlata è semplicemente una sottoquery che viene eseguita più volte, una per ogni record (riga) restituito dalla query esterna (principale). In altre parole, la query esterna restituisce una tabella con più righe; la query interna viene quindi eseguita una volta per ciascuna di queste righe. Se la query esterna restituisce 10 righe, la query interna verrà eseguita 10 volte. Se la query esterna restituisce 100 righe, la query interna verrà eseguita 100 volte.

Quando si dovrebbe usare una subquery correlata SQL?

Alcune domande sui dati possono essere risolte solo con subquery correlate. Ciò è particolarmente vero quando si pongono domande negative sui dati.

Le domande sui dati negativi si presentano quando si cercano record che non soddisfano una particolare condizione. Un esempio di una semplice domanda sui dati negativi è: "Ottenere i nomi di tutti i film non prodotti da Steven Spielberg".

In questo articolo risolveremo un esempio di domanda sui dati negativi. Prima di farlo, rivediamo le tabelle coinvolte. Abbiamo due tabelle: la tabella employee e la tabella payment_history tabella. La seconda tabella ha una colonna denominata payment_type che indica se un pagamento fa parte di uno stipendio regolare, di un bonus o di un premio. Vediamo alcuni dati di esempio delle tabelle che utilizzeremo:

employee

payment_history

Ecco la domanda negativa a cui vorremmo rispondere:

"Ottenere i nomi dei dipendenti che non hanno mai ricevuto un premio".

La query SQL che risponde alla domanda precedente è la seguente:

SELECT last_name, first_name
FROM    employee e1
WHERE NOT EXISTS (SELECT ph.last_name 
                                      FROM payment_history ph 
                                      WHERE ph.employee_id = e1.employee_id 
                                      AND ph.payment_type = 'award')

Subquery correlate SQL vs. subquery semplici

La differenza principale tra una subquery correlata SQL e una subquery semplice è che una subquery correlata SQL fa riferimento alle colonne della tabella della query esterna.

Nell'esempio precedente, ph.employee_id = e1.employee_id è un riferimento alla tabella esterna della sottoquery (e1). Per identificare una sottoquery correlata, basta cercare questo tipo di riferimenti. Se ne trovate almeno uno, avete una subquery correlata!

La parte negativa di una domanda sui dati viene spesso risolta in una subquery correlata SQL utilizzando l'operatore NOT EXISTS nella clausola WHERE. EXISTS è un operatore sempre seguito da una subquery. Se la sottoquery restituisce almeno un record, allora EXISTS dà come risultato TRUE. Se la sottoquery restituisce un insieme vuoto, allora EXISTS dà come risultato FALSE. Si noti che usiamo NOT EXISTS, che è semplicemente l'opposto di EXISTS.

Il risultato della query precedente è:

Un altro esempio di sottoquery correlata

In questo esempio, cercheremo di ottenere i nomi di tutti i dipendenti che nel mese di marzo 2018 hanno percepito uno stipendio superiore alla media degli stipendi mensili di tutti i mesi precedenti, utilizzando una subquery correlata SQL. Ecco la query che eseguiremo:

SELECT    first_name, last_name 
FROM       employee e1, payment_history ph
WHERE    e1.employee_id = ph.employee_id 
     AND     amount_paid > = (
                               SELECT AVG(amount_paid) FROM payment_history ph2
                               WHERE ph2.employee_id = e1.employee_id
                                     AND ph2.payment_date  < '01/03/2018'
                                     AND ph2.payment_type = 'salary' 
                               )
     AND    month(ph.payment_date) =3 
     AND    year(ph.payment_date) = 2018 
     AND    ph.payment_type ='salary'

Basta con la negatività. E le domande sui dati positivi?

Dobbiamo usare una sottoquery correlata SQL per rispondere a una domanda sui dati positivi? No, non è necessario. Tuttavia, se lo si desidera, è possibile farlo. Per le domande positive, di solito è sufficiente utilizzare una condizione JOIN o una relazione tra due tabelle.

Cambiamo la nostra domanda precedente in una domanda positiva e risolviamola con una JOIN invece di una subquery correlata. La domanda diventa: "Ottenere i nomi dei dipendenti che hanno ricevuto pagamenti di premi". La query SQL (senza sottoquery correlate) che risponde a questa domanda è la seguente:

SELECT    first_name, last_name 
FROM       employee e1 
                 JOIN payment_history ph ON  ph.employee_id = e1.employee_id 
WHERE    ph.payment_type =award'

Il risultato è:

Attenzione: La sottoquery SQL correlata è probabilmente lenta

Vorrei solo ricordare che cerchiamo di non usare troppo le subquery SQL correlate, se possibile. Ricordiamo che una sottoquery correlata viene eseguita una volta per ogni record restituito dalla query esterna. Se la query esterna restituisce migliaia e migliaia di record, si può immaginare quanto rapidamente la query rallenti le prestazioni. In generale, si dovrebbe usare una sottoquery correlata SQL solo se è assolutamente necessario.

Provate voi stessi una subquery correlata SQL!

Abbiamo visto che le subquery correlate sono una parte importante del linguaggio SQL e possono aiutarci a rispondere a diverse domande sui dati, soprattutto quelle negative. Abbiamo anche spiegato come riconoscere una subquery correlata SQL e perché, in generale, dovremmo cercare di evitare le subquery correlate, se possibile, per motivi di prestazioni.

Per migliorare le vostre competenze in materia di subquery, provate il corso LearnSQL.it' SQL Basics . Abbiamo una sezione specifica dedicata alle subquery, con molti esercizi pratici ed esempi di subquery correlate per aiutarvi a padroneggiare il contenuto.