En SQL, la clause WITH est également connue sous le nom de CTE. Il s'agit d'une fonctionnalité puissante qui nous permet de créer des ensembles de résultats temporaires dans une requête. L'un des rôles majeurs des CTE est de simplifier les requêtes complexes en sous-requêtes plus petites et réutilisables. Cela contribue à rendre le code plus lisible et maintenable à long terme.
Rejoignez-nous dans ce didacticiel pour explorer le fonctionnement des expressions de table communes à l'aide de la clause WITH et des fonctionnalités prises en charge.
Exigences:
À des fins de démonstration, nous utiliserons les éléments suivants :
- MySQL version 8.0 et supérieure
- Exemple de base de données Sakila
Une fois les exigences données remplies, nous pouvons en apprendre davantage sur les CTE et la clause WITH.
Clause SQL AVEC
La clause WITH nous permet de définir un ou plusieurs ensembles de résultats temporaires appelés expressions de table communes.
Nous pouvons référencer les CTE résultants dans la requête principale comme n’importe quelle autre table ou ensemble de résultats. Cela joue un rôle crucial dans la création de requêtes SQL modulaires.
Bien que la syntaxe de CTE puisse varier légèrement en fonction de vos besoins, ce qui suit montre la syntaxe de base de CTE en SQL :
AVEC nom_cte (colonne1, colonne2, ...) AS (
-- Requête CTE
SÉLECTIONNER ...
DEPUIS ...
OÙ ...
)
-- Requête principale
SÉLECTIONNER ...
DEPUIS ...
REJOIGNEZ cte_name SUR...
OÙ ...
Nous commençons par le mot-clé WITH qui indique à la base de données SQL que nous souhaitons créer et utiliser CTE.
Ensuite, nous précisons le nom du CTE qui nous permet de le référencer dans d'autres requêtes.
Nous spécifions également une liste facultative de noms de colonnes si le CTE inclut les alias de colonnes.
Ensuite, nous procédons à la définition de la requête CTE. Celui-ci contient toutes les tâches ou les données que le CTE effectue entre parenthèses.
Enfin, nous précisons la requête principale qui référence le CTE.
Exemple d'utilisation :
L’une des meilleures façons de comprendre comment utiliser et travailler avec les CTE est de regarder un exemple pratique.
Prenons par exemple la base de données exemple Sakila. Supposons que nous souhaitions trouver les 10 premiers clients ayant le plus grand nombre de locations.
Jetez un œil au CTE illustré ci-dessous.
Utilisation de la clause SQL WITH pour trouver les 10 principaux clients avec le nombre de locations le plus élevé :
AVEC CustomerRentals AS (SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS location_count
DU client c
REJOINDRE la location r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
SÉLECTIONNER *
DE ClientLocation
COMMANDE PAR location_count DESC
LIMITE 10 ;
Dans l'exemple donné, nous commençons par définir un nouveau CTE à l'aide du mot-clé WITH suivi du nom que nous souhaitons attribuer au CTE. Dans ce cas, nous l’appelons « CustomerRentals ».
À l'intérieur du corps CTE, nous calculons le nombre de locations pour chaque client en joignant le tableau des clients et des locations.
Enfin, dans la requête principale, nous sélectionnons toutes les colonnes du CTE, classons les résultats en fonction du nombre de locations (ordre décroissant) et limitons la sortie aux 10 premières lignes.
Cela nous permet de récupérer les clients ayant le plus grand nombre de locations, comme indiqué dans le résultat suivant :
CTE récursifs
Dans d'autres cas, vous pourriez avoir affaire à des structures de données hiérarchiques. C'est là que les CTE récursifs entrent en jeu.
Prenons par exemple un cas où l'on souhaite naviguer dans l'organisation hiérarchique ou représenter une structure arborescente. Nous pouvons utiliser le mot-clé WITH RECURSIVE pour créer un CTE récursif.
Puisqu'il n'existe aucune donnée hiérarchique que nous pouvons utiliser dans la base de données Sakila pour démontrer un CTE récursif, mettons en place un exemple de base.
Département CRÉER TABLE (Department_id INT CLÉ PRIMAIRE AUTO_INCREMENT,
nom_département VARCHAR(255) NON NULL,
parent_department_id INT,
CLÉ ÉTRANGÈRE (parent_department_id) RÉFÉRENCES département (department_id)
);
INSERT INTO département (department_name, parent_department_id)
VALEURS
(« Entreprise », NULL),
(« Finances », 1),
(« RH », 1),
(« Comptabilité », 2),
« Recrutement », 3),
(« Paie », 4) ;
Dans ce cas, nous avons un exemple de tableau « département » avec des données aléatoires. Pour retrouver la structure hiérarchique des départements, on peut utiliser un CTE récursif comme suit :
AVEC RÉCURSIF DépartementHiérarchie AS (SELECT Department_id, Department_name, parent_department_id
DU département
OÙ parent_department_id EST NULL
UNION TOUS
SELECT d.department_id, d.department_name, d.parent_department_id
DU département d
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
SÉLECTIONNER *
DE DépartementHiérarchie ;
Dans ce cas, le CTE récursif commence par les départements ayant un « parent_department_id » NULL (départements racine) et récupère de manière récursive les départements enfants.
Conclusion
Dans ce didacticiel, nous avons découvert les fonctionnalités les plus fondamentales et les plus utiles des bases de données SQL, telles que les expressions de table communes, en comprenant comment utiliser le mot-clé WITH.