MySQL Sottoquery con esempi

Che cos'è una subquery in SQL?

A sottoquery Una query SELECT è una query SELECT contenuta all'interno di un'altra query. La query SELECT interna viene solitamente utilizzata per determinare i risultati della query SELECT esterna, quindi il database valuta prima l'istruzione interna e poi ne trasmette l'output verso l'alto.

La query interna è chiamata query interna o query nidificata, e l'istruzione che la contiene è chiamata interrogazione esternaAnalizziamo la sintassi della sottoquery.

Il diagramma sopra mostra la struttura generale dell'istruzione: la clausola SELECT esterna specifica le colonne che si desidera visualizzare, mentre la clausola SELECT interna tra parentesi quadre specifica il valore o l'elenco di valori con cui la clausola WHERE effettua il confronto.

Perché utilizzare una sottoquery?

Prima di esaminare i diversi tipi, è utile sapere quando una subquery si merita di essere inserita in un'istruzione.

Una subquery risponde a una domanda il cui valore di filtro non è noto in anticipo. Deve essere calcolato a partire dai dati stessi. Un reclamo comune dei clienti della videoteca MyFlix riguarda il numero ridotto di titoli, e la direzione desidera acquistare film per la categoria con il minor numero di titoli. Nessuno sa quale sia questa categoria finché non viene interrogata il database, quindi il valore deve essere calcolato prima e poi utilizzato come filtro.

Le sottoquery sono atractive per tre ragioni pratiche:

• leggibilità: Ciascuna parte della logica è racchiusa in un blocco separato tra parentesi quadre, in modo che l'affermazione risulti come una sequenza di piccole domande piuttosto che come un'unica espressione complessa.
• Isolamento: Una query interna può essere eseguita autonomamente per confermare che restituisca il valore atteso, il che semplifica notevolmente le fasi di test e debug.
• Flessibilità: Lo stesso schema funziona nelle clausole WHERE, HAVING, SELECT e FROM, nonché all'interno delle istruzioni INSERT, UPDATE e DELETE.

Il compromesso riguarda la velocità, che verrà esaminata nel confronto JOIN più avanti in questo articolo.

Tipi di sottoquery in MySQL

MySQL Il sistema supporta tre tipi di subquery, e il tipo viene determinato dalla struttura del risultato restituito dalla query interna. Ciascun tipo è spiegato di seguito con un esempio pratico sul database myflixdb.

1) Sottoquery scalare

A sottoquery scalare Restituisce esattamente una riga e una colonna, ovvero un singolo valore. Poiché il risultato è un singolo valore, può essere utilizzato ovunque sia consentito un valore letterale. Riprendendo il problema di MyFlix menzionato in precedenza, è possibile utilizzare una query come questa:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Fornisce un risultato:

Vediamo come funziona questa query.

Come mostra il diagramma di esecuzione, MySQL prime corse SELECT MIN(category_id) FROM movies, riceve un valore e solo allora esegue la query esterna con quel valore. Poiché viene restituito un singolo valore, gli operatori consentiti sono quelli standard di confronto: =, <> (o !=), >, >=, <e <=.

Suggerimento: Se una sottoquery è posizionata dopo = restituisce più di una riga, MySQL genera l'errore 1242, La sottoquery restituisce più di una riga. Passare l'operatore a IN, oppure stringere la clausola WHERE interna.

2) Sottoquery di riga

A sottoquery di riga Restituisce anche una singola riga, ma tale riga può contenere più di una colonna. La query esterna, pertanto, confronta una riga di valori con un costruttore di riga anziché con un singolo valore.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Gli operatori consentiti sono gli stessi operatori di confronto elencati sopra, applicati all'intera riga contemporaneamente.

3) Sottoquery della tabella

A sottoquery della tabella restituisce più righe e spesso più colonne, quindi la query esterna deve utilizzare un operatore di insieme come IN, NOT IN, ANY, ALL, o EXISTS.

Supponiamo di voler ottenere i nomi e i numeri di telefono degli utenti che hanno noleggiato un film e non lo hanno ancora restituito, in modo da poterli contattare per un promemoria. È possibile utilizzare una query come questa:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Vediamo come funziona questa query.

In questo caso, la query interna restituisce più di un risultato, quindi l'elenco dei numeri di iscrizione viene passato al IN operatore e ogni membro corrispondente viene restituito.

Sottoquery annidate su più livelli di profondità

Finora avete visto due livelli. Una sottoquery può contenere anche un'altra sottoquery, il che produce un'istruzione annidata tripla.

Supponiamo che la direzione voglia premiare il membro che paga di più. Possiamo eseguire una query come questa:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

La query più interna individua il pagamento più elevato, la query intermedia converte tale importo in un numero di iscrizione e la query più esterna converte il numero di iscrizione in un nome. La query sopra riportata produce il seguente risultato:

Come utilizzare le subquery con INSERT, UPDATE e DELETE

Le subquery non si limitano alle istruzioni SELECT. Lo stesso schema tra parentesi funziona anche all'interno delle istruzioni di modifica dei dati, consentendo di modificare un intero set di righe in un'unica operazione senza creare una tabella temporanea.

INSERT con una sottoquery. Una subquery può fornire le righe da inserire, copiando i dati da una tabella all'altra. L'elenco delle colonne della SELECT deve corrispondere all'elenco delle colonne dell'INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

AGGIORNA con una sottoquery. Qui la query interna decide quali righe vengono elaborate. L'esempio seguente contrassegna tutti i membri il cui affitto è ancora in sospeso.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

ELIMINA con una sottoquery. Lo stesso principio si applica alla rimozione delle righe che soddisfano una condizione presente in una seconda tabella.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Attenzione: MySQL non consente a un'istruzione di modificare una tabella e selezionare dalla stessa tabella all'interno di una sottoquery nella clausola FROM. Se viene visualizzato l'errore 1093, racchiudere la query interna in una tabella derivata, ad esempio SELECT * FROM (SELECT ...) AS t, Cosicché MySQL materializza il risultato prima che la modifica venga applicata. È anche saggio eseguire prima la SELECT interna da sola e confermare il conteggio delle righe prima di eseguire un AGGIORNAMENTO DELETE in produzione.

Sottoquery vs. join

Sia una subquery che una JOIN possono combinare informazioni provenienti da più tabelle, quindi la domanda spontanea è quale delle due sia la più adatta.

Rispetto alle join, le subquery sono semplici da usare e facili da leggere. Non sono complicate come Entra a far partee quindi vengono spesso utilizzati da Principianti SQL.

Tuttavia, le subquery presentano problemi di prestazioni. L'utilizzo di un join al posto di una subquery può talvolta offrire un incremento delle prestazioni fino a 500 volte superiore, perché l'ottimizzatore è in grado di risolvere un join in un singolo passaggio anziché valutare ripetutamente l'istruzione interna.

Data la possibilità di scelta, si consiglia di utilizzare un JOIN su una sottoquery. Le subquery dovrebbero essere utilizzate solo come soluzione di ripiego quando non è possibile utilizzare un'operazione JOIN per ottenere il risultato sopra descritto.

Le subquery sono anche facili da scomporre in singoli componenti logici, il che è molto utile quando analisi e il debug delle query.

DOMANDE FREQUENTI