Subquery vs. JOIN

Una delle sfide nella scrittura di query SQL è scegliere se utilizzare una subquery o una JOIN. Ci sono molte situazioni in cui una JOIN è la soluzione migliore, mentre ce ne sono altre in cui è meglio una subquery. Esaminiamo questo argomento in dettaglio.

Le sottoquery sono utilizzate nelle query SQL complesse. Di solito, c'è una query esterna principale e una o più subquery annidate all'interno della query esterna.

Le sottoquery possono essere semplici o correlate. Le subquery semplici non si basano sulle colonne della query esterna, mentre le subquery correlate fanno riferimento ai dati della query esterna.

È possibile conoscere le subquery nella sezione Subquery del corso interattivo "SQL Basics" o esercitarsi a scrivere subquery nella sezione Subquery del corso "SQL Practice Set". Oppure è sufficiente leggere l'articolo "Subquery SQL" di Maria Alcaraz.

La clausola JOIN non contiene query aggiuntive. Collega due o più tabelle e seleziona i dati da esse in un unico insieme di risultati. È molto usata per unire tabelle con chiavi primarie e chiavi esterne. È possibile esercitarsi con SQL JOINs nel nostro corso interattivo SQL JOINs corso interattivo. Contiene oltre 90 esercizi per rivedere ed esercitare diversi tipi di JOIN. Per saperne di più su JOINè possibile leggere l'articolo "Come esercitarsi con SQL JOINs" di Emil Drkušić.

Le subquery e JOINpossono essere utilizzate entrambe in una query complessa per selezionare dati da più tabelle, ma lo fanno in modi diversi. A volte è possibile scegliere l'una o l'altra, ma ci sono casi in cui la subquery è l'unica vera opzione. Di seguito descriveremo i vari scenari.

Consideriamo due semplici tabelle, product e saleche utilizzeremo nei nostri esempi.

Ecco la product tabella.

Questa tabella contiene le seguenti colonne:

• id: l'identificatore del prodotto.
• name: il nome del prodotto.
• cost: il costo del prodotto.
• year: l'anno di fabbricazione del prodotto.
• city: la città in cui è stato realizzato il prodotto.

E l'altra tabella, sale:

che presenta le seguenti colonne:

• id: l'identificativo della vendita.
• product_id: l'identificativo del prodotto venduto.
• price: il prezzo di vendita.
• year: l'anno in cui il prodotto è stato venduto.
• city: la città in cui il prodotto è stato venduto.

Utilizzeremo queste due tabelle per scrivere query complesse con subquery e JOIN.

Quando riscrivere le subquery con le JOIN

I principianti di SQL spesso usano le subquery quando gli stessi risultati possono essere ottenuti con JOINs. Mentre le subquery possono essere più facili da capire e da usare per molti utenti di SQL, JOINs è spesso più efficiente. JOINs è anche più facile da leggere quando le query diventano più complesse. Pertanto, ci concentreremo innanzitutto su quando è possibile sostituire una subquery con una JOIN per migliorare l'efficienza e la leggibilità.

Subquery scalare

Il primo caso è quello delle subquery scalari. Una subquery scalare restituisce un singolo valore (una colonna e una riga) che verrà utilizzato dalla query esterna. Ecco un esempio.

Supponiamo di aver bisogno dei nomi e dei costi dei prodotti venduti per 2.000 dollari.

Vediamo il codice con una sottoquery:

SELECT name, cost 
FROM product
WHERE id=(SELECT product_id 
  FROM sale 
    WHERE price=2000 
    AND product_id=product.id
  );

e il risultato:

La query esterna seleziona i nomi (name) e il costo (cost) dei prodotti. Poiché non vogliamo tutti i prodotti, utilizziamo la clausola WHERE per filtrare le righe in base agli ID dei prodotti restituiti dalla sottoquery.

Ora analizziamo la subquery. La tabella sale contiene i record di vendita dei prodotti. La sottoquery filtra innanzitutto i record solo per quelli con un prezzo di vendita pari a 2.000 dollari (price=2000). Quindi utilizza gli ID dei prodotti (product_id) nelle vendite selezionate per identificare i record della tabella ( ). product tabella (product_id=product.id). Si tratta di una sottoquery correlata, poiché la seconda condizione della sottoquery fa riferimento a una colonna della query esterna. Solo due prodotti sono stati venduti a 2.000 dollari: la poltrona e il tavolo TV.

Questa query non è molto efficiente. Come possiamo modificarla?

Possiamo costruire una struttura JOIN e ottenere lo stesso risultato. Guardate la query con una struttura JOIN:

SELECT p.name, p.cost 
FROM product p 
JOIN sale s ON p.id=s.product_id
WHERE s.price=2000;

In questa query, si collegano le due tabelle product e sale con l'operatore JOIN. Nella condizione JOIN, i record della tabella product sono collegati ai record della tabella sale tabella attraverso gli ID prodotto. Alla fine, le righe vengono filtrate da una clausola WHERE per selezionare il record quando il prezzo di vendita del prodotto è pari a 2.000 dollari.

Subquery all'interno della clausola IN

Un'altra subquery che può essere facilmente sostituita da una JOIN è quella utilizzata in un operatore IN. In questo caso, la subquery restituisce alla query esterna un elenco di valori.

Supponiamo di voler ottenere i nomi e i costi dei prodotti venduti nel nostro esempio.

SELECT name, cost 
FROM product 
WHERE id IN (SELECT product_id FROM sale);

La query esterna seleziona i nomi e i costi dei prodotti, quindi filtra i record i cui ID prodotto sono presenti nell'elenco restituito dalla sottoquery. La sottoquery seleziona gli ID dei prodotti dalla tabella sale (SELECT product_id FROM sale), quindi solo i prodotti venduti vengono restituiti da questa query nell'insieme finale dei risultati, come in questo caso:

Ci sono più prodotti nella tabella product ma solo quattro sono stati venduti.

La query sottostante restituisce lo stesso risultato utilizzando l'operatore JOIN:

SELECT DISTINCT p.name, p.cost 
FROM product p 
JOIN sale s ON s.product_id=p.id;

Si tratta di una query molto semplice. Collega le due tabelle per ID prodotto e seleziona i nomi e i costi di questi prodotti. Si tratta di INNER JOIN, quindi se un prodotto non ha il suo ID nella tabella, non verrà restituito. sale tabella, non verrà restituito.

Si noti che viene utilizzata anche la parola chiave DISTINCT per rimuovere i record duplicati. Questo è spesso necessario se si trasformano le subquery con una IN o una NOT IN in JOIN.

Volete saperne di più sulle subquery SQL con l'operatore IN? Guardate un episodio della nostra serie We Learn SQL su Youtube. Ricordatevi di iscrivervi al nostro canale.

Subquery con la clausola NOT IN

Questa è una situazione analoga a quella precedente, ma in questo caso la subquery è utilizzata in un operatore NOT IN. Vogliamo selezionare i nomi e i costi dei prodotti che non sono stati venduti.

Di seguito è riportato un esempio con una subquery all'interno dell'operatore NOT IN:

SELECT name, cost 
FROM product 
WHERE id NOT IN (SELECT product_id FROM sale);

I risultati:

La sottoquery restituisce gli ID dei prodotti dalla tabella sale (i prodotti venduti) e li confronta con gli ID dei prodotti della query esterna. Se un record della query esterna non trova il suo ID prodotto nell'elenco restituito dalla subquery, il record viene restituito.

Come si può riscrivere questa sottoquery con un operatore JOIN? Si può fare in questo modo:

SELECT DISTINCT p.name, p.cost
FROM product p 
LEFT JOIN sale s ON s.product_id=p.id 
WHERE s.product_id IS NULL;

Questa query collega le due tabelle product e sale per gli ID prodotto. È necessario utilizzare anche la parola chiave DISTINCT, come abbiamo fatto quando abbiamo trasformato la precedente sottoquery con una IN in una JOIN.

Notate che nel riscrivere la subquery nell'operatore NOT IN, abbiamo usato un LEFT JOIN e un WHERE. In questo modo, si inizia con tutti i prodotti, compresi quelli non venduti, quindi si selezionano solo i record che sono NULL nella colonna product_id. NULL indica che il prodotto non è stato venduto.

Subquery correlate in EXISTS e in NOT EXISTS

Anche le subquery in una EXISTS o in una NOT EXISTS sono facili da riscrivere con JOIN.

La query seguente utilizza una subquery per ottenere i dettagli sui prodotti che non sono stati venduti nel 2020.

SELECT name, cost, city
FROM product  
WHERE NOT EXISTS ( SELECT id  
  FROM sale WHERE year=2020 AND product_id=product.id );

Il risultato:

Per ogni prodotto della query esterna, la sottoquery seleziona i record il cui anno di vendita è il 2020 (year=2020). Se non ci sono record per un determinato prodotto nella subquery, la clausola NOT EXISTS restituisce true.

L'insieme dei risultati contiene i prodotti con anno di vendita diverso dal 2020 e i prodotti senza alcun record nella tabella. sale tabella. È possibile riscrivere la stessa query utilizzando una JOIN:

SELECT p.name, p.cost, p.city FROM product p 
LEFT JOIN  sale s ON s.product_id=p.id 
WHERE s.year!=2020 OR s.year IS NULL;

In questo caso, si collega la tabella product con la tabella sale attraverso l'operatore LEFT JOIN. Questo ci permette di includere i prodotti che non sono mai stati venduti nell'insieme dei risultati. La clausola WHERE filtra i record selezionando i prodotti che non sono presenti nella tabella ( ) e i prodotti che non sono mai stati venduti. sale (s.year IS NULL) e i prodotti con anno di vendita diverso dal 2020 (s.year!=2020).

Quando non è possibile sostituire una sottoquery con una JOIN

JOINLe JOIN possono essere efficienti, ma ci sono situazioni che richiedono una subquery e non una JOIN. Di seguito sono riportate alcune di queste situazioni.

Subquery in FROM con GROUP BY

La prima di queste situazioni è una subquery in una clausola FROM che utilizza una GROUP BY per calcolare i valori aggregati.

Vediamo il seguente esempio:

SELECT city, sum_price  
 FROM  
(
  SELECT city, SUM(price) AS sum_price FROM sale 
  GROUP BY city 
) AS s
WHERE sum_price < 2100;

e il risultato:

La subquery seleziona le città e calcola la somma dei prezzi di vendita per città. La somma di tutti i prezzi di vendita in ciascuna città della tabella sale tabella è calcolata dalla funzione aggregata SUM(). Utilizzando i risultati della sottoquery, la query esterna seleziona solo le città il cui prezzo di vendita totale è inferiore a 2.100 dollari (WHERE sum_price < 2100). Si dovrebbe ricordare dalle lezioni precedenti come utilizzare gli alias per le sottoquery e come selezionare un valore aggregato in una query esterna.

Subquery che restituisce un valore aggregato in una clausola WHERE

Un'altra situazione in cui non è possibile riscrivere una struttura di subquery con una JOIN è il confronto di un valore aggregato in una clausola WHERE. Guardate questo esempio:

SELECT name FROM product
WHERE cost<(SELECT AVG(price) from sale);

Il risultato:

Questa query recupera i nomi dei prodotti il cui costo è inferiore al prezzo medio di vendita. Il prezzo medio di vendita viene calcolato con l'aiuto della funzione aggregata AVG() e viene restituito dalla sottoquery. Il costo di ciascun prodotto viene confrontato con questo valore nella query esterna.

Subquery in una clausola ALL

Un'altra situazione è quella di una sottoquery con una clausola ALL.

SELECT name FROM product
WHERE cost > ALL(SELECT price from sale);

La sottoquery restituisce tutti i prezzi di vendita della tabella. sale nella tabella. La query esterna restituisce il nome del prodotto con il prezzo di vendita più alto del costo.

Il risultato:

Quando utilizzare una subquery rispetto a una JOIN

Abbiamo esaminato alcuni usi comuni delle subquery e le situazioni in cui alcune subquery possono essere riscritte con JOIN. JOIN è più efficiente nella maggior parte dei casi, ma ci sono casi in cui non è possibile utilizzare costrutti diversi da una subquery. Mentre le subquery possono essere più leggibili per i principianti, JOINs è più leggibile per i codificatori SQL esperti, man mano che le query diventano più complesse. È buona norma evitare livelli multipli di subquery annidate, poiché non sono facilmente leggibili e non hanno buone prestazioni. In generale, se possibile, è meglio scrivere una query con JOINs piuttosto che con subquery, soprattutto se le subquery sono correlate.

Se siete interessati a saperne di più o se volete esercitarvi, consultate le sezioni dedicate alle subquery nel corso "SQL Basics" o nel corso "SQL Practice Set".