Conversione di subquery in join

Non tutte le query sono uguali, soprattutto in termini di prestazioni. In questo articolo vedremo come convertire le subquery SQL in join per migliorare l'efficienza.

Quando è opportuno utilizzare le subquery SQL?

Ottima domanda! Purtroppo non esiste una risposta concreta. I principianti di SQL tendono a fare un uso eccessivo delle subquery. In genere, una volta scoperto che la costruzione SQL funziona in una situazione, cercano di applicare lo stesso approccio ad altre situazioni. È naturale.

Tuttavia, in alcuni casi una subquery può essere sostituita da una JOIN più efficiente. Se si può evitare una subquery e sostituirla con una clausola di JOIN, lo si dovrebbe fare senza esitazione. Ma naturalmente, in alcuni casi, l'uso di una subquery è l'unico modo per risolvere un problema di dati. In questo articolo mostrerò esempi di entrambi i casi: quando una subquery è indispensabile e quando invece dovrebbe essere evitata e sostituita da una JOIN.

I dati

Prima di passare agli esempi, vediamo brevemente il database di esempio che utilizzeremo. Il database contiene due tabelle che rappresentano le statistiche di produzione di un'azienda agricola di mele immaginaria di nome EverRed. L'azienda ha tre fattorie.

La prima tabella è current_year_productionche contiene informazioni sul numero di mele prodotte nell'anno in corso da ogni azienda agricola, nonché sulla superficie e sul numero di alberi di ogni azienda. La seconda tabella, production_historycontiene le informazioni sulla produzione passata di ogni azienda agricola. Di seguito sono riportati alcuni esempi di dati provenienti da queste due tabelle.

Esempio: sostituire una subquery con una JOIN

Supponiamo di essere un analista di dati SQL che lavora presso EverRed. Il proprietario dell'azienda vuole ottenere i nomi delle aziende agricole in cui l'azienda sta producendo più mele nell'anno in corso rispetto all'anno precedente (2017).

Soluzione utilizzando una sottoquery:

SELECT farm_name, 
FROM current_year_production CYP
WHERE production_in_kg > (
  SELECT production_in_kg 
  FROM production_history PH  
  WHERE PH.farm_id = CYP.farm_id
  AND year = 2017
) 

Soluzione utilizzando una clausola JOIN:

SELECT farm_name, 
FROM current_year_production CYP 
JOIN production_history PH
  ON PH.farm_id = CYP.farm_id 
WHERE PH.year = 2017
  AND CYP.production_in_kg > PH.production_in_kg 

La differenza tra questi due approcci sta nelle prestazioni. Mentre la clausola JOIN del secondo esempio deve essere eseguita una sola volta, la subquery del primo esempio verrà eseguita una volta per azienda. In questo caso, abbiamo solo tre fattorie, quindi la differenza è trascurabile. Ma se si lavorasse per un'azienda più grande, con 10.000 fattorie globali? La subquery dovrebbe essere eseguita 10.000 volte. È chiaro che una sottoquery è inefficiente per i nostri scopi.

Inoltre, in un database di prova con poche aziende, entrambe le query vengono eseguite con un tempo di risposta accettabile; tuttavia, quando si passa a un database produttivo (dove il volume di dati è solitamente molto più elevato), il tempo di risposta dell'approccio con subquery aumenterà in modo significativo, mentre il tempo di risposta dell'approccio JOIN rimarrà stabile.

Il risultato di entrambe le precedenti query equivalenti è:

Esempio: quando le subquery sono l'unica strada da percorrere

Supponiamo ora che il proprietario dell'azienda, dopo aver letto i risultati forniti nella query precedente, vi chieda di ottenere i nomi delle aziende agricole che quest'anno stanno producendo più mele per metro quadro rispetto alla media storica. Sembra complesso, ma è più facile di quanto sembri.

Soluzione con una sottoquery:

SELECT
  farm_name, 
  production_in_kg / area AS "production_per_meter"
FROM Current_year_production 
WHERE production_in_kg / area > (
  SELECT AVG(PH.production_in_kg / CYP.area) 
  FROM production_history PH 
  JOIN Current_year_production CYP  
    ON PH.farm_id = CYP.farm_id 
) 

Non possiamo sostituire questa subquery con una JOIN perché non abbiamo una tabella con la media calcolata in precedenza. In altre parole, dobbiamo prima calcolare la media storica. Per farlo, abbiamo bisogno di un GROUP BY, che può interrompere la relazione uno-a-uno necessaria per un JOIN. Un altro punto da notare è che la metrica "mele per metro quadro" si ottiene con la seguente espressione:

	production_in_kg / area

Abbiamo utilizzato la metrica "mele per metro quadro" perché abbiamo bisogno di un modo per confrontare la produttività delle diverse aziende agricole e classificarle. La "produzione_in_kg" totale di un'azienda agricola non è un valore comparabile, perché è probabile, ad esempio, che l'azienda più grande abbia una migliore production_in_kg. Dividiamo quindi la "produzione_in_kg" per la superficie di ogni azienda agricola per standardizzare i valori e creare una metrica comparabile.

Scopriamo che la produzione media storica per metro quadrato è di 0,42. Quindi il risultato della query precedente è:

Esempio: quando JOIN e subquery sono ugualmente efficienti

Come ultima domanda sui dati, cerchiamo di ottenere gli anni in cui l'azienda ha prodotto meno mele rispetto all'anno in corso. Possiamo scrivere questa query utilizzando due approcci diversi.

Soluzione con una sottoquery:

SELECT year, sum(production_in_kg) 
FROM production_history PH
GROUP BY year
HAVING sum(production_in_kg) < (
  SELECT sum(production_in_kg)
  FROM current_year_production
)

Soluzione utilizzando una clausola JOIN:

SELECT year, sum(PH.production_in_kg) 
FROM production_history PH 
JOIN current_year_production CYP
  ON PH.farm_id = CYP.farm_id
GROUP BY year
HAVING sum(PH.production_in_kg) < sum(CYP.production_in_kg)

Si può notare che entrambe le query sono molto simili; la differenza principale sta nella clausola JOIN e nella sottoquery. In questo caso, entrambe le query sono ugualmente efficienti: la sottoquery viene eseguita una sola volta nella clausola HAVING, quindi non ci sono problemi di prestazioni.

Provate voi stessi!

Per concludere, è importante notare che le subquery e le JOIN sono entrambe risorse molto importanti per uno sviluppatore SQL. Abbiamo visto esempi in cui è possibile sostituire una subquery con una JOIN ed esempi in cui non è possibile effettuare tale sostituzione. Inoltre, a volte le subquery e le JOIN sono ugualmente efficienti.

Ma come si fa a sapere quando usare una subquery e quando una JOIN? In tutta onestà, l'unico modo per sviluppare l'intuizione è risolvere regolarmente esercizi di SQL. Se state cercando di affinare le vostre competenze in SQL, il nostro SQL Practice Set offre 88 problemi pratici per un ripasso completo.

Restate sintonizzati per altri articoli!