Quand dois-je utiliser une expression de table commune (CTE) ?

Vous souhaitez en savoir plus sur les expressions de table communes ou CTE ? Vous voulez savoir quand les CTE sont utiles ? Lisez la suite - nous allons en parler dans cet article.

Si vous avez déjà entendu parler des expressions de tableau communes, vous vous êtes probablement demandé à quoi elles servent. Et même si ce n'est pas le cas, c'est une bonne chose que vous soyez ici ! Les CTE peuvent s'avérer très utiles, surtout si vous avez déjà maîtrisé les bases du SQL, comme la sélection, le classement, le filtrage des données et la jonction de tables. Entrons tout de suite dans le vif du sujet et voyons ce que sont les CTE, ainsi que quand et comment les utiliser.

Qu'est-ce qu'une expression de table commune ?

Une expression de table commune (CTE) est une fonctionnalité SQL relativement nouvelle. Elle a été introduite dans SQL:1999, la quatrième révision de SQL, avec des normes ISO publiées de 1999 à 2002 pour cette version de SQL.

Les CTE ont été introduites pour la première fois dans SQL Server en 2005, puis PostgreSQL les a rendues disponibles à partir de la version 8.4 en 2009. MySQL a attendu un peu plus longtemps et les a rendus disponibles en 2018, à partir de la version 8.0. En termes simples, il s'agit d'un ensemble de données temporaire renvoyé par une requête, qui est ensuite utilisé par une autre requête. Il est temporaire car le résultat n'est stocké nulle part ; il n'existe que lorsque la requête est exécutée.

Il existe deux types de CTE :

• non récursif
• récursif

Dans cet article, je ne parlerai que des CTE non récursifs, et je ne mentionnerai que brièvement les CTE récursifs à la fin.

La syntaxe de base du CTE (non récursif) est la suivante :

WITH expression_name AS (CTE definition)

Comme vous pouvez le constater, il est réalisé à l'aide d'une instruction WITH. C'est pourquoi les CTE sont également appelés requêtes WITH. Après l'instruction WITH, vous définissez un CTE entre parenthèses. Définir un CTE signifie simplement écrire une requête SELECT qui vous donnera un résultat que vous voulez utiliser dans une autre requête.

Comme vous pouvez le voir, cela se fait à l'aide d'une instruction WITH. C'est pourquoi les CTE sont également appelés des requêtes WITH. Après le WITH, vous définissez un CTE entre parenthèses. Définir un CTE signifie simplement écrire une requête SELECT qui vous donnera un résultat que vous voulez utiliser dans une autre requête.

SELECT ... 
FROM expression_name

Vous définissez votre requête SELECT, puis vous faites référence à votre CTE, en l'utilisant comme n'importe quelle autre table après la clause FROM.

Si vous souhaitez en savoir plus sur les CTE avant de passer aux exemples, voici un article qui les explique bien.

La syntaxe des CTE

Voyons maintenant comment la syntaxe CTE fonctionne dans la pratique. Supposons qu'il existe une base de données contenant diverses données de l'université avec les trois tables suivantes :

• students
• subjects
• exams

La table students comporte les colonnes suivantes :

• id: l'ID de l'étudiant, une clé primaire
• first_namele prénom de l'étudiant
• last_namele nom de famille de l'étudiant

La table suivante est la subjects contenant les données :

• idl'identifiant de la matière, clé primaire
• subject_namele nom de la matière

La troisième table est la table exams table qui stocke les données suivantes :

• idl'ID de l'examen passé
• exam_datela date à laquelle l'examen a été donné
• subject_idl'ID du sujet, une clé étrangère de la table. subjects
• student_idl'ID de l'étudiant qui a passé l'examen, une clé étrangère de la table. students

Votre tâche consiste à calculer la note moyenne des étudiants. Ensuite, pour chaque étudiant dont la note moyenne est supérieure à 8,5, vous devez afficher son prénom, son nom et sa note moyenne, et les étiqueter comme étudiants "exceptionnels". Comment faire cela en utilisant un CTE ?

Le code qui vous donnera le résultat souhaité peut être écrit comme suit :

WITH grade_average AS (
SELECT	s.id,
		s.first_name,
		s.last_name,
		AVG (e.grade) AS average_grade 
FROM students s JOIN exams e ON s.id = e.student_id
GROUP BY s.id, s.first_name, s.last_name
)

SELECT	first_name,
		last_name,
		average_grade,
		'exceptional' AS tag
FROM grade_average
WHERE average_grade>8.5;

Tout d'abord, vous devez définir votre CTE. Comme vous l'avez déjà appris, cela se fait à l'aide d'une instruction WITH. Elle est suivie par le nom de l'élément CTE, qui est grade_average dans ce cas. Une requête CTE est définie entre les parenthèses. Lorsque vous la regardez seule, elle n'est pas compliquée ; il s'agit d'une requête SELECT d'apparence plutôt normale. Elle sélectionne id, first_name, et last_name dans la table students. Elle calcule également la note moyenne, en utilisant la colonne grade de la table exams. Le résultat est affiché dans la nouvelle colonne average_grade. Les tableaux students et exams sont jointes sur la colonne appropriée d'identification de l'étudiant de chaque table. Le résultat est groupé par les colonnes id, first_name, et last_name de la table students. Les enregistrements sont groupés, car vous devez obtenir le résultat par étudiant.

Après la définition du CTE, il y a une autre requête SELECT qui utilise le CTE. Cette requête sélectionne les colonnes first_name, last_name, et average_grade dans le CTE, grade_average. Elle attribue également la valeur "exceptionnel". Il y a une clause WHERE à la fin pour afficher uniquement les étudiants dont la note moyenne est supérieure à 8,5.

En exécutant la requête, vous obtiendrez les noms de trois élèves exceptionnels.

Utilisation de plus d'un CTE dans une requête

Il est possible de définir et d'utiliser plus d'un CTE dans une requête. Pour ce faire, vous séparez chaque CTE par une virgule et vous n'utilisez l'instruction WITH que pour définir le premier CTE.

Laissez-moi vous montrer un exemple. Avec les mêmes tableaux que dans l'exemple précédent, vous avez la tâche suivante : afficher le nom des matières et leurs notes moyennes et minimales respectives, mais uniquement pour les matières dans lesquelles tout le monde a réussi l'examen, c'est-à-dire que leur note est égale ou supérieure à 5.

Pour obtenir le résultat souhaité, votre requête doit ressembler à ceci :

WITH subject_average AS (
SELECT	su.id,
su.subject_name,
		AVG (e.grade) AS subject_average_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
),

min_grade AS (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS subject_min_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
HAVING MIN (e.grade) > 5
)

SELECT	sa.id,
		sa.subject_name,
		sa.subject_average_grade
FROM subject_average sa JOIN min_grade m ON sa.id =m.id;

Tout d'abord, un CTE appelé subject_average est défini. Il sélectionne les colonnes id et subject_name dans la table subjects. Ensuite, il calcule les notes moyennes en utilisant les données du tableau exams et affecte les résultats dans la nouvelle colonne subject_average_grade. Enfin, il regroupe les données pour obtenir le résultat par matière.

Maintenant, vous définissez le deuxième CTE. Rappelez-vous ce que j'ai dit précédemment - vous séparez les CTE avec des virgules et écrivez le deuxième CTE en omettant l'instruction WITH. Le deuxième CTE ici est nommé min_grade. Il sélectionne lui aussi l'id et subject_name dans la table subjects puis calcule les notes minimales et affiche le résultat dans la nouvelle colonne subject_min_grade. Il regroupe les données comme cela a été fait dans le premier CTE. Puisque vous avez besoin du résultat uniquement pour les matières dans lesquelles tout le monde a réussi, vous utilisez une clause HAVING pour sélectionner uniquement les matières dans lesquelles la note minimale est de 5 ou plus.

Enfin, vous écrivez la requête SELECT qui affichera l'ID de la matière, le nom de la matière et la note moyenne pour chaque matière qui répond aux critères. Il n'y a que deux matières de ce type :

Une fois que vous avez appris les bases des CTE, il y a le cours Requêtes récursives avec beaucoup plus d'exemples où vous pouvez pratiquer l'écriture de la syntaxe.

Quand utiliser les CTE

Les CTE vous permettent d'effectuer des agrégations à plusieurs niveaux. De quoi s'agit-il ?

Reprenons les tableaux que nous avons utilisés dans les exemples précédents. Il s'agit maintenant de calculer la note minimale moyenne et la note maximale moyenne par matière.

Par où commencer ? Si vous réfléchissez logiquement, vous devriez d'abord trouver les notes minimales et maximales par matière, puis trouver la moyenne des résultats par matière. C'est simple, le code ressemble à ceci :

SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade,
		AVG (MIN (e.grade)) AS avg_min_grade,
		AVG (MAX (e.grade)) AS avg_max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name;

En toute logique, il s'agit de calculer d'abord la note minimale et la note maximale par matière, puis la moyenne de ces valeurs. Voilà ! Vous exécutez maintenant le code, et vous obtenez un message qui ressemble à ceci :

Msg 130, Level 15, State 1, Line 16
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Hm, pas le résultat que vous espériez ? C'est parce que SQL n'autorise pas les constructions telles que AVG (MIN (e.grade)). Vos idées étaient correctes, mais vous devez utiliser un CTE pour les traduire en code SQL. Voici comment procéder :

WITH min_max_grade AS (
SELECT		su.id,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
FROM subjects su JOIN exams e ON su.id = e.subject_id
GROUP BY su.id, su.subject_name
)

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM min_max_grade;

Le CTE est nommé min_max_grade. Il contient une instruction SELECT qui calcule les notes minimales et maximales par matière, comme je voulais le faire dans la requête qui a renvoyé le message d'erreur. Le résultat est affiché dans les nouvelles colonnes min_grade et max_grade. Un CTE vous aide maintenant à traduire votre logique en code.

Après avoir défini le CTE, vous écrivez une instruction SELECT qui calcule la moyenne de min_grade et max_grade à partir du CTE. Le résultat sera affiché dans les nouvelles colonnes avg_min_grade et avg_max_grade. Maintenant que vous le voyez, c'est facile, non ?

Les CTE sont également très utiles lorsque vous devez organiser des requêtes longues et complexes. L'utilisation des CTE améliorera la lisibilité de votre code, puisqu'ils décomposent le code en étapes distinctes. Il devient plus facile de modifier le code ou de corriger les erreurs. Si vous insistez pour ne pas utiliser les CTE, votre code pourrait ressembler à ceci :

SELECT		AVG (min_grade) AS avg_min_grade,
		AVG (max_grade) AS avg_max_grade
FROM (
SELECT	su.id,
		su.subject_name,
		MIN (e.grade) AS min_grade,
		MAX (e.grade) AS max_grade
	FROM subjects su JOIN exams e ON su.id = e.subject_id
	GROUP BY su.id, su.subject_name
) AS min_max;

Comparé à la solution utilisant un CTE, cela semble un peu fouillis et plus difficile à lire. La lecture des sous-requêtes peut être difficile, car vous devez d'abord penser à ce que fait chaque sous-requête, puis revenir à la requête principale et, d'une manière ou d'une autre, les relier toutes dans votre tête. En outre, l'utilisation de sous-requêtes de ce type va à l'encontre du fonctionnement logique de votre esprit et de votre façon de penser aux étapes qui vous mèneront à la solution. Rappelez-vous que vous avez décomposé le problème en deux étapes : d'abord, calculer les notes minimales et maximales pour chaque matière, puis calculer la moyenne des notes minimales et maximales. Le code CTE reflète exactement cet ordre.

La logique du code avec une sous-requête est à l'opposé de la façon dont vous avez envisagé la solution. Ici, nous écrivons d'abord que nous voulons une moyenne des notes, puis nous spécifions dans la sous-requête que nous voulons que les moyennes soient celles des notes minimales et maximales. Lorsque vous utilisez une sous-requête, la façon dont vous écrivez le code va généralement à l'encontre de la façon dont vous pensez à la logique.

Et si le code avec une sous-requête est moins lisible et plus difficile à comprendre que le code avec un CTE dans cet exemple simple, imaginez ce que cela donnerait si vous deviez écrire des requêtes plus complexes ! Vous vous gratteriez la tête, en faisant de gros efforts pour comprendre ce que fait chaque partie du code. Avoir des difficultés à comprendre un code peut être très frustrant. C'est là que les CTE peuvent vous aider.

Vous avez probablement remarqué que les CTE ressemblent beaucoup aux sous-requêtes. Vous vous êtes peut-être demandé pourquoi j'utilise des CTE alors que tout ce que j'ai fait aurait pu être fait avec des sous-requêtes. C'est vrai, mais outre le fait qu'ils sont plus lisibles, les CTE présentent un gros avantage par rapport aux sous-requêtes : les résultats d'un CTE peuvent être utilisés plusieurs fois dans une requête. Si vous souhaitez en savoir plus sur ce sujet, je vous recommande de lire les autres différences entre les CTE et les sous-requêtes.

J'ai mentionné précédemment que les CTE peuvent être non récursifs ou récursifs. Jusqu'à présent, nous n'avons examiné que les CTE non récursifs. Les CTE récursifs sont des CTE qui se référencent eux-mêmes ; ce faisant, ils renvoient le sous-résultat et répètent le processus jusqu'à ce qu'ils renvoient le résultat final. L'utilisation des CTE récursifs permet d'exploiter pleinement la puissance des CTE; ils sont utiles pour traiter les structures hiérarchiques, telles que les arbres et les graphes.

Vous avez compris comment utiliser les CTE ?

Dans cet article, nous avons abordé certaines des bases des CTE. Vous avez appris ce qu'est un CTE, compris sa syntaxe et passé en revue quelques exemples simples pour vous donner une idée de ce que peuvent faire les CTE. J'ai également indiqué quelques utilisations courantes des CTE pour vous aider à trouver un moyen de les utiliser dans vos études ou votre travail. J'espère vous avoir donné de bonnes orientations ; à vous maintenant de mettre en pratique ce que vous avez appris.

Si vous avez des questions ou des commentaires, faites-le moi savoir dans la section des commentaires !