Come usare le subquery nelle dichiarazioni INSERT, UPDATE e DELETE

Sapevate che le subquery possono essere utilizzate anche nelle istruzioni UPDATE, INSERT e DELETE? Le subquery in SQL sono come blocchi di costruzione che possono essere utilizzati in una serie di istruzioni, non solo in SELECT. Se le subquery sono utili in SELECT, leggete questo articolo per scoprire quanto possono essere utili in altre istruzioni. In poco tempo sarete in grado di creare query SQL molto più complesse e potenti!

Se volete rinfrescare le vostre conoscenze sulle subquery, vi consiglio il corso interattivo SQL Practice Set interattivo. Contiene oltre 80 esercizi sulle subquery e su altre costruzioni impegnative di SELECT.

Punto di partenza: Un rapido ripasso delle subquery

Iniziamo con un breve promemoria su cosa sia una subquery. Poiché le subquery sono usate più spesso nelle istruzioni SELECT, esamineremo un esempio di semplice subquery in un'istruzione SELECT.

Una subquery può essere definita come una query all'interno di un'altra query. Sebbene le subquery siano usate più spesso nella clausola WHERE delle istruzioni SELECT, possono essere usate in molte altre clausole, tra cui WHERE, FROM e HAVING.

Vediamo il database che useremo come esempio. Immaginiamo di essere il proprietario di un'enoteca e di avere un semplice database con 3 tabelle per gestire le attività del negozio.

La prima tabella è wineche contiene i prodotti venduti, con il nome, il prezzo, il numero di bottiglie in magazzino e così via per ogni vino. La seconda è orderche memorizza gli ordini ricevuti dai nostri clienti, compreso il nome del vino ordinato e la quantità ordinata, oltre ad altre informazioni.

wine

order

Supponiamo di voler ottenere un elenco di vini per i quali non abbiamo mai ricevuto un ordine. La query avrà il seguente aspetto:

SELECT  name,
FROM    wine
WHERE   name NOT IN ( SELECT wine_name FROM order )

La sottoquery restituisce i nomi di tutti i vini per i quali abbiamo ricevuto ordini. Poi la query esterna, utilizzando l'operatore NOT IN, ottiene i nomi dei vini non inclusi in nessun ordine. L'SQL è tanto semplice quanto potente! Se volete ripassare alcuni concetti di subquery, vi suggerisco il corso SQL Basics dove troverete una sezione completa sulle subquery.

Prima tappa: usare le sottoquery nelle dichiarazioni INSERT

Utilizziamo ora una subquery in un'istruzione INSERT. Si tratta di un'operazione molto comune; l'idea è quella di inserire in una tabella l'insieme completo dei risultati di una subquery o di un'istruzione SELECT. A titolo di esempio, immaginiamo di voler creare le fatture per tutti gli ordini di vino ricevuti nel corso della giornata. Di seguito è riportata una vista parziale della nostra tabella invoice tabella:

Facciamo finta che la data odierna sia il 28 giugno 2020 e che vogliamo inserire i record delle fatture associate agli ordini di oggi. Possiamo utilizzare la seguente SELECT per generare i dati delle fatture:

SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Il risultato di questa query è riportato di seguito:

Questo è esattamente ciò che vogliamo inserire nella tabella invoice.

Se aggiungiamo semplicemente una clausola INSERT prima della query, possiamo inserire il risultato della query nella tabella winecome si può vedere nell'esempio seguente:

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Si noti quanto segue:

• È necessario nominare esplicitamente le colonne della tabella invoice in cui stiamo inserendo.
• Le colonne dell'elenco SELECT devono essere nello stesso ordine delle colonne della tabella.
• Abbiamo omesso la colonna invoice_id per consentire al database di scegliere il valore successivo utilizzando un generatore di sequenze predefinito.

Dopo aver eseguito la clausola INSERT, la tabella invoice conterrà i nuovi record delle fatture per gli ordini di oggi. Lo si può vedere qui sotto, con i nuovi record in rosso:

Supponiamo che in un determinato giorno abbiamo accidentalmente eseguito due volte il programma INSERT e che i nostri clienti abbiano ricevuto due fatture per ogni ordine. Non vogliamo ripetere lo stesso errore in futuro! Per evitare che ciò accada, aggiungiamo una sottoquery all'istruzione INSERT per verificare se esiste già una fattura con lo stesso nome order_id. Di seguito è riportata la nuova versione della query INSERT. La sottoquery aggiunta alla fine identifica le fatture già esistenti e la query esterna le scarta utilizzando l'operatore NOT IN.

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT order.date, 
       order.client_id, 
       o.quantity * w.price as amount, 
       o.quantity || ’ bottles of ‘ || o.wine_name,
       o.order_id
FROM   order o 
JOIN   wine ON wine.name = order.wine_name
WHERE  order.date = ‘2020-06-28’
  AND  o.order_id NOT IN (SELECT order_id 
                           FROM invoice i 
                           WHERE i.order_id=o.order_id )

Se volete migliorare le vostre competenze nell'uso delle istruzioni INSERT, UPDATE e DELETE, vi suggerisco il corso How to INSERT, UPDATE, and DELETE Data in SQL dove si possono vedere diversi esempi di queste istruzioni.

Seconda tappa: le sottoquery nelle dichiarazioni UPDATE

Come SELECT, l'istruzione UPDATE può avere una subquery in diversi punti o clausole. In un'istruzione UPDATE, le due clausole in cui le subquery sono più comunemente utilizzate sono SET e WHERE.

La clausola SET è quella in cui si definisce il nuovo valore per la colonna che viene modificata dall'istruzione UPDATE. Per ottenere questo nuovo valore si può usare una subquery, che può essere selezionata da qualsiasi tabella o da qualsiasi subquery valida, purché restituisca un solo record con una sola colonna per ogni record aggiornato. Il tipo di dati della colonna restituita dalla subquery deve essere dello stesso tipo di quello della colonna da modificare.

Creiamo un UPDATE per mantenere aggiornato il nostro stock di bottiglie di vino. Eseguiremo un UPDATE alla fine della giornata per riflettere ciò che abbiamo venduto oggi. Il codice sarà simile a questo:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Se non usassimo la clausola WHERE nella UPDATE, finiremmo per modificare tutti i record della tabella wine, compresi i record dei vini che non abbiamo venduto oggi. La subquery restituisce un NULL per tutti i vini non venduti oggi e noi erroneamente SET la colonna stock a NULL, poiché il risultato dell'espressione "stock - NULL" è NULL. Dobbiamo risolvere questo problema.

Ci sono due approcci per risolvere il problema. Il primo consiste nel modificare l'espressione SUM(quantity) in modo che restituisca uno zero anziché un NULL. A tale scopo, è sufficiente utilizzare la funzione COALESCE, come in questo caso:

UPDATE wine w
SET stock = stock - (
                     SELECT coalesce(SUM (quantity), 0)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Il secondo approccio consiste nell'aggiungere una sottoquery nella clausola WHERE per modificare solo i vini che sono stati ordinati oggi e mantenere l'espressione SUM(quantity) così com'è. La seguente query mostra questo approccio:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )
WHERE w.name IN (SELECT order.wine_name FROM order WHERE date = CURRENT_DATE)

Questa UPDATE non è ideale: la clausola SET utilizza una sottoquery correlata. Una subquery correlata è una subquery che il database deve eseguire più volte, una per ogni riga modificata nella tabella. Nel nostro esempio, la query ottiene l'indirizzo SUM(quantity) per ogni vino venduto oggi. Sebbene le subquery correlate possano essere potenti, è meglio evitarle quando è possibile, per una questione di best practice. In questo caso, possiamo evitare la sottoquery correlata utilizzando una clausola FROM nella UPDATE, come si può vedere di seguito:

UPDATE wine w
SET stock = stock - subquery.total_in_orders
FROM ( SELECT wine_name, sum( order_quantity ) AS total_in_orders  
       FROM   order 
       WHERE  date = TODAY 
       GROUP BY wine_name 
     ) subquery
WHERE w.name = subquery.wine_name 

Come risorsa supplementare, suggerisco l'articolo Beginners Guide to the SQL Subquery, dove è possibile imparare l'ABC delle subquery attraverso esempi di varia complessità.

Terza tappa: le subquery nelle dichiarazioni DELETE

Con l'istruzione DELETE, le subquery possono essere utilizzate solo all'interno di una clausola WHERE. Supponiamo di voler eliminare i record dei vini per i quali non abbiamo ricevuto alcun ordine negli ultimi 6 mesi. Possiamo creare una sottoquery che restituisca i vini venduti negli ultimi 6 mesi, quindi identificare i record che vogliamo rimuovere nella tabella wine utilizzando l'operatore NOT IN. Vediamo come funziona SQL:

DELETE FROM wine w
WHERE name NOT IN ( SELECT wine_name 
                    FROM   order 
                    WHERE  date >= CURRENT_DATE - interval ‘6 Months’ 
                  )

Supponiamo di voler eliminare i vini per i quali gli ordini totali negli ultimi 6 mesi sono stati inferiori a 10 unità. L'istruzione DELETE avrà questo aspetto:

DELETE FROM wine w
WHERE 10 > ( SELECT SUM(quantity) 
                    FROM   order o 
                    WHERE  o.wine_name = w.name 
                      AND  date >= CURRENT_DATE - interval ‘6 Months’
                  )

In questo caso, la sottoquery restituisce la quantità di bottiglie ordinate negli ultimi 6 mesi per ciascun vino. Confrontando questa quantità con 10, possiamo determinare se un determinato vino deve essere eliminato.

Arrivare alla fine del tour delle subquery

Le subquery sono come dei blocchi di costruzione in SQL. Abbiamo visto che possono essere utilizzate in diversi modi, ad esempio nelle istruzioni SELECT o in qualsiasi istruzione di modifica come INSERT, UPDATE e DELETE. Abbiamo visto come utilizzare una subquery in diverse clausole e che esistono diversi modi di utilizzare le subquery nelle istruzioni INSERT, UPDATE e DELETE. Se volete saperne di più sulle subquery, vi suggerisco l'articolo Subquery vs. CTE: A SQL Primer, in cui viene spiegato un tipo di subquery chiamato CTE con molti esempi e dettagli. Infine, ci sono due corsi che contengono molte informazioni utili sulle subquery con numerosi esempi: SQL Basics dove si trova una sezione completa sulle subquery e How to INSERT, UPDATE, and DELETE Data in SQL che è un corso più avanzato. Andiamo a migliorare le vostre competenze in SQL!