Comment utiliser des requêtes imbriquées en SQL

Introduction

Structured Query Language (SQL) est utilisé pour gérer les données dans un système de gestion de base de données relationnelle (SGBDR). Une fonction utile en SQL est de créer une requête dans une autre requête, également appelée sous-requête ou requête imbriquée. Une requête imbriquée est une instruction SELECT généralement encadrée de parenthèses et intégrée à une opération SELECT, INSERT ou DELETE principale.

Dans ce tutoriel, vous utiliserez des requêtes imbriquées avec les instructions SELECT, INSERT et DELETE. Vous utiliserez également des fonctions d’agrégation dans une requête imbriquée pour comparer les valeurs de données aux valeurs de données triées que vous avez spécifiées avec les clauses WHERE et LIKE.

Prérequis

Pour suivre ce guide, vous aurez besoin d’un ordinateur exécutant un type de système de gestion de base de données relationnelle (SGBDR) qui utilise SQL. Les instructions et exemples de ce tutoriel ont été validés en utilisant l’environnement suivant :

• 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 est installé et sécurisé sur le serveur. Suivez notre guide Comment installer MySQL sur Ubuntu 20.04 pour configurer cela. Ce guide suppose que vous avez également configuré un utilisateur MySQL non root, comme décrit dans l’étape 3 de ce guide.

Pour pratiquer l’utilisation de requêtes imbriquées dans ce tutoriel, vous aurez besoin d’une base de données et d’une table chargées de données d’exemple. Si vous n’en avez pas une prête à insérer, vous pouvez lire la section suivante Connexion à MySQL et Configuration d’une Base de Données d’Exemple pour apprendre à créer une base de données et une table. Ce tutoriel fera référence à cette base de données et à cette table d’exemple tout au long.

Connexion à MySQL et Configuration d’une Base de Données d’Exemple

Si votre base de données SQL s’exécute sur un serveur distant, connectez-vous en SSH à votre serveur depuis votre machine locale :

ssh sammy@your_server_ip

Ensuite, ouvrez l’invite MySQL, en remplaçant sammy par les informations de votre compte utilisateur MySQL :

mysql -u sammy -p

Créez une base de données nommée zooDB :

CREATE DATABASE zooDB;

Si la base de données a été créée avec succès, vous recevrez la sortie suivante :

Output
Query OK, 1 row affected (0.01 sec)

Pour sélectionner la base de données zooDB, exécutez la déclaration USE suivante :

USE zooDB;

Output
Database changed

Après avoir sélectionné la base de données, créez une table à l’intérieur. Pour l’exemple de ce tutoriel, nous allons créer une table qui stocke des informations sur les visiteurs du zoo. Cette table comportera les sept colonnes suivantes :

• guest_id : stocke les valeurs des invités qui visitent le zoo et utilise le type de données int. Cela sert également de clé primaire, ce qui signifie que chaque valeur de cette colonne fonctionnera comme un identifiant unique pour sa ligne respective.
• first_name : contient le prénom de chaque invité en utilisant le type de données varchar avec un maximum de 30 caractères.
• last_name : utilise le type de données varchar, encore une fois avec un maximum de 30 caractères, pour stocker le nom de famille de chaque invité.
• guest_type : contient le type d’invité (adulte ou enfant) pour chaque invité en utilisant le type de données varchar avec un maximum de 15 caractères.
• membership_type : représente le type d’adhésion que chaque invité détient, en utilisant le type de données varchar pour contenir un maximum de 30 caractères.
• membership_cost : stocke le coût pour différents types d’adhésion. Cette colonne utilise le type de données decimal avec une précision de cinq et une échelle de deux, ce qui signifie que les valeurs de cette colonne peuvent avoir cinq chiffres, et deux chiffres à droite du point décimal.
• total_visits: utilise le type de données int pour enregistrer le nombre total de visites de chaque invité.

Créez une table nommée guests contenant chacune de ces colonnes en exécutant la commande CREATE TABLE suivante :

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)
); 

Ensuite, insérez des données d’exemple dans la table vide :

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

Une fois les données insérées, vous êtes prêt à commencer à utiliser des requêtes imbriquées en SQL.

Utilisation de Requêtes Imbriquées avec SELECT

En SQL, une requête est une opération qui récupère des données d’une table dans une base de données et comprend toujours une instruction SELECT. Une requête imbriquée est une requête complète intégrée à une autre opération. Une requête imbriquée peut avoir tous les éléments utilisés dans une requête régulière, et toute requête valide peut être intégrée à une autre opération pour devenir une requête imbriquée. Par exemple, une requête imbriquée peut être intégrée aux opérations INSERT et DELETE. Selon l’opération, une requête imbriquée doit être intégrée en encadrant l’instruction dans le bon nombre de parenthèses pour suivre un ordre d’opérations particulier. Une requête imbriquée est également utile dans les scénarios où vous souhaitez exécuter plusieurs commandes dans une seule instruction de requête, plutôt que d’écrire plusieurs instructions pour retourner votre ou vos résultats souhaités.

Pour mieux comprendre les requêtes imbriquées, illustrons comment elles peuvent être utiles en utilisant les données d’exemple de l’étape précédente. Par exemple, disons que vous voulez trouver tous les invités dans la table guests qui ont visité le zoo plus fréquemment que le nombre moyen. Vous pourriez supposer que vous pouvez trouver ces informations avec une requête comme celle-ci:

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

Cependant, une requête utilisant cette syntaxe retournera une erreur:

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

La raison de cette erreur est que les fonctions d’agrégation comme AVG() ne fonctionnent pas à moins qu’elles ne soient exécutées dans une clause SELECT.

Une option pour récupérer ces informations serait d’abord d’exécuter une requête pour trouver le nombre moyen de visites d’invités, puis d’exécuter une autre requête pour trouver des résultats basés sur cette valeur comme dans les deux exemples suivants:

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)

Cependant, vous pouvez obtenir le même ensemble de résultats avec une seule requête en imbriquant la première requête (SELECT AVG(total_visits) FROM guests;) dans la seconde. Gardez à l’esprit qu’avec les requêtes imbriquées, utiliser la quantité appropriée de parenthèses est nécessaire pour effectuer l’opération que vous souhaitez réaliser. Cela est dû au fait que la requête imbriquée est la première opération à être exécutée:

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)

Selon cette sortie, cinq invités visitaient plus que la moyenne. Ces informations pourraient offrir des idées utiles pour réfléchir à des moyens créatifs de s’assurer que les membres actuels continuent de visiter fréquemment le zoo et renouvellent leur adhésion chaque année. De plus, cet exemple démontre la valeur de l’utilisation d’une requête imbriquée dans une seule déclaration complète pour obtenir les résultats souhaités, plutôt que d’avoir à exécuter deux requêtes distinctes.

Utilisation de Requêtes Imbriquées avec INSERT

Avec une requête imbriquée, vous n’êtes pas limité à l’insérer uniquement dans d’autres déclarations SELECT. En fait, vous pouvez également utiliser des requêtes imbriquées pour insérer des données dans une table existante en intégrant votre requête imbriquée dans une opération INSERT.

Pour illustrer, supposons qu’un zoo affilié demande des informations sur vos invités car ils sont intéressés à offrir une réduction de 15 % aux invités qui achètent une adhésion « Résident » à leur emplacement. Pour ce faire, utilisez CREATE TABLE pour créer une nouvelle table appelée upgrade_guests qui contient six colonnes. Faites attention aux types de données, tels que int et varchar, et aux caractères maximum qu’ils peuvent contenir. S’ils ne correspondent pas aux types de données d’origine de la table guests que vous avez créée dans la section de mise en place d’une base de données d’exemple, vous recevrez une erreur lorsque vous essayez d’insérer des données de la table guests en utilisant une requête imbriquée et les données ne seront pas transférées correctement. Créez votre table avec les informations suivantes :

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)
); 

Pour la cohérence et la précision, nous avons gardé la plupart des informations sur les types de données dans cette table identiques à celles de la table guests. Nous avons également supprimé toutes les colonnes supplémentaires que nous ne voulons pas dans la nouvelle table. Avec cette table vide prête à être utilisée, la prochaine étape consiste à insérer les valeurs de données souhaitées dans la table.

Dans cette opération, écrivez INSERT INTO et la nouvelle table upgrade_guests, afin qu’il y ait une direction claire pour l’insertion des données. Ensuite, écrivez votre requête imbriquée avec l’instruction SELECT pour récupérer les valeurs de données pertinentes et FROM pour vous assurer qu’elles proviennent de la table guests.

De plus, appliquez une réduction de 15 % à tous les membres « Résidents » en incluant l’opération mathématique de multiplication, * pour multiplier par 0,85, dans l’instruction de requête imbriquée (membership_cost * 0.85). Ensuite, utilisez la clause WHERE pour trier les valeurs dans la colonne membership_type. Vous pouvez encore restreindre les résultats aux adhésions « Résidents » en utilisant la clause LIKE et placez le symbole de pourcentage % avant et après le mot « Résident » entre guillemets simples pour sélectionner toutes les adhésions qui suivent le même modèle, ou dans ce cas le même libellé. Votre requête sera écrite comme suit :

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

La sortie indique qu’il y avait cinq enregistrements ajoutés à la nouvelle table upgrade_guests. Pour confirmer que les données que vous avez demandées ont été transférées avec succès de la table guests dans la table vide upgrade_guests que vous avez créée, et avec les conditions que vous avez spécifiées avec la requête imbriquée et la clause WHERE, exécutez ce qui suit :

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)

Selon cette sortie de votre nouvelle table upgrade_guests, les informations sur l’adhésion des invités liées aux « Résidents » de la table guest ont été insérées correctement. De plus, le nouveau membership_cost a été recalculé avec la réduction de 15 % appliquée. Par conséquent, cette opération a permis de segmenter et de cibler le public approprié et dispose des prix remisés disponibles pour partager avec ces nouveaux membres potentiels.

Utilisation de requêtes imbriquées avec DELETE

Pour pratiquer l’utilisation d’une requête imbriquée avec une instruction DELETE, supposons que vous souhaitiez supprimer les invités fréquents car vous voulez uniquement vous concentrer sur la promotion de la remise pour le pass premium amélioré auprès des membres qui ne visitent pas souvent le zoo pour le moment.

Commencez cette opération avec l’instruction DELETE FROM afin qu’il soit clair d’où les données sont supprimées, dans ce cas, la table upgrade_guests. Ensuite, utilisez la clause WHERE pour trier les total_visits qui sont supérieurs à la quantité spécifiée dans la requête imbriquée. Dans votre requête imbriquée, utilisez SELECT pour trouver la moyenne, AVG, des total_visits, de sorte que la clause WHERE précédente dispose des valeurs de données appropriées à comparer. Enfin, utilisez FROM pour récupérer ces informations de la table guests. L’instruction de requête complète sera comme suit :

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

Output
Query OK, 2 rows affected (0.00 sec)

Confirmez que ces enregistrements ont été supprimés avec succès de la table upgrade_guests et utilisez ORDER BY pour organiser les résultats par total_visits dans l’ordre numérique croissant :

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)

Comme le montre cette sortie, l’instruction DELETE et la requête imbriquée ont fonctionné correctement en supprimant les valeurs de données spécifiées. Cette table contient maintenant les informations pour les trois invités ayant moins que le nombre moyen de visites, ce qui est un excellent point de départ pour que le représentant du zoo les contacte afin de leur proposer de passer à un passe premium à prix réduit et, espérons-le, les incite à aller plus souvent au zoo.

Conclusion

Les requêtes imbriquées sont utiles car elles vous permettent d’obtenir des résultats très précis que vous ne pourriez obtenir autrement qu’en exécutant des requêtes séparées. De plus, l’utilisation des instructions INSERT et DELETE avec des requêtes imbriquées vous offre une autre façon d’insérer ou de supprimer des données en une seule étape. Si vous souhaitez en savoir plus sur la manière d’organiser vos données, consultez notre série sur Comment utiliser SQL.