SQL CTEs : Une vue d'ensemble complète des expressions de table communes

Cet article fournit un guide détaillé de tout ce que vous devez savoir sur les expressions de table communes (CTE), de la définition de base aux requêtes récursives les plus complexes. Si la base de données que vous utilisez prend en charge les CTE, voici tout ce dont vous avez besoin pour en tirer le meilleur parti.

Les expressions de table commune - également appelées CTE, clauses WITH ou requêtes récursives (bien que ce dernier nom soit en fait une application spécifique) - sont une fonctionnalité relativement nouvelle du langage SQL. Elles visent à simplifier les requêtes complexes, en les rendant plus faciles à lire, à comprendre et à maintenir. Vous pouvez commencer par lire ce qu'est une expression de table courante pour avoir une vue d'ensemble. Ensuite, attachez votre ceinture et préparez-vous, car cet article vous conduira à travers toutes les ressources CTE (cours, exercices, exemples et explications) disponibles sur notre LearnSQL.fr site web.

Les ETC sont utiles pour décomposer des requêtes complexes et volumineuses en parties plus petites et plus compréhensibles, tout comme les vues, les sous-requêtes et les tables temporaires. Et, tout comme les vues, les sous-requêtes et les tables temporaires, vous pouvez combiner les ETC pour obtenir un résultat final. Toutefois, l'avantage des ETC est qu'ils ne génèrent pas d'objets persistants ou occupant de l'espace dans la base de données (comme le font les vues et les tables temporaires). De plus, ils sont plus faciles à lire et à interpréter que les sous-requêtes.

Si vous suivez notre Requêtes récursives vous aurez une vue d'ensemble complète des ETC SQL. Vous y apprendrez la syntaxe des CTE et les différentes façons de les utiliser. Vous recevrez plus de 100 exercices interactifs que vous pourrez exécuter librement sur notre base de données en ligne. Tout ce dont vous avez besoin, c'est d'une connaissance de base du langage SQL et d'un navigateur avec une connexion Internet.

Syntaxe des CTE

La forme générale d'un CTE SQL commence par une clause WITH suivie d'une définition de requête (une instruction SELECT normale) à laquelle est attribué un nom. Cette définition est suivie d'une instruction SELECT qui fait référence à la requête CTE par le nom qui lui a été attribué, comme s'il s'agissait d'une table ou d'une vue. Voici un exemple :

WITH cte_name AS (cte_query_definition)
SELECT *
FROM cte_name;

La clause WITH dirige la requête et contient une sous-requête à laquelle un nom est attribué. La requête principale (située après la clause WITH ) est une instruction SELECT normale qui peut utiliser la sous-requête nommée autant de fois que nécessaire.

Lorsque vous exécutez une requête contenant des CTE, le moteur de la base de données exécute d'abord la ou les requêtes CTE, en enregistrant les résultats de la requête CTE jusqu'à ce qu'il ait fini d'exécuter l'ensemble de la requête ; les résultats CTE peuvent être considérés comme des résultats intermédiaires. Il utilise ces résultats intermédiaires comme des tables pour composer le résultat final. Il renvoie ensuite le résultat final et rejette les résultats intermédiaires qu'il a générés précédemment.

Un exemple d'ETC de base

Prenons un exemple simple. Vous disposez d'un schéma comportant trois tables : employee, divisionet payment. Vous devez obtenir une liste des salariés avec leur rémunération maximale et la division à laquelle chaque salarié appartient. Vous pouvez créer un CTE qui détermine d'abord la rémunération maximale de chaque salarié dans une sous-requête appelée max_paid. Ensuite, dans la requête principale SELECT, vous joignez max_paid avec employee et division pour obtenir le résultat final :

WITH max_paid (employee_id, max_payment) AS (
	SELECT
		emp.employee_id,
		MAX(pay.payment) AS max_payment
	FROM
		employee AS emp
INNER JOIN payment AS pay ON
pay.employee_id = emp.employee_id
	GROUP BY
		emp.employee_id
	)
SELECT
	emp.employee_id,
	emp.name AS employee_name,
	div.name AS division_name,
	mp.max_payment
FROM max_paid mp
	INNER JOIN employee AS emp ON
		emp.employee_id = mp.employee_id
	INNER JOIN division AS div ON
		div.division_id = emp.division_id;

Lorsque la base de données exécute cette requête, elle crée d'abord un ensemble de données temporaire avec les résultats de la requête CTE et le nomme max_paid. Ensuite, dans le SELECT externe, elle utilise max_paid comme s'il s'agissait d'une table. Il joint max_paid avec les véritables tables du schéma (employee et division) pour construire le résultat final.

Pour une meilleure compréhension de la syntaxe SQL CTE, je vous recommande ces 5 exemples pratiques de clauses WITH.

Plusieurs CTE dans une même requête

Plusieurs CTE peuvent être utilisés dans la même requête. Pour ce faire, il suffit de séparer chaque définition d'ETC par une virgule :

WITH cte1 AS (
	SELECT ...
	FROM ...
),
cte2 AS (
	SELECT ...
	FROM ...
)
SELECT ...
FROM cte1 JOIN cte2, ...

Chaque CTE peut utiliser d'autres CTE précédemment définis dans la même requête ; c'est ce qu'on appelle l'imbrication. Cela permet aux CTE de décomposer des requêtes complexes et volumineuses en sous-requêtes plus petites (et plus faciles à gérer). Vous pouvez résoudre progressivement chaque partie d'un problème jusqu'à ce qu'il soit simplifié en une seule SELECT.

L'exemple suivant utilise deux CTE imbriqués qui obtiennent des informations à partir d'une table appelée sales. Le premier CTE, SalesByDayRegionobtient les totaux des ventes par jour et par région. Le second, SalesByDays'appuie sur SalesByDayRegion pour obtenir les totaux des ventes pour chaque jour. Le dernier SELECT joint les deux CTE pour calculer le pourcentage des ventes de chaque région par rapport aux totaux quotidiens.

Voici la requête :

WITH SalesByDayRegion AS (
	SELECT
		day,
		region,
		SUM(amount) AS AmountByDayRegion
	FROM 	Sales
	GROUP BY day, region
	),
	SalesByDay AS (
	SELECT
		day
		SUM(GroupedAmount1) AS AmountByDay
	FROM 	Sales
	GROUP BY day
	)
SELECT
	sdr.day,
	sdr.region,
	AmountByDayRegion
	AmountByDayRegion / AmountByDay AS Percentage
FROM SalesByDayRegion sdr 
INNER JOIN SalesByDay sd
ON sdr.day = sd.day;

Requêtes récursives

WITH Les clauses - c'est-à-dire les ETC - permettent à SQL d'implémenter la récursivité. Il n'est donc pas nécessaire de le faire dans d'autres langages de programmation, ce qui permet d'améliorer l'efficacité de l'exécution des requêtes.

Dans tout langage de programmation, la récursivité est mise en œuvre par l'appel répété d'une fonction ou d'une procédure jusqu'à ce qu'une condition de terminaison soit remplie. Chaque itération récursive ajoute ou modifie les données résultant de l'itération précédente et fournit ses résultats à l'itération suivante. Lorsque la condition de terminaison est remplie, elle renvoie le résultat final.

En SQL, la récursivité est possible car les CTE peuvent se référencer eux-mêmes. Dans la plupart des dialectes SQL (à l'exception de Transact SQL), le mot RECURSIVE est utilisé après WITH pour indiquer une récursion. Pour en savoir plus sur ce qu'est un ETC récursif, cliquez ici.

Les ETC récursifs sont composés des éléments suivants : un membre d'ancrage, un membre récursif, un contrôle de terminaison et une invocation.

Le membre d'ancrage établit le point de départ de la récursivité. Cette partie de l'ETC doit pouvoir être résolue sans s'invoquer elle-même. Le membre récursif utilise les résultats d'une itération précédente pour traiter ou ajouter des données aux résultats, obtenant ainsi un nouvel ensemble de résultats pour l'itération suivante ou, lorsque la condition de terminaison est remplie, un ensemble de résultats pour le membre d'invocation.

Le membre d'ancrage et le membre récursif sont combinés à l'aide d'un opérateur UNION. Cela signifie que les ensembles de résultats des deux membres doivent avoir la même structure de colonnes : leurs colonnes doivent être dans le même ordre et avoir les mêmes types de données.

Dans l'exemple suivant, nous utilisons un ETC récursif pour calculer la factorielle des nombres 1 à 5 :

WITH RECURSIVE factorial(n, factorial) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, (n +1) * factorial FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

Dans cet exemple, le membre d'ancrage de l'ETC récursif est :

SELECT 1, 1

Et le membre récursif, ainsi que la condition de terminaison, est le SELECT suivant :

SELECT n + 1, (n +1) * factorial 
FROM factorial 
WHERE n < 5

L'invocation est simplement une SELECT * de l'ETC factorial. Notez que cette SELECT traite factorial comme s'il s'agissait d'une table. Si nous ne voulons voir que les trois premières lignes du résultat du calcul factoriel, nous pouvons l'ajouter à l'invocation :

SELECT * 
FROM factorial 
WHERE n <= 3;

Cet exemple est extrêmement simple. Les CTE récursifs peuvent être utilisés pour répondre à des besoins plus complexes, par exemple pour parcourir un ensemble de données représentant une structure arborescente. Vous pourriez l'utiliser pour explorer l'organigramme d'une entreprise, comme nous le verrons dans l'exemple ci-dessous.

Si vous souhaitez approfondir la récursivité en SQL, consultez notre cours en ligneRequêtes récursives .

Avantages des expressions de table communes

Les expressions de table communes présentent de nombreux avantages, qu'il s'agisse d'améliorer la compréhension des requêtes ou de parcourir des structures de données hiérarchiques. Vous pouvez même faire des dessins avec SQL. Pour commencer, je vous suggère de lire les articles déjà mentionnés, CTEs explained with examples et 5 practical examples of CTEs in SQL. Ensuite, lisez la suite pour voir quand il est utile et efficace d'utiliser les ETC.

Faciliter l'organisation et la clarté des requêtes

Les ETC vous permettent de résoudre des requêtes complexes en les décomposant en plusieurs requêtes plus petites, plus faciles à gérer et à lire. En outre, la possibilité d'utiliser des ETC imbriqués vous permet d'avancer progressivement vers une solution.

Un ETC est équivalent à une sous-requête, mais avec un avantage très important : vous pouvez donner un nom à un ETC et l'utiliser à plusieurs reprises dans différentes parties de votre requête. C'est comme si vous créiez une vue, à la différence près que la vue est un objet de base de données pérenne. L'ETC n'existe que le temps de l'exécution de la requête, puis il disparaît sans laisser de trace. L'exemple suivant montre comment créer un CTE pour obtenir des données récapitulatives à partir d'une table, puis utiliser ce CTE pour obtenir des données récapitulatives à partir d'une table. sales puis d'utiliser cet ETC pour effectuer différentes requêtes :

WITH TotalSalesByCategory AS (
	SELECT customer_category, SUM(amount) AS total_sales
	FROM sales
	GROUP BY customer_category
	)
SELECT 'big customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('A', 'B', 'C')
UNION
SELECT 'medium customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('D', 'E', 'F')
UNION
SELECT 'small customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('G', 'H', 'I');

Apprenez à désencombrer vos requêtes dans ces articles :

• Comment organiser les requêtes SQL à l'aide d'ETC
• Améliorer la lisibilité des requêtes avec les expressions de table communes

Agrégations à plusieurs niveaux

Nous avons vu différentes options pour inclure plusieurs CTE dans la même requête. Ce faisant, vous multipliez la puissance des CTE pour réduire la complexité d'une requête et améliorer sa lisibilité. Les options d'utilisation de plusieurs ETC en SQL se résument à ce qui suit :

1. Plusieurs ETC indépendants.
2. ETC imbriqués.
3. Utiliser un ETC récursif avec des ETC non récursifs.

Apprenez à tirer parti des ETC combinés grâce aux articles suivants :

• Comment écrire plusieurs ETC en SQL
• Comment utiliser 2 CTE dans une seule requête SQL
• Peut-on utiliser plusieurs instructions WITH en SQL ?

Interroger des données hiérarchiques

Les données hiérarchiques sont caractérisées par une relation ordonnée entre leurs éléments. En général, les données hiérarchiques sont organisées en niveaux ; vous pouvez déterminer quelles données se trouvent "au-dessus" ou "au-dessous" d'autres éléments d'information.

Dans les bases de données relationnelles, des relations hiérarchiques peuvent être établies au sein d'une table en faisant en sorte que chaque ligne contienne un champ qui lui permet d'être reliée à une ligne de niveau supérieur au sein de la même table. De cette manière, il est possible de construire des structures de données hiérarchiques avec des lignes "parents" et des lignes "enfants", qui sont généralement représentées visuellement sous la forme d'une structure arborescente.

Les CTE sont également utilisés pour interroger des données hiérarchiques. Pour en savoir plus sur l'interrogation de données hiérarchiques en SQL, cliquez ici.

Un exemple typique de structure de données hiérarchiques est l'organigramme d'une entreprise, où les lignes de la table contiennent des données sur les employés et où chaque employé se réfère à son patron. Pour plus de détails, lisez comment trouver tous les employés de chaque responsable en SQL.

S'amuser à dessiner des images avec les CTE

Vous cherchez une façon amusante d'écrire des requêtes ingénieuses en SQL ? Vous ne pouvez pas réaliser de grandes œuvres d'art en SQL, mais vous pouvez faire des dessins schématiques en utilisant des CTE récursifs.

Nous avons mentionné précédemment que vous pouviez utiliser la récursivité pour parcourir des structures de données arborescentes hiérarchiques, telles qu'un organigramme. Vous serez peut-être surpris d'apprendre que vous pouvez dessiner et parcourir des structures hiérarchiques avec SQL. Pour le savoir, lisez cet article sur l'utilisation de SQL pour dessiner un arbre de Noël plutôt unique.

Alternatives aux CTE SQL

Si l'on fait abstraction de la récursivité (qui, en SQL, est une vertu unique des ETC), le reste des avantages offerts par les ETC peut être obtenu à l'aide de sous-requêtes, de vues et de tables temporaires. Comparons chacun de ces éléments à un ETC.

ETC et sous-requêtes

Les sous-requêtes en SQL doivent être écrites entre parenthèses et incluses dans le reste des éléments de la requête principale. Cela signifie qu'elles n'apportent pas beaucoup de clarté à la requête. Les CTE sont écrits séparément, au début de la requête et dans leur propre clause WITH. Cela facilite grandement la lecture de la requête. Si vous n'êtes pas intéressé par le fonctionnement interne de l'ETC, vous pouvez ignorer la clause WITH et ne lire que le SELECT principal pour comprendre les résultats.

Vous pouvez obtenir plus de détails sur les différences entre les CTE et les sous-requêtes en lisant cet article qui décrit en détail les différences entre les CTE et les sous-requêtes.

Par ailleurs, les sous-requêtes ne sont pas réutilisables. Si vous souhaitez utiliser le résultat d'une sous-requête dans différentes parties d'une requête, vous devrez le réécrire à chaque fois. En revanche, les CTE peuvent être réutilisés autant de fois que souhaité dans la requête principale SELECT ou dans d'autres CTE de la même requête. Cela améliore la facilité de lecture et les performances de la requête ; chaque CTE n'est exécuté qu'une seule fois et ses résultats sont disponibles tout au long de la requête.

Enfin, il est bon de noter que les sous-requêtes contenues dans la clause FROM portent un alias qui est utilisé dans le reste de la requête pour accéder à leurs résultats. Mais les autres sous-requêtes incluses dans la liste des colonnes d'une clause SELECT ou WHERE n'ont pas d'alias. Les CTE portent toujours un nom qui est utilisé pour les référencer dans le reste de la requête.

CTE et vues

Les vues ont beaucoup en commun avec les CTE, mais il existe une différence cruciale entre elles. Les vues restent des objets dans la base de données jusqu'à ce que quelqu'un les supprime. Les CTE n'existent que dans le contexte de leur requête. Une fois la requête exécutée, l'ETC disparaît. Pour en savoir plus, lisez cet article sur les différences entre les CTE et les vues.

ETC et tables temporaires

Les tables temporaires présentent également de nombreuses similitudes avec les ETC. Le cycle de vie d'une table temporaire se termine avec la session de base de données au cours de laquelle la table temporaire a été créée, de sorte que vous n'avez pas à vous soucier de laisser des objets inutiles encombrer le schéma de la base de données. Mais il existe une différence fondamentale : la création de la table temporaire doit être effectuée dans une commande distincte de la requête qui l'utilise. Vous ne pouvez pas créer une table temporaire à l'intérieur d'une requête SELECT; si vous le faisiez, il s'agirait d'une sous-requête et non d'une table temporaire.

Si vous créez une table temporaire et que vous vous rendez compte que vous devez y ajouter une colonne ou modifier quoi que ce soit d'autre, vous devez la supprimer et la recréer. Avec un CTE, il vous suffit de modifier sa définition et de réexécuter la requête.

Les ETC dans différents SGBD

Les ETC sont apparus dans la norme SQL de 1999. Leur implémentation dans chacun des systèmes de gestion de base de données (SGBD) les plus populaires est la suivante :

• PostgreSQL : version 8.4, juillet 2009
• MS SQL Server : version 2012, mai 2012
• Oracle Database : version 9.2, juin 2002
• MySQL : version 8.0, avril 2018
• MariaDB : version 10.2.2, mai 2018

Google BigQuery prend également en charge les CTE. Comme il fonctionne sur le cloud en tant qu'entrepôt de données entièrement géré, il suffit de savoir qu'il prend actuellement en charge les CTE.

La syntaxe SQL CTE est la même pour tous les SGBD. La seule exception est MS SQL Server (T-SQL), où la syntaxe d'écriture d'un CTE récursif ne nécessite pas l'utilisation du mot-clé RECURSIVE. Pour en savoir plus sur les ETC dans MS SQL Server, lisez la section consacrée à la récursivité dans SQL Server et consultez les exemples d'ETC dans SQL Server.

Mise en pratique de vos connaissances sur les ETC SQL

Tout au long de cet article, vous avez appris tout ce qu'il faut savoir sur les expressions de table communes en SQL. Vous devez maintenant mettre ces connaissances en pratique. La meilleure façon de le faire est de suivre l'un de nos cours spécifiques sur les ETC et les requêtes récursives, où vous trouverez des tonnes d'exercices interactifs.

Ces cours s'adressent aux étudiants, aux analystes de bases de données intermédiaires et aux data scientists débutants. La seule exigence est une certaine maîtrise du langage SQL de base, comme les déclarations SELECT, JOINs, GROUP BY, etc.

Vous pouvez opter pour notre cours standard Requêtes récursives, qui ne tient pas compte des dialectes SQL, ou choisir un cours spécifique pour le SGBD de votre choix :

• Requêtes récursives en MS SQL Server
• Requêtes récursives en MySQL 8
• Requêtes récursives en PostgreSQL

Aucun de ces cours ne vous oblige à utiliser vos propres bases de données. Notre plateforme vous fournit une base de données prête à l'emploi, avec son schéma et ses informations. Ces environnements de pratique sont sûrs ; vous n'avez pas à vous inquiéter de faire des erreurs, car il n'y a aucun risque de casser quoi que ce soit.

Consultez ces autres ressources précieuses pour renforcer votre connaissance des requêtes WITH:

• Les 5 meilleures questions d'entretien sur les CTE SQL
• 11 Exercices SQL sur l'expression de table commune

L'importance de connaître les CTE en SQL

La maîtrise des expressions de table communes (CTE) et des requêtes récursives de SQL commence par la compréhension des bases et se poursuit par des applications plus complexes. Nos ressources sélectionnées rendent l'apprentissage des CTE à la fois agréable et pratique.

Pour maintenir vos compétences en matière d'ETC, prenez l'habitude de vous entraîner quotidiennement. Lorsque vous êtes confronté à des requêtes complexes, demandez-vous si l'utilisation d'un ou de plusieurs ETC ne pourrait pas simplifier la tâche en la divisant en parties plus petites et plus faciles à gérer. Bientôt, vous vous retrouverez à utiliser les ETC de manière routinière dans vos tâches SQL.

En outre, je vous recommande de consulter notre coursRequêtes récursives pour approfondir votre compréhension des ETC SQL. Vous disposerez ainsi des outils nécessaires pour vous attaquer en toute confiance aux requêtes les plus complexes. Bon apprentissage !