Subquery correlate in SQL: Guida per principianti

A volte, l'utilizzo di una sottoquery correlata a SQL è l'unico modo per risolvere un'istruzione. Ma queste subquery possono essere molto lente. In questo post parleremo di quando utilizzare una subquery correlata, perché e come farlo.

Le subquery sono una risorsa importante per aumentare la potenza espressiva di SQL. Se non avete letto il nostro precedente articolo, le subquery sono semplicemente un'istruzione SELECT all'interno di un'altra SELECT. Possono essere utilizzate in diversi punti all'interno di SELECT, come ad esempio nelle clausole WHERE, HAVING, o FROM. Possono anche essere utilizzate in altre istruzioni, come UPDATE o DELETE.

Come le subquery semplici, una subquery correlata SQL contiene una query all'interno di una query. Il nome deriva dal fatto che le due query sono correlate; la query interna utilizza informazioni ottenute dalla query esterna (ad esempio, da una tabella a cui fa riferimento la query esterna). Per i programmatori, si tratta di una struttura simile a un ciclo annidato.

Prima di passare a un esempio di subquery correlata in SQL, facciamo un rapido ripasso sulle subquery semplici (non correlate).

Una semplice subquery

Il seguente esempio SQL utilizza una semplice subquery per ottenere un elenco di dipendenti che guadagnano più dello stipendio medio dell'azienda. In questo caso, la subquery è mostrata in grassetto e il suo risultato è lo stipendio medio dell'azienda. La query esterna confronta lo stipendio di ciascun dipendente con la media aziendale. Facile!

SELECT 
  lastname, 
  firstname, 
  salary 
FROM employee 
WHERE salary > (SELECT avg(salary) 
                FROM employee)

Esempio 1: una semplice subquery SQL non correlata

Le subquery correlate sono le successive. Queste sono molto particolari, perché a volte sono l'unico modo per risolvere una query. Tuttavia, pensateci due volte prima di usare una subquery correlata in SQL. Possono essere lente, come spiegheremo più avanti.

Utilizzo di una sottoquery correlata in SQL

Cominciamo con un esempio di subquery correlata in SQL. Supponiamo di voler trovare tutti i dipendenti con uno stipendio superiore a quello medio del loro reparto. Utilizzeremmo la seguente query. Ancora una volta, ho messo in grassetto la sottoquery:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE e1.salary > (SELECT avg(salary)
                   FROM employee e2 
                   WHERE e2.dept_id = e1.dept_id)

Esempio 2: Una sottoquery correlata in SQL

La differenza principale tra una subquery correlata in SQL e una subquery semplice è che le subquery correlate fanno riferimento a colonne della tabella esterna. Nell'esempio precedente, e1.dept_id i è un riferimento alla tabella esterna della subquery. Per identificare una query correlata, basta cercare questo tipo di riferimenti. Se ne trovate almeno uno, avete una subquery correlata SQL!

Vediamo un altro esempio. Supponiamo di voler ottenere i nomi dei reparti che hanno più di 10 dipendenti. Possiamo utilizzare la seguente subquery SQL correlata:

SELECT deptname
FROM department d1
WHERE 10 < (SELECT count(*)
            FROM employee e
            WHERE e.dept_id = d1.dept_id)

Esempio 3: Un'altra sottoquery correlata in SQL

È il momento di un ultimo esempio. Abbiamo detto che le subquery possono far parte delle clausole WHERE, FROM, HAVING e SELECT. In questo esempio, utilizzeremo una subquery correlata in SQL nell'elenco SELECT per trovare il nome di ogni dipendente, il suo stipendio e lo stipendio medio del suo reparto. Otterremo lo stipendio medio utilizzando una sottoquery correlata all'interno della clausola SELECT.

Ecco il codice:

SELECT 	
  lastname,
  firstname, 
  salary,
  (SELECT avg(salary) 
    FROM employee e2
    WHERE e2.dep_id = e1.dep_id) AS avg_dept_salary
FROM employee e1

Esempio 4: Una sottoquery correlata SQL nell'elenco SELECT

Quante volte viene eseguita una sottoquery correlata SQL?

Supponiamo di avere una tabella chiamata "assigned_to_project" che contiene i nomi dei dipendenti assegnati ai progetti. Vogliamo trovare tutti i dipendenti che non sono assegnati ad alcun progetto. La soluzione è la seguente query:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE NOT EXISTS (SELECT project_id
                  FROM assigned_to_project 
                  WHERE employee_id = e1.employee_id)

Esempio 5: Una sottoquery correlata in SQL

Innanzitutto, questa query è facile da analizzare. La clausola NOT EXISTS è TRUE quando la sottoquery restituisce un insieme di risultati vuoto. Questo accade solo per i dipendenti non assegnati ad alcun progetto. Anche in questo caso, è abbastanza facile!

Tuttavia, lo scopo di questa sezione è analizzare quante volte viene eseguita la sottoquery correlata a SQL. Cercate di capire quale delle seguenti affermazioni è corretta:

1. Viene eseguita una sola volta.
2. Viene eseguita una volta per ogni progetto.
3. Viene eseguita una volta per ogni dipendente non assegnato ad alcun progetto.
4. Viene eseguito una volta per ogni dipendente dell'azienda.

Analizziamo queste opzioni. Supponiamo di avere 1.000 dipendenti e 20 progetti. Inoltre, abbiamo 800 dipendenti già assegnati a un progetto. In questo caso, la prima opzione prevede un'esecuzione, la seconda 20 esecuzioni, la terza 200 e l'ultima 1.000 esecuzioni.

Se avete detto che la risposta corretta è "una volta per ogni dipendente dell'azienda", avete ragione. Se esaminiamo la dichiarazione, la risposta diventa ovvia: dobbiamo verificare se ogni dipendente ha dei progetti o meno. Secondo l'esempio precedente, però, questo significa che la sottoquery verrà eseguita 1.000 volte! In termini di prestazioni, questo è lo scenario peggiore dei quattro.

Poiché le subquery correlate tendono a implicare molte esecuzioni, tendono anche a essere lente. Di norma, per questo motivo, si cerca sempre di evitare l'uso di una subquery correlata in SQL. Tuttavia, come abbiamo già detto, a volte la correlazione è l'unico modo per risolvere una query.

Quando utilizzare una sottoquery correlata in SQL

A questo punto dell'articolo il lettore dovrebbe pensare: Quando usare una subquery correlata in SQL? In realtà, ci sono alcuni casi in cui è necessario. Questo è particolarmente vero nelle query in cui si cercano quelli che potremmo definire negativi.

Ecco un esempio di query "negativa". Supponiamo di avere una tabella dello storico dei pagamenti con una colonna chiamata payment_type che indica se un pagamento è uno stipendio regolare, un bonus o un premio. Se vogliamo una query che restituisca i dipendenti che non hanno mai ricevuto un premio, useremo questa query:

SELECT 
  lastname, 
  firstname
FROM employees e1
WHERE NOT EXISTS (SELECT ph.lastname 
                  FROM payment_history ph 
                  WHERE ph.emp_id = e1.employee_id 
                  AND ph.payment_type =’award’)

Esempio 6: Una sottoquery correlata a SQL che utilizza NOT EXISTS

EXISTS NOT EXISTS è un operatore unario. Ha un solo operando, che è una sottoquery (correlata o meno). Se la sottoquery restituisce almeno un record, EXISTS restituisce TRUE. Se la sottoquery non restituisce alcun record, EXISTS restituisce FALSE. In questo caso, è necessario utilizzare una subquery correlata per ottenere i risultati.

Utilizzo di sottoquery correlate nelle istruzioni UPDATE o DELETE

A volte si trovano subquery correlate nelle istruzioni UPDATE o DELETE. Il seguente UPDATE contiene una subquery correlata SQL che ottiene il nuovo valore della colonna all_money_made:

UPDATE employee emp
SET all_money_made = (SELECT SUM(payment)
                      FROM payment_history 
                      WHERE employee_id = emp.emp_id)

Esempio 7: Una sottoquery SQL correlata in un UPDATE

Questo articolo ci ha mostrato quando utilizzare una sottoquery correlata in SQL. Le query che cercano i valori negativi sono dei buoni candidati, anche se ci sono altre occasioni in cui una correlazione è l'unica opzione reale. Abbiamo anche visto quante volte viene eseguita una sottoquery correlata, di solito molte, molte volte. Questo è il loro maggiore svantaggio.

Provate voi stessi

Lesubquery correlate sono una risorsa importante per gli sviluppatori SQL. Per saperne di più e per migliorare le vostre competenze in materia di subquery, provate il corso LearnSQL.it' SQL Basics . Abbiamo una sezione specifica per le subquery, oltre a numerosi esercizi ed esempi!