Cours MariaDB / Exprimer des conditions avec les sous-requêtes, EXISTS et NOT EXISTS

Une sous-requête fournit une valeur ou un ensemble de lignes à l’intérieur d’une autre requête SQL. Dans MariaDB, EXISTS et NOT EXISTS vérifient la présence ou l’absence de lignes correspondantes : ils permettent par exemple de sélectionner chaque client une seule fois selon l’existence d’une commande payée.

Choisir entre valeur, liste et test d’existence

Comparer le prix d’un produit à la moyenne nécessite un nombre. Vérifier qu’un client a déjà payé une commande demande seulement de savoir si une commande correspondante existe. Définissez la forme du résultat attendu avant de choisir la syntaxe.

Forme Ce qu’elle fournit ou vérifie Point à contrôler
Sous-requête scalaire Une valeur issue d’une colonne et d’au plus une ligne Zéro ligne donne NULL ; plusieurs lignes provoquent une erreur
IN avec sous-requête Appartenance à un ensemble de valeurs Types comparés et éventuelles valeurs NULL
EXISTS Présence d’au moins une ligne admissible Corrélation avec la bonne ligne extérieure
NOT EXISTS Absence de toute ligne admissible Définition précise de ce qui doit être absent

La requête extérieure conserve ici l’unité « un client ». Une jointure peut produire plusieurs lignes pour ses commandes ; un test d’existence qualifie la ligne cliente sans ajouter ces lignes au résultat. Les répétitions et les totaux après plusieurs JOIN concernent le niveau de détail des lignes jointes, qui répond à un besoin différent.

Préparer des clients aux historiques différents

Connectez-vous au serveur d’apprentissage avec course_user et utilisez la base shopcourse. Le compte doit pouvoir créer, remplir, lire et nettoyer les tables d’exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 précise comment se connecter et exécuter un fichier.

  1. Dans le client MariaDB, lancez SELECT VERSION(), DATABASE(); et SHOW TABLES;. Vérifiez le serveur, la base et les noms présents avant toute création.
  2. Les tables c29_customers, c29_products, c29_orders et c29_items appartiennent uniquement à cet exercice. Si elles existent, examinez leurs définitions et leurs lignes. Ne les supprimez que si vous savez qu’elles ne contiennent aucune donnée à conserver.
  3. Enregistrez tout le bloc suivant en UTF-8 dans un fichier, par exemple C:/mariadb-lab/c29-subqueries.sql, en créant le dossier si nécessaire. Dans le client, exécutez SOURCE C:/mariadb-lab/c29-subqueries.sql; avec le chemin réel. Vous pouvez aussi utiliser un éditeur SQL connecté ; ces instructions ne s’exécutent pas directement à l’invite du système.
USE shopcourse;
CREATE TABLE c29_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c29_order_customer FOREIGN KEY (customer_id)
  REFERENCES c29_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c29_item_order FOREIGN KEY (order_id)
  REFERENCES c29_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c29_item_product FOREIGN KEY (product_id)
  REFERENCES c29_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c29_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c29_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c29_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c29_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Le jeu autonome contient quatre clients, quatre produits, quatre commandes et six articles de commande. Les chaînes coréennes sont conservées entre les traductions pour reproduire les mêmes résultats. Les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe. Les clients 1 et 3 ont la ville 서울 (Séoul), le client 2 부산 (Busan), le client 4 대전 (Daejeon). Conservez ces textes en UTF-8 et suivez les identifiants.

Le client 1 a deux commandes paid, le client 2 en a une, le client 3 possède seulement une commande canceled et le client 4 n’a aucune commande. Ce contraste permet de distinguer « aucune commande » de « aucune commande payée ».

Comparer un prix à une moyenne scalaire

Exécutez cette requête après la préparation, dans shopcourse :

SELECT product_id,product_name FROM c29_products
WHERE price > (SELECT AVG(price) FROM c29_products) ORDER BY product_id;

Les prix sont 3500, 1500, 8000 et 25000 ; leur moyenne vaut 9500. Le résultat contient uniquement le produit 4, nommé 스탠드, la lampe. La fonction AVG() résume l’ensemble des prix en une valeur, utilisée par la comparaison extérieure.

À une position scalaire, une sous-requête renvoyant plusieurs prix sans agrégation provoque une erreur telle que Subquery returns more than 1 row. Ne masquez pas un choix indéfini par LIMIT 1 : déterminez s’il faut une moyenne, un maximum, une ligne identifiée ou un test sur un ensemble. La référence des sous-requêtes scalaires précise aussi le cas où aucune ligne ne produit de valeur : le résultat scalaire est alors NULL.

Dans cette requête, AVG() sans regroupement fournit une valeur même si l’ensemble à agréger est vide : cette valeur vaut alors NULL. Une comparaison ordinaire à ce NULL ne devient pas vraie. La documentation de AVG décrit son comportement sur les valeurs nulles et les ensembles vides.

Sélectionner chaque client avec un EXISTS corrélé

Pour conserver les clients ayant au moins une commande payée, reliez la sous-requête au client extérieur :

SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE EXISTS (SELECT 1 FROM c29_orders o
 WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;
customer_id customer_name
1 김민수
2 이서연

Les clients 1 et 2 apparaissent une fois chacun. La deuxième commande payée du client 1 ne multiplie pas la ligne extérieure. SELECT 1 est une façon d’écrire le test : le nombre 1 ne représente ni un montant ni le nombre de commandes.

La condition o.customer_id=c.customer_id établit la corrélation, c’est-à-dire le lien avec le client examiné. Si vous l’enlevez en gardant uniquement o.status='paid', la question devient « existe-t-il une commande payée quelque part ? ». Comme la réponse est vraie sur ce jeu, les quatre clients seraient retenus. Rétablissez la corrélation pour retrouver uniquement les clients 1 et 2.

Le test porte sur les lignes produites, pas sur la valeur écrite dans la liste SELECT. Ainsi, SELECT EXISTS (SELECT NULL); renvoie 1 : une ligne contenant une valeur nulle existe bien. La documentation de EXISTS et NOT EXISTS dans MariaDB explique cette distinction.

Définir l’absence avec NOT EXISTS et distinguer NOT IN

Pour chercher les clients qui n’ont aucune commande payée, inversez le test tout en conservant les deux conditions internes :

SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE NOT EXISTS (SELECT 1 FROM c29_orders o
 WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;

Le résultat contient 3 / 박지우 et 4 / 최하늘. Le client 3 a une commande annulée, qui ne satisfait pas le statut demandé ; le client 4 n’a aucune commande. Si vous retirez uniquement AND o.status='paid', vous demandez l’absence de commande de tout statut et obtenez uniquement le client 4. Remettez ensuite le critère pour retrouver les clients 3 et 4.

NOT IN demande une comparaison avec un ensemble de valeurs. La présence de NULL dans cet ensemble peut rendre le résultat inconnu au lieu de vrai. Par exemple, SELECT 4 NOT IN (1,2,NULL); renvoie NULL, tandis que SELECT 1 NOT IN (1,2,NULL); renvoie 0, puisqu’une correspondance certaine existe pour 1. Une condition inconnue ne passe pas le filtre WHERE.

Avec les identifiants non nuls de notre modèle et un ensemble interne sans valeurs nulles, certaines requêtes avec NOT IN et NOT EXISTS peuvent sélectionner les mêmes clients. Ce n’est pas une équivalence générale : contrôlez aussi les éventuelles valeurs nulles du côté extérieur et la relation voulue. La référence de NOT IN illustre ces comparaisons, et le cours sur NULL et IS NULL explique leurs conséquences.

Vérifier la forme, la corrélation et les résultats

Sur les données initiales, les contrôles sont : produit 4 au-dessus de la moyenne ; clients 1 et 2 avec une commande payée ; clients 3 et 4 sans commande payée ; client 4 seul sans aucune commande. Ces lectures ne modifient pas les données, vous pouvez donc réexécuter les requêtes après chaque variation du filtre.

  • Erreur de plusieurs lignes : vérifiez qu’une position scalaire reçoit une seule colonne et au plus une ligne. Pour une appartenance à plusieurs valeurs, choisissez une forme adaptée au lieu de retenir une ligne arbitraire.
  • Tous les clients ou aucun : contrôlez la corrélation avec l’alias extérieur et le statut demandé. Une condition globale peut donner la même réponse pour tous les clients.
  • Résultat absent avec NOT IN : examinez les valeurs nulles de l’ensemble interne et les colonnes comparées.
  • Différence avec un JOIN : vérifiez si votre résultat doit contenir un client une fois ou une ligne pour chaque commande correspondante.

Pour diagnostiquer un cas corrélé, vous pouvez exécuter la requête interne séparément en remplaçant temporairement la référence extérieure par un identifiant connu, par exemple le client 1, puis comparer avec le client 4. Écrire le sens logique « pour ce client » ne signifie pas que le moteur rescane physiquement toute la table à chaque ligne : l’optimiseur peut choisir d’autres stratégies. Ne présentez pas EXISTS comme systématiquement plus rapide qu’un JOIN ; une conclusion de performance demande des données et un plan d’exécution représentatifs.

Avant de recommencer depuis zéro, vérifiez la base, les définitions et les données, puis sauvegardez les informations à conserver. Le bloc suivant supprime seulement les quatre objets d’exercice c29_, des enfants vers leurs parents. La documentation de DROP TABLE rappelle la suppression des structures et des lignes ; un ROLLBACK ordinaire ne les rétablit pas.

DROP TABLE shopcourse.c29_items;
DROP TABLE shopcourse.c29_orders;
DROP TABLE shopcourse.c29_products;
DROP TABLE shopcourse.c29_customers;

Réexécutez le bloc de préparation complet puis les trois requêtes de lecture pour retrouver les résultats initiaux. Les tables d’autres exercices, comme les noms préfixés c28_, et une éventuelle table générale products ne font pas partie de ce nettoyage.