L'operatore SQL EXISTS

L'uso della clausola SQL EXISTS ci permette di creare query complesse in modo semplice. In questo articolo scopriremo i pro e i contro dell'operatore EXISTS in questo articolo.

In SQL, l'operatore EXISTS ci aiuta a creare condizioni logiche nelle nostre query. In sostanza, controlla se ci sono righe in una sottoquery. Vi mostreremo la sintassi di EXISTS, vi forniremo alcuni esempi di utilizzo e vi daremo diversi esercizi su cui esercitarvi.

Se siete alle prime armi con SQL o avete bisogno di un ripasso di SQL, vi consiglio il corso SQL per principianti. Include argomenti di SQL che vanno dalle SELECT di base ad argomenti complessi come join, aggregazioni e subquery. È un ottimo modo per sviluppare, rinfrescare o ampliare le proprie competenze in SQL.

Sintassi dell'operatore EXISTS

Probabilmente conoscete gli operatori SQL come =, >, < e LIKE. Tutti questi operatori possono essere utilizzati per creare condizioni logiche che restituiscono TRUE o FALSE. Esempi comuni di condizioni sono:

WHERE Employee_id = 10345
WHERE Price < 1000
WHERE Name LIKE ‘John%’

Gli operatori =, < e LIKE confrontano due elementi o operandi. Per questo motivo sono chiamati operatori binari.

				WHERE EXISTS ( subquery )

Vediamo ora l'intera query SQL per determinare dove collocare la condizione EXISTS:

SELECT columns
FROM table1
WHERE EXISTS (SELECT columns FROM table2);

L'operatore EXISTS viene utilizzato per creare condizioni booleane per verificare se una sottoquery restituisce una o più righe o un insieme vuoto. Quando la sottoquery restituisce almeno una riga, EXISTS restituisce TRUE. Non importa quante righe vengono restituite o quante colonne sono presenti nell'elenco della subquery SELECT. Solo quando la sottoquery restituisce 0 righe, EXISTS restituisce FALSE. È importante notare che nessuna delle righe restituite dalla subquery viene mostrata nel risultato finale.

Esempio di database: Barche e auto di lusso

Esaminiamo alcune query di esempio basate su un database di una società che vende auto e barche di lusso. L'azienda tiene traccia delle auto e delle barche nella tabella prodotto, che ha le colonne product_id, product_name, product_price, product_type.

Il database ha anche una tabella Client con le colonne client_id, client_name e country.

Infine, c'è la tabella sale con le colonne product_id, client_id, sale_date. (Per semplicità, ometto l'idea di avere una tabella per gli ordini d'acquisto; in questo tipo di azienda, un cliente normalmente acquista un solo prodotto alla volta).

Esempi dell'operatore SQL EXISTS

Esempio 1: Trovare i prodotti venduti

Per il primo esempio di query, supponiamo che il responsabile marketing voglia sapere quali auto e barche sono state vendute tra il 1° aprile e il 15 aprile del 2024. La query che segue ha questo scopo:

SELECT product_name 
FROM product p1
WHERE EXISTS ( SELECT * FROM sale s 
               WHERE  s.product_id = p1.product_id 
               AND    s.sale_date >= ‘2024-04-01’ 
               AND    s.sale_date <= ‘2024-04-15’
             )

Risultati:

Esistono altri modi per scrivere questa query senza usare l'operatore EXISTS. Tuttavia, per spiegare il funzionamento dell'operatore EXISTS, questo è un buon esempio di base.

L'operatore SELECT è molto semplice: va alla tabella product per ottenere la colonna product_name. Tuttavia, non vogliamo tutti i product_names nella tabella. Vogliamo solo i prodotti che restituiscono TRUE per la seguente condizione:

WHERE EXISTS ( SELECT * FROM sale s 
               WHERE  s.product_id = p1.product_id 
               AND     s.sale_date >= ‘2024-04-01’ 
               AND     s.sale_date <= ‘2024-04-15’
             )

Se analizziamo la condizione, la sottoquery restituisce tutti i record di vendita per un determinato prodotto (p1.product_id) e per un periodo di tempo specifico (dal 2024-04-01 al 2024-04-15). Se la sottoquery restituisce dei record, allora EXISTS restituirà TRUE. Se la subquery non restituisce alcun record, EXISTS restituirà FALSE. Si noti che la sottoquery viene eseguita più volte, una per ogni riga letta dalla query esterna.

Questo tipo di subquery, che vengono eseguite una volta per ogni riga elaborata nella query principale, sono chiamate "subquery correlate" e hanno le loro particolarità. Se volete approfondire questo argomento, vi suggerisco l'articolo Correlated Subquery in SQL: A Beginner's Guide.

Esempio 2: Utilizzo dell'operatore NOT EXISTS

Supponiamo che il manager voglia i nomi dei veicoli che non sono stati venduti nello stesso periodo. Questo tipo di query - in cui si cercano record che non esistono nel database - è eccellente per l'operatore NOT EXISTS. Dopo tutto, stiamo cercando elementi che non esistono nel database. La query è la seguente:

SELECT * FROM product p1
WHERE p1.product_type = ‘Car’
AND   NOT EXISTS ( SELECT * FROM sale s
                   WHERE s.product_id = p1.product_id
                   AND   s.sale_date >= ‘2024-04-01’ 
                   AND   s.sale_date <= ‘2024-04-15’
                 )

Risultati:

Una volta capito come funziona l'operatore EXISTS in SQL, capire NOT EXISTS è molto semplice: è il contrario. Se EXISTS restituisce TRUE, allora NOT EXISTS restituisce FALSE e viceversa.

In questo esempio, la query principale ha una clausola WHERE con due condizioni. La prima condizione è la richiesta di prodotti del tipo "veicolo". La seconda condizione utilizza NOT EXISTS per mantenere solo i prodotti che non hanno avuto vendite nel periodo compreso tra il 2024-04-01 e il 2024-04-15. In altre parole, si cercano i prodotti del tipo "veicolo". In altre parole, cerchiamo i prodotti per i quali la sottoquery restituisce un insieme di risultati vuoto, cioè per i quali non ci sono vendite in quel periodo.

In alcuni casi, è possibile risolvere questo tipo di query utilizzando NOT IN con una sottoquery. In questa particolare query, possiamo inserire la clausola WHERE:

WHERE p1.product_type = ‘Car’
AND   p1.product_id NOT IN (SELECT s.product_id 
                            FROM  sale s
               WHERE s.sale_date >= ‘2024-04-01’ 
                            AND   s.sale_date <= ‘2024-04-15’
                           )

Tuttavia, c'è una differenza nel modo in cui la query viene eseguita internamente al database. La condizione della sottoquery NOT IN viene eseguita una volta, mentre la condizione della sottoquery NOT EXISTS viene eseguita una volta per ogni riga. Esatto, NOT EXISTS è una subquery correlata. Per ulteriori informazioni su questi operatori, leggete gli articoli 5 esempi di subquery SQL e SQL IN Operator.

Esempio 3: Uso di EXISTS con NOT EXISTS

Successivamente, abbiamo bisogno di un elenco dei clienti che non hanno acquistato un'imbarcazione durante l'estate 2023, ma che hanno acquistato un'imbarcazione durante l'inverno precedente (cioè da dicembre 2022 a marzo 2023). La query per ottenere questo report è la seguente:

SELECT * 
FROM   client c1
WHERE  EXISTS ( SELECT * FROM sale s1 
                JOIN  product p1 ON p1.product_id = s1.product_id
                WHERE c1.client_id = s1.client_id
                AND p1.product_type = 'Boat'
                AND s1.sale_date >= '2022-12-21' 
                AND s1.sale_date <= '2023-03-20' -- winter
              )
AND    NOT EXISTS ( SELECT * FROM sale s2
                   JOIN  product p1 ON p1.product_id = s2.product_id 
                   WHERE c1.client_id = s2.client_id
                   AND p1.product_type = 'Boat' 
                   AND s2.sale_date >= '2023-6-21' 
                   AND s2.sale_date <= '2023-09-20' -- summer
                  ) ;

Risultati:

Dopo i due esempi precedenti, questo non dovrebbe essere troppo difficile da capire; è la combinazione di entrambi. L'idea è quella di selezionare l'intero record (SELECT *) dalla tabella client e poi utilizzare EXISTS per verificare che una barca sia stata acquistata lo scorso inverno. Successivamente, si utilizza NOT EXISTS per verificare che una barca non sia stata acquistata nell'estate scorsa. Si noti che entrambe le sottoquery hanno un JOIN tra i caratteri sale e product perché è necessario utilizzare le colonne product_type e sale_date nelle condizioni WHERE.

A questo punto, vorrei fare una precisazione sulle subquery correlate. Abbiamo detto che le subquery correlate vengono eseguite una volta per ogni riga candidata. Questo fatto può influire sulle prestazioni dell'intera query, soprattutto quando si lavora con tabelle di grandi dimensioni.

In sintesi, l'operatore EXISTS (e le subquery correlate) è una potente risorsa SQL per alcuni tipi di query. Tuttavia, è bene evitare le sottoquery correlate se si lavora con tabelle di grandi dimensioni.

Esercitarsi con l'operatore EXISTS

Come per molti altri linguaggi informatici, imparare SQL facendo esercizi è uno dei modi migliori per acquisire competenze. In questa sezione, quindi, mostrerò tre esercizi sull'operatore EXISTS di diversa complessità.

Esercizio 1: Vecchi ordini

Esercizio: Il proprietario di un'azienda vuole sapere quali prodotti (automobili o barche) non sono stati ordinati negli ultimi 365 giorni.

Soluzione:

	 SELECT p1.product_name
       FROM  product p1
       WHERE NOT EXISTS ( SELECT 1 FROM sale s
                          WHERE  s.product_id = p1.product_id
                          AND    s.sale_date >= CURRENT_DATE - 365
                        );

Risultati:

Spiegazione: La query principale utilizza la tabella product per ottenere la colonna product_name. La clausola WHERE di questa query è la parte fondamentale. Ha una condizione NOT EXISTS che valuta TRUE per i prodotti che non sono stati venduti nell'ultimo anno. La condizione s.sale_date >= CURRENT_DATE - 365 nella sottoquery ottiene tutte le righe dell'ultimo anno.

Si noti che la sottoquery restituisce una costante ( 1 ). Questo perché il dato importante della subquery è il numero di righe che restituisce, non il contenuto delle righe restituite; quindi, possiamo inserire una colonna o una costante come 1.

Esercizio 2: Acquisti di motori per barche

Esercizio: La nostra azienda è famosa per la produzione di un motore per imbarcazioni e abbiamo alcuni clienti che acquistano solo questo prodotto. Il reparto marketing vuole identificare i clienti che acquistano solo motori per barche, in modo da poterli indirizzare in una campagna di marketing.

Soluzione:

SELECT * FROM client c1
WHERE EXISTS ( SELECT * FROM sale s1  -- they buy boat engines
		   JOIN  product p1 ON p1.product_id = s1.product_id 
                WHERE c1.client_id = s1.client_id
                AND   p1.product_name = 'Boat engine'   
               )
AND NOT EXISTS ( SELECT * FROM sale s2  -- they never buy other product
                 JOIN  product p2 ON p2.product_id = s2.product_id 
		    WHERE c1.client_id = s2.client_id
                 AND p2.product_name <> 'Boat engine' 
                );

Risultati:

Spiegazione: Questo esercizio ha una clausola WHERE con due condizioni. La prima condizione utilizza l'operatore EXISTS per verificare che il cliente selezionato dalla query esterna abbia acquistato un motore per imbarcazioni. La seconda condizione WHERE utilizza l'operatore NOT EXISTS per verificare che lo stesso cliente (quello selezionato dalla query esterna) non abbia mai acquistato un altro tipo di prodotto.

Se volete approfondire gli esercizi sulle subquery, vi consiglio di leggere l'articolo SQL Subquery Practice: 15 esercizi con soluzioni.

Esercizio 3: Mai acquistato

Esercizio: Il proprietario dell'azienda vuole un report sui prodotti mai acquistati . Il report deve avere solo due colonne: client_name e product_name. Il contenuto del report deve essere il complemento della tabella. sale tabella. In altre parole, se un cliente non ha mai acquistato un prodotto, allora la coppia formata da questo client_name e questo product_name deve essere presente nel report.

Soluzione: Ci sono due approcci per risolvere questo esercizio:

• Utilizzando NOT EXISTS.
• Utilizzando l'operatore set EXCEPT (o MINUS).

Mostreremo entrambi gli approcci.

Soluzione utilizzando NOT EXISTS:

SELECT c1.client_name, p1.product_name
FROM client c1, product p1
WHERE NOT EXISTS ( SELECT 1 FROM sale s 
                   WHERE c1.client_id = s.client_id
                   AND   p1.product_id = s.product_id  
                 )

Risultati parziali della query:

Spiegazione: Il primo punto da capire è il risultato di questa parte della query:

SELECT c1.client_name, p1.product_name
FROM client c1, product p1

Poiché non stiamo unendo correttamente le tabelle client e productil risultato è un prodotto cartesiano, cioè un insieme di tutte le possibili coppie <client_name, product_name>.

Una volta ottenute tutte le coppie possibili, si procede a scartare quelle presenti nella tabella con l'operatore sale utilizzando l'operatore NOT EXISTS.

Soluzione utilizzando EXCEPT:

SELECT c1.client_name, p1.product_name
FROM client c1, product p1
EXCEPT
SELECT client_name, product_name 
FROM sale s
JOIN product p ON p.product_id  = s.product_id
JOIN client c ON c.client_id = s.client_id

Spiegazione: La prima parte di questo approccio è simile alla soluzione precedente: creiamo tutte le possibili coppie cliente-prodotto. Quindi, utilizzando l'operatore EXCEPT, si rimuovono le coppie presenti nella tabella. sale tabella. In questo modo si ottengono le coppie che stiamo cercando.

Ecco la differenza: Nella seconda parte dell'operatore EXCEPT, dobbiamo JOIN la tabella con le coppie di prodotti sale con l'operatore product e client e le tabelle.

Continuate ad ampliare le vostre competenze sull'operatore SQL EXISTS

In questo articolo abbiamo trattato gli operatori EXISTS e NOT EXISTS con diversi esempi. Abbiamo anche spiegato i pro e i contro delle subquery correlate. Ai lettori che desiderano approfondire le proprie competenze in SQL, consiglio di seguire il percorso Pratica su SQL traccia. Troverete centinaia di esercizi su argomenti SQL come JOIN, GROUP BY, HAVING, subquery e persino l'operatore EXISTS.

Se siete alle prime armi con l'SQL o avete bisogno di un ripasso, vi consiglio il nostro SQL per principianti corso. Quando aumentate le vostre competenze in SQL, investite su di voi!