Come utilizzare query nidificate in SQL

Introduzione

Structured Query Language (SQL) viene utilizzato per gestire i dati in un sistema di gestione di database relazionali (RDBMS). Una funzione utile in SQL è la creazione di una query all’interno di un’altra query, anche nota come subquery o query nidificata. Una query nidificata è una dichiarazione SELECT che è tipicamente racchiusa tra parentesi e incorporata all’interno di un’operazione primaria SELECT, INSERT o DELETE.

In questo tutorial, utilizzerai query nidificate con le dichiarazioni SELECT, INSERT e DELETE. Utilizzerai anche funzioni di aggregazione all’interno di una query nidificata per confrontare i valori dei dati con i valori dei dati ordinati che hai specificato con le clausole WHERE e LIKE.

Prerequisiti

Per seguire questa guida, avrai bisogno di un computer che esegua un tipo qualsiasi di sistema di gestione di database relazionali (RDBMS) che utilizzi SQL. Le istruzioni e gli esempi in questo tutorial sono stati validati utilizzando l’ambiente seguente:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installato e protetto sul server. Segui la nostra guida Come Installare MySQL su Ubuntu 20.04 per configurarlo. Questa guida presuppone che tu abbia anche configurato un utente MySQL non-root, come descritto nel Passaggio 3 di questa guida.

Per esercitarti nell’uso di query nidificate in questo tutorial, avrai bisogno di un database e di una tabella caricati con dati di esempio. Se non ne hai uno pronto per l’inserimento, puoi leggere la seguente sezione Connessione a MySQL e Configurazione di un Database di Esempio per imparare come creare un database e una tabella. Questo tutorial farà riferimento a questo database e a questa tabella di esempio.

Connessione a MySQL e Configurazione di un Database di Esempio

Se il tuo database SQL viene eseguito su un server remoto, accedi tramite SSH al tuo server dalla tua macchina locale:

ssh sammy@your_server_ip

Successivamente, apri il prompt di MySQL, sostituendo sammy con le informazioni del tuo account utente MySQL:

mysql -u sammy -p

Crea un database chiamato zooDB:

CREATE DATABASE zooDB;

Se il database è stato creato con successo, riceverai il seguente output:

Output
Query OK, 1 row affected (0.01 sec)

Per selezionare il database zooDB, esegui il seguente statement USE:

USE zooDB;

Output
Database changed

Dopo aver selezionato il database, crea una tabella al suo interno. Per l’esempio di questo tutorial, creeremo una tabella che memorizza informazioni sui visitatori dello zoo. Questa tabella avrà le seguenti sette colonne:

• guest_id: memorizza i valori dei visitatori dello zoo, e utilizza il tipo di dato int. Serve anche come chiave primaria della tabella, il che significa che ciascun valore in questa colonna funzionerà come identificatore unico per la riga corrispondente.
• first_name: contiene il nome di ciascun visitatore utilizzando il tipo di dato varchar con un massimo di 30 caratteri.
• last_name: utilizza il tipo di dato varchar, ancora con un massimo di 30 caratteri, per memorizzare il cognome di ciascun visitatore.
• guest_type: contiene il tipo di visitatore (adulto o bambino) per ciascun visitatore utilizzando il tipo di dato varchar con un massimo di 15 caratteri.
• membership_type: rappresenta il tipo di abbonamento detenuto da ciascun visitatore, utilizzando il tipo di dato varchar per contenere un massimo di 30 caratteri.
• membership_cost: memorizza il costo per vari tipi di abbonamento. Questa colonna utilizza il tipo di dato decimal con una precisione di cinque e una scala di due, il che significa che i valori in questa colonna possono avere cinque cifre, e due cifre a destra del punto decimale.
• total_visits: utilizza il tipo di dato int per registrare il numero totale di visite da parte di ciascun ospite.

Crea una tabella chiamata ospiti che contenga ciascuna di queste colonne eseguendo il seguente comando CREATE TABLE:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

Successivamente, inserisci alcuni dati di esempio nella tabella vuota:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

Una volta inseriti i dati, sei pronto per iniziare a utilizzare le query nidificate in SQL.

Utilizzo di Query Nidificate con SELECT

In SQL, una query è un’operazione che recupera dati da una tabella in un database e include sempre una dichiarazione SELECT. Una query nidificata è una query completa incorporata all’interno di un’altra operazione. Una query nidificata può avere tutti gli elementi utilizzati in una query regolare, e qualsiasi query valida può essere incorporata all’interno di un’altra operazione per diventare una query nidificata. Ad esempio, una query nidificata può essere incorporata in operazioni INSERT e DELETE. A seconda dell’operazione, una query nidificata deve essere incorporata racchiudendo l’istruzione nel numero corretto di parentesi per seguire un determinato ordine di operazioni. Una query nidificata è anche utile in scenari in cui si desidera eseguire più comandi in un’unica istruzione di query, anziché scrivere più istruzioni per restituire il/i risultato/i desiderato/i.

Per comprendere meglio le query nidificate, vediamo come possono essere utili utilizzando i dati di esempio dal passaggio precedente. Ad esempio, supponiamo che tu voglia trovare tutti gli ospiti nella tabella guests che hanno visitato lo zoo con una frequenza più alta rispetto alla media. Potresti pensare di trovare queste informazioni con una query come la seguente:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

Tuttavia, una query utilizzando questa sintassi restituirà un errore:

Output
ERROR 1111 (HY000): Invalid use of group function

Il motivo di questo errore è che le funzioni di aggregazione come AVG() non funzionano a meno che non vengano eseguite all’interno di una clausola SELECT.

Un’opzione per recuperare queste informazioni sarebbe eseguire prima una query per trovare il numero medio di visite degli ospiti, e quindi eseguire un’altra query per trovare i risultati basati su quel valore come nei seguenti due esempi:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

Tuttavia, puoi ottenere lo stesso insieme di risultati con una singola query nidificando la prima query (SELECT AVG(total_visits) FROM guests;) all’interno della seconda. Tieni presente che con le query nidificate, è necessario utilizzare la quantità appropriata di parentesi per completare l’operazione che desideri eseguire. Questo perché la query nidificata è la prima operazione che viene eseguita:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

Secondo questo output, cinque ospiti stavano visitando più della media. Queste informazioni potrebbero offrire utili spunti per pensare a modi creativi per garantire che i membri attuali continuino a visitare frequentemente lo zoo e rinnovino ogni anno i loro abbonamenti. Inoltre, questo esempio dimostra il valore dell’utilizzo di una query nidificata in un’unica dichiarazione completa per ottenere i risultati desiderati, anziché dover eseguire due query separate.

Utilizzo di Query Nidificate con INSERT

Con una query nidificata, non sei limitato a inserirla solo all’interno di altre dichiarazioni SELECT. Infatti, puoi anche utilizzare query nidificate per inserire dati in una tabella esistente incorporando la tua query nidificata all’interno di un’operazione INSERT.

Per illustrare, supponiamo che uno zoo affiliato richieda alcune informazioni sui tuoi ospiti perché sono interessati a offrire uno sconto del 15% agli ospiti che acquistano una tessera “Resident” presso la loro struttura. Per fare ciò, utilizza CREATE TABLE per creare una nuova tabella chiamata upgrade_guests che contenga sei colonne. Presta attenzione ai tipi di dati, come int e varchar, e ai massimi caratteri che possono contenere. Se non sono allineati con i tipi di dati originali dalla tabella guests che hai creato nella sezione di creazione di un database di esempio, allora riceverai un errore quando cercherai di inserire i dati dalla tabella guests utilizzando una query nidificata e i dati non si trasferiranno correttamente. Crea la tua tabella con le seguenti informazioni:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

Per coerenza e accuratezza, abbiamo mantenuto la maggior parte delle informazioni sul tipo di dati in questa tabella uguali alla tabella guests. Abbiamo inoltre rimosso eventuali colonne extra che non vogliamo nella nuova tabella. Con questa tabella vuota pronta all’uso, il passo successivo è inserire i valori dei dati desiderati nella tabella.

In questa operazione, scrivi INSERT INTO e la nuova tabella upgrade_guests, in modo che ci sia una chiara direzione per l’inserimento dei dati. Successivamente, scrivi la tua query nidificata con l’istruzione SELECT per recuperare i valori dei dati rilevanti e FROM per assicurarti che provengano dalla tabella guests.

Inoltre, applica lo sconto del 15% a qualsiasi membro “Resident” includendo l’operazione matematica di moltiplicazione, *, per moltiplicare per 0.85, all’interno della dichiarazione della query nidificata (membership_cost * 0.85). Utilizza quindi la clausola WHERE per ordinare i valori nella colonna membership_type. Puoi restringere ulteriormente i risultati solo alle adesioni “Resident” utilizzando la clausola LIKE e inserendo il simbolo percentuale % prima e dopo la parola “Resident” tra virgolette singole per selezionare tutte le adesioni che seguono lo stesso modello, o in questo caso la stessa terminologia. La tua query sarà scritta come segue:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

L’output indica che sono stati aggiunti cinque record alla nuova tabella upgrade_guests. Per confermare che i dati richiesti sono stati trasferiti correttamente dalla tabella guests nella tabella vuota upgrade_guests che hai creato, e con le condizioni specificate nella query nidificata e nella clausola WHERE, esegui quanto segue:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

Secondo questo output dalla tua nuova tabella upgrade_guests, le informazioni sull’adesione degli ospiti relative ai “Resident” dalla tabella guest sono state inserite correttamente. Inoltre, il nuovo membership_cost è stato ricalcolato con lo sconto del 15% applicato. Di conseguenza, questa operazione ha aiutato a segmentare e indirizzare il pubblico appropriato e ha i prezzi scontati prontamente disponibili da condividere con questi nuovi potenziali membri.

Utilizzo delle query nidificate con DELETE

Per praticare l’uso di una query nidificata con un’istruzione DELETE, supponiamo che si desideri rimuovere eventuali ospiti che sono frequenti visitatori perché si desidera concentrarsi sulla promozione dello sconto per l’aggiornamento del pass premium ai membri che attualmente non stanno visitando molto lo zoo.

Inizia questa operazione con l’istruzione DELETE FROM in modo che sia chiaro da dove vengono eliminati i dati, in questo caso, dalla tabella upgrade_guests. Quindi, utilizza la clausola WHERE per ordinare eventuali total_visits che sono più del valore specificato nella query nidificata. Nella tua query nidificata incorporata, utilizza SELECT per trovare la media, AVG, di total_visits, in modo che la clausola WHERE precedente abbia i valori dei dati appropriati da confrontare. Infine, utilizza FROM per recuperare tali informazioni dalla tabella guests. L’istruzione di query completa sarà come segue:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Conferma che tali record siano stati eliminati con successo dalla tabella upgrade_guests e utilizza ORDER BY per organizzare i risultati per total_visits in ordine numerico e ascendente:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

Come indica questo output, l’istruzione DELETE e la query nidificata hanno funzionato correttamente nell’eliminare i valori dati specificati. Questa tabella contiene ora le informazioni dei tre ospiti con meno visite rispetto alla media, che è un ottimo punto di partenza per il rappresentante dello zoo per contattarli per un’upgrade a un pass premium a prezzo scontato e sperare di incoraggiarli a visitare lo zoo più spesso.

Conclusioni

Le query nidificate sono utili perché consentono di ottenere risultati altamente dettagliati che altrimenti si otterrebbero solo eseguendo query separate. Inoltre, l’utilizzo delle istruzioni INSERT e DELETE con query nidificate fornisce un altro modo per inserire o eliminare dati in un’unica operazione. Se desideri saperne di più su come organizzare i tuoi dati, dai un’occhiata alla nostra serie su Come Utilizzare SQL.