Cours MariaDB / Décomposer une requête avec WITH et les CTE
Une expression de table commune, ou CTE pour Common Table Expression, donne un nom à un résultat intermédiaire utilisable dans une seule instruction SQL. Avec WITH, MariaDB permet de séparer le choix des commandes, le calcul de leurs montants et la lecture finale, sans créer une table permanente ni garantir une exécution plus rapide.
Définir le rôle et l’unité de chaque étape
Pour afficher les montants des commandes payées, trois questions se présentent : quelles commandes retenir, quel montant de produits associer à chacune et quel client afficher ? Donnez à chaque étape une responsabilité précise. Un nom descriptif aide seulement si vous pouvez aussi expliquer ses colonnes et ce que représente une ligne.
| Résultat | Une ligne représente | Colonnes utiles |
|---|---|---|
| paid_orders | Une commande payée | order_id, customer_id |
| order_totals | Le montant des articles d’une commande | order_id, goods_total |
| SELECT final | Une commande payée avec son client et son montant | order_id, customer_name, goods_total |
Cette séparation rend visible le niveau de détail des résultats. Le cours sur les totaux après plusieurs jointures explique pourquoi une valeur de commande peut être répétée lorsqu’on la joint directement à ses articles. Ici, le montant des articles est d’abord regroupé par commande, puis relié à la liste des commandes retenues.
Préparer un jeu de commandes autonome
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 quatre tables de cet exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 présente la connexion et l’exécution d’un fichier.
- Vérifiez le serveur et la base avec
SELECT VERSION(), DATABASE();, puis les noms existants avecSHOW TABLES;. Si WITH est refusé, relevez la version et consultez la syntaxe WITH de MariaDB adaptée à votre serveur. - Les noms
c31_customers,c31_products,c31_ordersetc31_itemssont propres à cet exercice. S’ils existent, examinez leur définition et leurs données. Ne les supprimez que s’il s’agit bien de données d’exercice sans rien à conserver. - Enregistrez tout le bloc suivant en UTF-8, par exemple dans
C:/mariadb-lab/c31-cte.sql, en créant le dossier si nécessaire. Depuis le client MariaDB, lancezSOURCE C:/mariadb-lab/c31-cte.sql;avec le chemin réel. Le SQL s’exécute dans la connexion au serveur, pas directement dans l’invite du système.
USE shopcourse;
CREATE TABLE c31_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 c31_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 c31_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_c31_order_customer FOREIGN KEY (customer_id)
REFERENCES c31_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c31_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_c31_item_order FOREIGN KEY (order_id)
REFERENCES c31_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c31_item_product FOREIGN KEY (product_id)
REFERENCES c31_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c31_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c31_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c31_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 c31_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 contient quatre clients, quatre produits, quatre commandes et six articles de commande. Les textes coréens sont conservés entre les traductions pour reproduire exactement les 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) et le client 4 대전 (Daejeon). Conservez ces chaînes en UTF-8 et suivez les identifiants.
Les commandes 101, 102 et 103 ont le statut paid, tandis que la commande 104 est canceled. Les deux premières appartiennent au client 1, la troisième au client 2 et la dernière au client 3. Le client 4 n’a aucune commande.
Déclarer deux CTE avant le SELECT final
Exécutez l’instruction complète suivante dans shopcourse :
WITH paid_orders AS (
SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
), order_totals AS (
SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c31_items GROUP BY order_id
)
SELECT p.order_id,c.customer_name,t.goods_total
FROM paid_orders p JOIN order_totals t ON t.order_id=p.order_id
JOIN c31_customers c ON c.customer_id=p.customer_id
ORDER BY p.order_id;
| order_id | customer_name | goods_total |
|---|---|---|
| 101 | 김민수 | 11500.00 |
| 102 | 김민수 | 8000.00 |
| 103 | 이서연 | 19500.00 |
WITH apparaît une seule fois au début. Les deux définitions sont séparées par une virgule ; chaque AS (...) contient son SELECT. Le SELECT final vient après la dernière parenthèse. Placez le point-virgule seulement à la fin de l’instruction, après ORDER BY p.order_id.
paid_orders choisit les trois commandes payées. order_totals calcule une somme par commande à partir des quantités et des prix unitaires des articles. Les jointures finales associent ces sommes aux commandes retenues et aux noms des clients. Le client 1 apparaît deux fois parce qu’il a deux commandes : le résultat demandé reste une ligne par commande.
Pour 101, le calcul est 2 × 3500 + 3 × 1500 = 11500. Pour 102, il vaut 1 × 8000 = 8000, et pour 103, 1 × 3500 + 2 × 8000 = 19500. goods_total représente uniquement les produits ; les frais de livraison ne sont pas inclus dans cette somme.
Contrôler les résultats intermédiaires
Avant de chercher une erreur dans toutes les jointures, vérifiez la population choisie avec cette instruction autonome :
WITH paid_orders AS (
SELECT order_id,customer_id FROM c31_orders WHERE status='paid'
)
SELECT * FROM paid_orders ORDER BY order_id;
Les couples order_id / customer_id attendus sont 101 / 1, 102 / 1 et 103 / 2. Dans une copie de cette instruction, remplacez seulement le statut paid par canceled : vous obtenez 104 / 3. Rétablissez ensuite paid pour retrouver les trois commandes initiales.
Vous pouvez aussi exécuter seul le SELECT placé dans order_totals, en lui ajoutant ORDER BY order_id pour lire les lignes dans un ordre défini. Il produit quatre montants : 11500.00, 8000.00, 19500.00 et 1500.00 pour 101 à 104. La présence de 104 à cette étape est normale : cette CTE additionne tous les articles, et la jointure avec paid_orders écarte ensuite la commande annulée.
Pour voir une CTE pendant le diagnostic, conservez sa définition et remplacez temporairement le SELECT final par une lecture de ses colonnes. Une définition séparée du SELECT qui l’utilise ne suffit pas. Si le besoin devient « une ligne par client », ajoutez une agrégation au bon niveau ; renommer une CTE ne regroupe pas les commandes automatiquement.
Respecter la portée et distinguer lisibilité et performance
Le nom paid_orders n’existe que dans l’instruction qui le définit. Un nouveau SELECT * FROM paid_orders;, exécuté après son point-virgule, ne retrouve pas la CTE. Il faut réécrire la définition avec la nouvelle instruction. La présentation des CTE non récursives les décrit comme des résultats nommés locaux à une requête.
Pour réutiliser une définition dans plusieurs instructions, une vue peut convenir ; pour conserver des lignes intermédiaires pendant une session, examinez une table temporaire adaptée. Ces objets ont des règles et des usages distincts. Une CTE ne crée aucun de ces objets permanents à votre place. Cet exercice est non récursif : aucune CTE n’y réutilise sa propre définition pour parcourir une hiérarchie.
Le découpage logique en étapes ne promet pas une table physique pour chaque nom, ni un calcul effectué une seule fois. Selon la requête et la version, l’optimiseur peut fusionner ou matérialiser des structures intermédiaires ; les mécanismes d’optimisation documentés par MariaDB distinguent notamment ces stratégies. Évaluez la lisibilité séparément de la vitesse et inspectez le plan avec EXPLAIN lorsque la performance compte, sur des données représentatives.
Vérifier les noms, les colonnes et le nettoyage
- Erreur de syntaxe : recomptez les parenthèses, placez une virgule entre les définitions et gardez un seul point-virgule final. Une définition WITH sans son instruction finale est incomplète.
- Nom introuvable : vérifiez que vous exécutez la définition et son SELECT ensemble, dans la même instruction et la bonne base.
- Colonne introuvable : regardez la liste SELECT de la CTE, pas seulement les colonnes de la table d’origine. La requête finale ne reçoit que les colonnes exposées par cette liste.
- Montant ou nombre de lignes inattendu : contrôlez la population, la clé de jointure et le regroupement par commande. Choisissez des noms qui ne masquent pas ceux des tables de base et énumérez les colonnes utiles.
Il n’existe aucun objet permanent paid_orders ou order_totals à supprimer après ces SELECT. Pour refaire les données de départ, vérifiez la base et les quatre tables d’exercice, puis sauvegardez ce qui doit être gardé. Le bloc ci-dessous supprime leurs structures et leurs lignes, des enfants vers les parents ; la documentation de DROP TABLE précise notamment l’effet de validation implicite. Un ROLLBACK ordinaire ne restaure pas ces tables.
DROP TABLE shopcourse.c31_items;
DROP TABLE shopcourse.c31_orders;
DROP TABLE shopcourse.c31_products;
DROP TABLE shopcourse.c31_customers;
Réexécutez le bloc de préparation complet puis les deux instructions WITH pour retrouver les résultats initiaux. Les objets d’autres exercices, par exemple les tables préfixées c30_, et une éventuelle table générale products restent en dehors de ce nettoyage.




