Qu'est-ce que la clause WITH en SQL ?

Dans cet article, vous allez découvrir la clause SQL WITH, également connue sous le nom d'expression de table commune (CTE). Nous passerons en revue quelques exemples pour démontrer certains de leurs cas d'utilisation à la lumière de leurs avantages.

Introduction à la clause SQL WITH

La clause WITH en SQL a été introduite dans le langage SQL standard pour simplifier les longues requêtes complexes, notamment celles qui comportent des JOINs et des sous-requêtes. Souvent appelée de manière interchangeable CTE ou refactorisation de sous-requêtes, une clause WITH définit un ensemble de données temporaire dont la sortie est disponible pour être référencée dans les requêtes suivantes.

La meilleure façon d'apprendre la clause WITH en SQL est de s'exercer. Je vous recommande le cours interactif de LearnSQL.fr Requêtes récursives. Il contient plus de 100 exercices qui enseignent la clause WITH en commençant par les bases et en progressant vers des sujets avancés comme les requêtes récursives WITH.

La clause WITH est considérée comme " temporaire " car le résultat n'est pas stocké de façon permanente dans le schéma de la base de données. Elle agit comme une vue temporaire qui n'existe que pendant la durée de la requête, c'est-à-dire qu'elle n'est disponible que pendant la durée d'exécution des instructions SELECT, INSERT, UPDATE, DELETE ou MERGE. Elle n'est valable que dans la requête à laquelle elle appartient, ce qui permet d'améliorer la structure d'une déclaration sans polluer l'espace de noms global.

La clause WITH est utilisée dans les requêtes pour lesquelles une table dérivée n'est pas adaptée. Elle est donc considérée comme une alternative plus soignée aux tables temporaires. En bref, le principal avantage de la clause WITH est qu'elle permet d'organiser et de simplifier les requêtes hiérarchiques longues et complexes en les décomposant en morceaux plus petits et plus lisibles.

La clause WITH a été introduite dans la norme SQL pour la première fois en 1999 et est désormais disponible dans tous les principaux SGBDR. Voici quelques applications courantes de la CTE SQL :

• La référence à une table temporaire plusieurs fois dans une seule requête.
• L'exécution d'agrégations à plusieurs niveaux, comme la recherche de la moyenne des maximums.
• L'exécution d'un calcul identique plusieurs fois dans le contexte d'une requête plus large.
• L'utiliser comme une alternative à la création d'une vue dans la base de données.

Voyons un exemple simple et rapide de la clause WITH ci-dessous en utilisant la table CommandeDetails de la célèbre base de données Northwind. L'objectif est de retourner la quantité moyenne commandée par ProduitID :

REQUÊTE :

WITH cte_quantity
AS
(SELECT
	SUM(Quantite) as Total
FROM CommandeDetails
GROUP BY ProduitID)

SELECT
	AVG(Total) QuantiteMoyenneDeProduits
FROM cte_quantity;

RÉSULTAT :

Nombre d'enregistrements : 1

Si vous deviez l'exécuter sans la clause WITH et utiliser une sous-requête à la place, la requête ressemblerait à ceci :

REQUÊTE :

SELECT
	AVG(Total) QuantiteMoyenneDeProduits
FROM
(SELECT
SUM(Quantite) as Total
FROM CommandeDetails
GROUP BY ProduitID)

Bien que vous ne puissiez pas voir beaucoup de différences tangibles entre les deux, la structure décomposée que facilite la clause WITH sera d'une valeur inestimable lorsque vos requêtes augmenteront en taille et en hiérarchie. Nous en verrons un exemple ci-dessous sous la forme d'une clause WITH imbriquée. Vous trouverez d'autres exemples dans l'un de nos précédents articles sur le sujet - Les CTE expliqués avec des exemples.

La syntaxe de la clause WITH

La séquence générale des étapes pour exécuter une clause WITH est la suivante :

1. Lancez le WITH
2. Spécifiez le nom de l'expression pour la requête à définir.
3. Facultatif: Spécifiez les noms de colonnes séparés par des virgules.
4. Après avoir attribué le nom de l'expression, entrez la commande AS. Les expressions, dans ce cas, sont les ensembles de résultats nommés que vous utiliserez plus tard dans la requête principale pour faire référence à l'ETC.
5. Rédigez la requête nécessaire pour produire l'ensemble de données temporaire souhaité.
6. Si vous travaillez avec plusieurs CTE ou clauses WITH, lancez chacune d'entre elles en les séparant par une virgule et répétez les étapes 2 à 4. Une telle disposition est également appelée clause imbriquée WITH.
7. Faites référence aux expressions définies ci-dessus dans une requête ultérieure en utilisant SELECT, INSERT, UPDATE, DELETE, ou MERGE.

La syntaxe d'implémentation d'une clause WITH est présentée dans le pseudo-code ci-dessous :

--CTE
WITH ExpressionNom1 (colonne_1, colonne_2, ..., colonne_n)
AS
(CTE query definition 1),
ExpressionNom2 (colonne_1, colonne_2,…,colonne_n)
AS
(CTE query definition 2)

--Final query using CTE
SELECT expression_A, expression_B, ...
FROM ExpressionNom2

La clause WITH est un remplacement direct des sous-requêtes normales. La seule différence est que vous pouvez réutiliser le même ensemble de résultats dérivés plusieurs fois dans votre code lorsque vous utilisez la clause WITH pour générer un CTE. Vous ne pouvez pas faire de même avec les sous-requêtes.

Comme nous le voyons ci-dessus, les principaux paramètres d'exécution d'une clause WITH sont :

• WITH : utilisée pour créer un CTE, ou le(s) ensemble(s) de données temporaire(s).
• ExpressionNom (colonne_1, ..., colonne_n) : le nom de l'ensemble de données temporaire virtuel qui sera utilisé dans la requête principale, et colonne_1 à colonne_n sont les noms des colonnes qui peuvent être utilisées dans les étapes de requête suivantes.
• AS (...) : Cette section définit la requête qui va alimenter le CTE ExpressionNom. Si vous implémentez un CTE imbriqué, la requête dans le deuxième AS fera probablement référence au premier CTE.
• SELECT expression_A, expression_B FROM ExpressionNom : cette section spécifie la requête externe principale dans laquelle l'instruction SELECT (ou les instructions INSERT,UPDATE,DELETE ouMERGE ) est utilisée sur un ou plusieurs des CTE générés pour produire ensuite le résultat souhaité.

Tous les paramètres mentionnés ci-dessus sont obligatoires. Vous pouvez choisir d'utiliser les clauses WHERE, GROUP BY, ORDER BY, et/ou HAVING selon vos besoins.

Lorsqu'une requête contenant une clause WITH est exécutée, la requête mentionnée dans la clause est d'abord évaluée et le résultat de cette évaluation est stocké dans une relation temporaire. Ensuite, la requête principale associée à la clause WITH est finalement exécutée en utilisant la relation temporaire produite.

Cet exemple montre une clause imbriquée WITH en utilisant la même table CommandeDetails que ci-dessus. Une clause WITH imbriquée, ou CTE imbriqués, implique deux CTE dans la même requête, le second faisant référence au premier.

OBJECTIF : retourner le nombre moyen de commandes, ou de ventes réalisées, par EmployeID pour ExpediteurID 2 et ExpediteurID 3.

RECHERCHE :

--1st CTE
WITH VentesCTE
AS
(SELECT
	EmployeID,
	COUNT(CommandeID) as Orders,
	ExpediteurID
FROM Orders
GROUP BY EmployeID, ExpediteurID),

--2nd CTE (nested)
ExpediteurCTE
AS
(SELECT *
FROM VentesCTE
WHERE ExpediteurID=2 or ExpediteurID=3)

--Query using CTE
SELECT
	ExpediteurID, AVG(Orders) CommandeMoyenneParEmploye
FROM
ExpediteurCTE
GROUP BY ExpediteurID;

RÉSULTAT :

Nombre d'enregistrements : 2

Ici, nous calculons le nombre moyen de commandes par employé, mais uniquement pour ExpediteurID 2 et ExpediteurID 3. Dans le premier CTE, VentesCTE le nombre de commandes est compté et regroupé par EmployeID et ExpediteurID. Dans le second CTE, ExpediteurCTE nous faisons référence au premier CTE et définissons les conditions ExpediteurID à l'aide d'une clause WHERE. Ensuite, dans la requête principale, nous nous référons uniquement au deuxième CTE, ExpediteurCTE pour calculer la moyenne des commandes par employé par ExpediteurID.

D'autres nuances de la syntaxe associée aux clauses SQL WITH et aux CTE sont détaillées dans le module 2 du cours Requêtes récursives qui contient également une série d'exemples plus avancés.

Cas d'utilisation de la clause SQL WITH

Alors, quand devez-vous vraiment utiliser une clause WITH ? Eh bien, il existe quelques cas d'utilisation uniques. La plupart d'entre eux sont axés sur la commodité et la facilité de développement et de maintenance des requêtes.

Les applications remarquables et les avantages associés des CTE SQL peuvent être résumés comme suit :

• Amélioration de la lisibilité du code - La programmation lettrée est une approche introduite par Donald Kuth, qui vise à organiser le code source dans l'ordre de la logique humaine, de sorte qu'il puisse être compris avec un minimum d'effort en le lisant comme un roman de manière séquentielle. La clause SQL WITH permet d'atteindre cet objectif en créant des tables virtuelles nommées et en divisant les grands calculs en petites parties. Celles-ci peuvent ensuite être combinées plus tard dans la requête, dans l'adresse finale SELECT ou dans une autre instruction, au lieu de tout regrouper en un seul gros morceau.
• Amélioration de la maintenabilité du code - La maintenabilité va de pair avec la lisibilité. Au fur et à mesure que vos requêtes et vos bases de données évoluent, vous aurez toujours besoin de déboguer et de résoudre les problèmes - un code plus facile à lire est plus facile à maintenir !
• Alternative à une vue - Les CTE peuvent se substituer aux vues et SELECT, INSERT, UPDATE, DELETE ou MERGE. Cela peut être particulièrement utile si vous n'avez pas les droits système pour créer un objet de vue ou si vous ne voulez pas créer une vue juste pour être utilisée dans une seule requête.
• Surmonter les limites de l'instruction - Les CTE permettent de surmonter les contraintes telles que SELECT les limites de l'instruction, par exemple, l'exécution d'une GROUP BY, l'utilisation de fonctions non-déterministes.
• Traitement des structures hiérarchiques - Il s'agit de l'une des applications les plus avancées du CTE et elle est réalisée par ce que l'on appelle les CTE récursifs. Les requêtes récursives peuvent s'appeler elles-mêmes, ce qui vous permet de traverser des modèles hiérarchiques complexes. Plus d'informations à ce sujet ci-dessous.

D'autres cas d'utilisation de l'ETC sont abordés dans l'un de nos articles précédents, " Quand utiliser l'ETC ". Le cours Requêtes récursives sur le site LearnSQL.fr vous aidera à décomposer l'ensemble du sujet de l'ECC à l'aide d'exemples détaillés, afin de vous aider à maîtriser le sujet à l'aide d'exercices pratiques.

La clause récursive WITH

Les exemples ci-dessus utilisent des clauses WITH non-récursives. Les CTE récursifs sont l'une des fonctionnalités les plus avancées de la clause WITH, qui permet de se référencer soi-même au sein de ce CTE. Cela facilite considérablement la tâche des utilisateurs qui souhaitent parcourir des structures de données hiérarchiques complexes, telles que les nomenclatures d'équipement et les organigrammes.

Si vous êtes familier avec la récursion en programmation, la fonction récursive de la clause WITH intègre de manière similaire un cas de base et une étape récursive.

Réflexions finales sur la clause SQL WITH

Dans cet article, nous avons parcouru la syntaxe de base et quelques exemples de comment et quand vous pouvez définir et utiliser les clauses WITH ou CTE. Pour mieux comprendre leur mise en œuvre dans votre propre code, la pratique est la clé ! Pour cela, je vous suggère le cours Requêtes récursives cours ici sur LearnSQL.fr. Dans ce cours, vous trouverez d'autres exemples pratiques et des applications uniques de la clause WITH, ainsi que des didacticiels interactifs sur l'utilisation des CTE (récursifs et non-récursifs) dans votre environnement de travail quotidien.

Une fois que vous aurez acquis une bonne maîtrise des clauses WITH, vous serez surpris de voir à quel point vos scripts SQL peuvent être améliorés !