Qu'est-ce qu'un CTE en T-SQL ? Un guide approfondi pour les débutants avec 7 exemples

Une expression de table commune (CTE) est une fonctionnalité T-SQL puissante qui simplifie la création de requêtes dans SQL Server. Les CTE fonctionnent comme des tables virtuelles (avec des enregistrements et des colonnes) qui sont créées à la volée pendant l'exécution d'une requête. Ils sont consommés par la requête et détruits après son exécution.

Dans certains cas, par exemple lorsque la requête attend des données dans un format spécifique et que les tables sources contiennent des données dans un autre format, un CTE peut servir de pont pour transformer les données des tables sources dans le format attendu par la requête.

Dans cet article, nous verrons comment créer un CTE en T-SQL, le dialecte SQL utilisé par Microsoft SQL Server. Pour en savoir plus sur les CTE en T-SQL, je vous recommande notre cours interactif Requêtes récursives in MS SQL Server. Il contient plus de 100 exercices pratiques sur les ETC en T-SQL. Vous pratiquerez vous-même tout ce qui est abordé dans cet article !

Expressions de table communes (CTE) en T-SQL

Pendant l'exécution d'une requête, vous pouvez vous référer à l'ETC comme à n'importe quelle table ordinaire. Elle peut être utilisée dans les instructions SELECT, INSERT, UPDATE et DELETE. Les ETC ne faisaient pas partie du langage SQL original. Ils ont été introduits en tant que nouvelle fonctionnalité dans la définition de la norme SQL en 1999 (SQL 3). En 2005, ils sont devenus disponibles dans SQL Server.

Les exemples de requêtes SQL présentés dans cet article sont basés sur la table olympic_games. Cette table contient les résultats de tous les jeux olympiques, y compris les trois records (or, argent et bronze) pour chaque sport.

Exemple 1 : Un CTE simple

Pour expliquer la syntaxe CTE en T-SQL, écrivons une requête simple. Supposons que nous voulions un rapport sur les pays et le nombre de fois où chaque pays a remporté une médaille d'or lors d'un marathon. Nous pouvons créer un CTE appelé gold_in_marathon pour renvoyer les lignes de médailles d'or dans les marathons masculins et féminins. Dans l'ETC gold_in_marathon, nous n'avons besoin que des colonnes city, year et country.

Notez que tous les CTE commencent par le mot-clé WITH, suivi de parenthèses. Dans les parenthèses, vous écrivez la requête que vous voulez que l'ETC renvoie. Vous trouverez ci-dessous la requête complète. L'ETC est indiqué en caractères gras :

WITH gold_in_marathon AS
(
  SELECT 
    city, 
    year, 
    country
  FROM olympic_games
  WHERE medal_type = 'Gold' 
  AND sport IN ('Marathon Men', 'Marathon Women')
)
SELECT 
  country, 
  count(*) AS gold_medals_in_marathon 
FROM gold_in_marathon
GROUP BY country
ORDER BY gold_medals_in_marathon DESC;

Dans la requête T-SQL ci-dessus, vous pouvez identifier deux requêtes différentes. La première est définie par la clause WITH entre parenthèses : c'est la requête qui définit le contenu de l'ETC. Il s'agit d'une requête SQL normale : vous pouvez introduire toutes les différentes caractéristiques SQL (WHERE, GROUP BY, HAVING, UNION, etc.). La base de données utilise le résultat pour créer une table virtuelle appelée gold_in_marathon. Vous pouvez voir le nom de l'ETC (gold_in_marathon) après la clause WITH.

La deuxième requête est la requête externe. Elle fait référence à l'ETC gold_in_marathon comme n'importe quelle autre table. Lorsque l'exécution de la requête externe est terminée, l'ETC gold_in_marathon est détruit et vous ne pouvez plus y faire référence.

Notez que le CTE ne contient que les colonnes city, year et country de la table olympic_games. Après avoir créé le CTE, la base de données exécute la requête externe qui, à son tour, lit le CTE gold_in_marathon, regroupe les lignes par country et utilise la fonction COUNT() pour obtenir le nombre de médailles d'or de chaque pays. Les résultats de la requête sont présentés ci-dessous :

Exemple 2 : Utilisation d'ETC avec des colonnes renommées (alias) en T-SQL

Dans la requête suivante, nous allons renommer explicitement une colonne dans l'ETC en utilisant un alias. Supposons que nous voulions un rapport avec un classement par pays pour les résultats du marathon (femmes et hommes). Chaque joueur recevra 3 points pour chaque médaille d'or, 2 points pour chaque médaille d'argent et 1 point pour chaque médaille de bronze. Nous allons créer un CTE appelé player_points pour calculer les points de chaque joueur. Comme nous utiliserons une colonne calculée pour les points, nous devrons attribuer un nom à cette colonne à l'aide d'un alias. Voyons la requête complète :

WITH player_points AS
(
  SELECT 
    country,
    winner_name, 
    SUM(
      CASE medal_type 
        WHEN 'Gold' THEN 3
	  WHEN 'Silver' THEN 2
	  WHEN 'Bronze' THEN 1
	END
	)  AS player_total
    FROM   olympic_games
    WHERE sport in ('Marathon Men', 'Marathon Women')
    GROUP BY country,winner_name
)
SELECT 
  country, 
  SUM(player_total) AS country_points 
FROM player_points
GROUP BY country
ORDER BY country_points DESC;

Dans le CTE player_points, nous calculons les points de chaque joueur en utilisant une colonne calculée, qui n'a pas de nom. Nous devons définir un nom pour cette colonne afin de pouvoir y faire référence dans la requête externe.

Une façon de définir un nom est d'utiliser un alias (nous verrons plus tard une autre façon) en utilisant la clause AS. Vous pouvez voir que la colonne a été nommée player_total dans la définition de l'ETC. Dans la requête externe, nous regroupons les lignes par pays afin de calculer le nombre total de points pour chaque pays. Notez que nous utilisons l'expression SUM(player_total) et que nous utilisons à nouveau un alias pour renommer la colonne en country_points. Les résultats de la requête sont présentés ci-dessous :

Avant de clore cette section, j'aimerais vous suggérer de lire l'article What Is a CTE in SQL Server, où vous trouverez de nombreux exemples de requêtes utilisant des CTE en T-SQL. L'article What Is a Common Table Expression (CTE) in SQL ? couvre le sujet des CTE en SQL standard ; ces deux articles sont un bon complément à celui-ci. Si vous souhaitez un cours sur les ETC et les requêtes récursives, je vous suggère à nouveau Requêtes récursives in MS SQL Server.

Exemple 3 : Utilisation de plusieurs ETC dans la même requête T-SQL

Dans cette section, nous allons montrer deux exemples de requêtes qui utilisent plus d'un ETC. Dans le premier exemple, nous utiliserons deux CTE indépendants et la requête principale accédera aux deux.

Supposons que nous souhaitions obtenir un rapport contenant les noms des athlètes olympiques ayant remporté au moins une médaille d'or et une médaille d'argent. Le premier CTE s'appelle gold. Après le nom de l'ETC, vous pouvez voir les noms des colonnes (winner_name et gold_medals) explicitement définis entre parenthèses. C'est l'autre façon de renommer une colonne dans l'ETC.

Le deuxième CTE s'appelle silver et possède deux colonnes : winner_name et silver_medals. Notez que nous n'avons pas placé la clause WITH avant le deuxième CTE. La clause WITH n'est utilisée qu'une seule fois avant la définition du premier CTE. Si nous avons d'autres CTE à définir, il suffit d'insérer une virgule avant de commencer la définition du ou des CTE suivants.

WITH gold(winner_name,gold_medals) AS
(
  SELECT 
    winner_name, 
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Gold'
  GROUP BY winner_name
), 
silver(winner_name,silver_medals) AS
(
  SELECT 
    winner_name,
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Silver'
  GROUP BY winner_name
)
SELECT  
  g.winner_name, 
  g.gold_medals, 
  s.silver_medals
FROM gold g 
JOIN silver s 
ON g.winner_name = s.winner_name;

Dans la requête T-SQL précédente, nous avons créé deux CTE : gold et silver avec winner_name et la quantité de médailles (or ou argent) gagnées par chaque joueur. Ensuite, dans la requête principale, nous avons joint les deux CTE comme s'il s'agissait de tables normales à l'aide d'une clause JOIN. Comme JOIN sans mots-clés fonctionne comme INNER JOIN, seuls les enregistrements pour le même winner_name dans les deux tables seront affichés dans le résultat de la requête. Le résultat est illustré ci-dessous :

Exemple 4 : une requête SQL Server avec un CTE basé sur un autre CTE

Ensuite, nous allons créer deux CTE dans une même requête, mais le second CTE sera basé sur le premier CTE. Supposons que nous voulions une requête pour obtenir le TOP 3 des pays pour le nombre de médailles gagnées lors d'un match olympique. Nous ne voulons pas répéter les pays, donc si la première et la deuxième position sont pour le même pays, nous ne voulons montrer ce pays qu'une seule fois. La requête sera la suivante :

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS quantity_of_medals
  FROM olympic_games
  GROUP BY city, year, country
), 
country_best_game AS
(
  SELECT  
    country, 
    city, 
    year, 
    quantity_of_medals 
  FROM medals m1
  WHERE quantity_of_medals = ( 
    SELECT max(quantity_of_medals) 
    FROM medals m2
    WHERE  m1.country = m2.country
  )
)
SELECT TOP 3 country, 
  city, 
  year, 
  quantity_of_medals 
FROM country_best_game 
ORDER BY quantity_of_medals DESC;

Dans cette requête, nous avons créé un CTE appelé medals avec la quantité de médailles obtenues par chaque pays dans chaque jeu olympique où le pays a remporté au moins une médaille. Ensuite, nous calculerons un deuxième ETC appelé country_best_game (basé sur le premier ETC) ; il contient une ligne pour chaque pays avec le nombre maximum de médailles obtenues dans un seul match. Enfin, dans la requête principale, nous ne sélectionnons que les trois pays ayant remporté le plus grand nombre de médailles en un seul match. Les résultats de la requête sont présentés ci-dessous :

À ce stade, j'aimerais vous suggérer l'article Comment apprendre les expressions de table communes SQL, où vous pouvez lire différentes approches pour apprendre les expressions de table communes. Vous verrez également plusieurs exemples de requêtes utilisant des ETC en SQL standard.

Exemple 5 : Utilisation d'un CTE dans un INSERT T-SQL

En T-SQL, les CTE peuvent également être utilisés dans les instructions UPDATE, INSERT et DELETE. En règle générale, toute commande SQL autorisant l'intégration de SELECT (par exemple une instruction CREATE VIEW ) peut prendre en charge un CTE. Voyons un exemple de INSERT utilisant une expression de table commune en T-SQL.

Supposons que nous ayons une table appelée country_medals_by_game avec les colonnes country, city, game, number_of_medals et delta_with_previous_game. Le contenu de chaque colonne est clair, à l'exception de la colonne delta_with_previous_game. Cependant, nous n'utiliserons pas encore cette colonne, nous l'expliquerons donc plus tard. La méthode INSERT pour remplir le tableau est la suivante :

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
INSERT INTO country_medals_by_game(city,year,country,quantity_of_medals)
SELECT city, year, country, medals_won
FROM medals;

Dans le précédent INSERT, nous avons utilisé un CTE appelé medals. Notez que le INSERT commence par la définition du CTE (vous pouvez définir plusieurs CTE si nécessaire). Une fois l'ETC défini, l'instruction INSERT commence. Dans cet exemple, l'instruction INSERT utilise une instruction SELECT, qui accède à son tour à l'ETC medals défini précédemment.

Exemple 6 : Utilisation d'un CTE dans un UPDATE du serveur SQL

La colonne delta_with_previous_game stocke la différence de médailles remportées par un pays lors de deux Jeux olympiques consécutifs. Si le pays a augmenté le nombre de médailles gagnées, cette colonne aura la différence du nombre de médailles comme valeur positive. Si le pays a remporté moins de médailles que lors du jeu olympique précédent, la colonne aura une valeur négative. L'UPDATE pour remplir la colonne est :

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
UPDATE country_medals_by_game 
SET delta_with_previous_game = (
 SELECT COALESCE(quantity_of_medals - COALESCE(prevgame.medals_won,0),0)
 FROM medals prevgame
 WHERE country_medals_by_game.year = prevgame.year + 4
  AND country_medals_by_game.country = prevgame.country
);

Dans cette page UPDATE, nous avons commencé par une clause WITH pour définir le même CTE medals que nous avons utilisé. Lorsque la section de définition de l'ETC se termine, la déclaration UPDATE commence. Dans la clause SET, nous utilisons une sous-requête pour calculer la différence entre les médailles gagnées lors de deux jeux olympiques consécutifs. Notez que la sous-requête accède à l'ETC medals et que la condition ...

country_medals_by_game.year = prevgame.year + 4

... consiste à faire correspondre une ligne de country_medals_by_game avec la ligne de médailles du jeu olympique précédent (qui s'est déroulé quatre ans plus tôt). Il est intéressant de noter ceci : Pour les pays qui n'ont pas participé à deux jeux contigus, nous fixons la colonne delta_with_previous_game à NULL, ce qui indique que nous ne pouvons pas calculer la différence ; l'utilisation d'un zéro pour cette colonne est incorrecte car elle signifierait que le pays a participé au jeu précédent, ce qui n'est pas le cas.

Exemple 7 : Utilisation des CTE pour Requêtes récursives en T-SQL

Dans SQL Server, il est courant d'avoir des tables représentant des hiérarchies de données (comme employé-gestionnaire, partie-sous-partie ou parent-enfant). Pour parcourir ces hiérarchies dans n'importe quelle direction - de haut en bas ou de bas en haut - SQL Server utilise une construction appelée CTE récursif.

Pour obtenir une hiérarchie de données dans notre base de données Olympic, nous ajouterons une paire de colonnes à la table olympic_games. Supposons que nous voulions identifier les médailles représentant un record du monde. Nous pouvons ajouter une colonne de texte appelée record et lui attribuer la valeur true lorsqu'une médaille est associée à un nouveau record du monde.

De plus, nous savons que chaque record bat un record précédent, nous ajouterons donc une autre colonne appelée previous_record_medal dans laquelle nous mettrons le medal_id du record précédent. Nous disposons maintenant d'une hiérarchie de données à lire à l'aide d'une requête récursive ; montrons une vue partielle de la table olympic_games avec ses nouvelles colonnes :

Supposons que nous voulions obtenir un rapport contenant la liste des records du monde du marathon masculin. Nous pouvons commencer par afficher le record mondial actuel, puis le record immédiatement précédent, et ainsi de suite. Nous aurons besoin d'un CTE récursif pour parcourir la hiérarchie des records du marathon masculin. Il se présente comme suit :

WITH record_history (medal_id, year, winner, country, prev_record_medal_id) AS
(
  SELECT 
    medal_id, 
    year, 
    winner_name, 
    country, 
    previous_record_medal
  FROM olympic_games
  WHERE sport = 'Marathon Men' AND record = 'true'

  UNION ALL

  SELECT 
    og.medal_id,
    og.year,
    og.winner_name,
    og.country, 
    og.previous_record_medal
  FROM olympic_games og 
  JOIN record_history mrh 
  ON og.medal_id = mrh.prev_record_medal_id
)
SELECT * 
FROM record_history;

L'ETC record_history est le résultat de l'ETC UNION ALL. La première requête de l'ETC UNION vise à obtenir le record du monde actuel ; notez la condition record = true. Après UNION ALL, une autre requête permet d'obtenir tous les records précédents du marathon masculin. La clé permettant de relier une ligne de record de médailles à la ligne de record de médailles précédente est la condition :

og.medal_id = mrh.prev_record_medal_id

Les résultats de la requête sont présentés ci-dessous :

Pour éviter une boucle infinie dans un CTE récursif, il existe une limite au nombre d'invocations autorisées. Dans SQL Server, cette limite est définie par défaut à 100. Toutefois, vous pouvez modifier cette limite en utilisant le paramètre MAXRECURSION à la fin de la requête récursive.

Si vous souhaitez approfondir les requêtes récursives, je vous conseille les articles Comment écrire un CTE récursif dans SQL Server et Do it in SQL : Recursive SQL Tree Traversal. Vous y trouverez plusieurs exemples et différentes approches pour expliquer les requêtes récursives.

Les CTE sont une fonctionnalité puissante du langage T-SQL

Dans cet article, nous avons vu comment utiliser les ETC T-SQL pour simplifier les requêtes complexes pour les bases de données SQL Server. Avant de conclure, j'aimerais vous suggérer quelques articles relatifs à SQL Server. Le premier est Top 5 SQL CTE Interview Questions, où vous pouvez trouver des conseils sur la façon de gérer un entretien pour un emploi SQL. Un autre article intéressant est Comment installer Microsoft SQL Server 2019 et SQL Server Management Studio, où vous trouverez de l'aide pour l'installation de SQL Server.

Enfin, j'aimerais vous encourager à consulter le cours Requêtes récursives dans MS SQL Server. Vous pourrez y apprendre à traiter les arbres et les graphiques en T-SQL et à organiser efficacement vos requêtes. Développez vos compétences et augmentez vos atouts !