CTE vs. sous-requête en SQL : Quelle est la différence ?

Qu'est-ce qu'une expression de table commune (CTE) ? Sont-elles identiques aux sous-requêtes ? Quand avez-vous besoin d'utiliser des CTE ? Cet article examine les similitudes et les différences entre les CTE et les sous-requêtes.

Lorsque je présente les Common Table Expressions à un étudiant, sa première réaction est la suivante : "Ce n'est qu'une sous-requête ! Pourquoi dois-je apprendre cela ?". Répondons à cette question en examinant ce que vous pouvez faire avec une sous-requête SQL et quels sont les avantages supplémentaires de l'utilisation d'un CTE.

Si, après avoir lu l'article, vous décidez d'apprendre à utiliser les CTE SQL, LearnSQL.fr propose un cours complet sur les Common Table Expressions qui couvre le sujet en profondeur. En commençant par des exemples simples, vous aborderez des concepts plus difficiles tels que le traitement des arbres et des graphiques. Chaque sujet est accompagné d'explications et d'exemples étape par étape. Avec 114 exercices interactifs, vous aurez beaucoup de pratique. Vous aurez probablement besoin d'environ 18 heures pour terminer le cours.

Qu'est-ce qu'une sous-requête ?

Une sous-requête est une requête imbriquée dans la requête principale ; la meilleure façon de l'expliquer est de regarder un exemple. Tous les exemples de cet article utilisent SQL Server ; la syntaxe peut être légèrement différente dans d'autres dialectes SQL.

Supposons que la direction veuille offrir une remise à tous les clients dont les achats annuels sont supérieurs à la moyenne de tous les clients. La requête permettant d'extraire une liste de ces clients pourrait ressembler à ceci :

SELECT 
  account_no, 
  name 
FROM customers 
WHERE annual_purchases >
  (SELECT AVG(annual_purchases) FROM customers);

La requête principale énumère les clients et la sous-requête calcule la moyenne des achats annuels de tous les clients. La sous-requête n'a pas besoin de nom (sauf si vous travaillez avec PostgreSQL).

Si vous n'êtes pas familiarisé avec les sous-requêtes, vous pouvez consulter le cours LearnSQL.fr's SQL pour les débutants, qui comporte une section consacrée aux sous-requêtes. Pour en savoir plus sur les sous-requêtes, consultez notre Guide du débutant pour les sous-requêtes SQL. Comment pratiquer les sous-requêtes SQL vous donne quelques idées sur la manière de mettre ces concepts en pratique.

Et que sont les CTE ?

Les Common Table Expressions sont des ensembles de résultats nommés qui sont définis au début d'une requête et auxquels la requête peut accéder comme s'il s'agissait de tables. Supposons que nous souhaitions comparer les salaires des employés au salaire moyen de leur fonction. La requête pourrait ressembler à ceci :

WITH avg_salary AS (
  SELECT 
    role, 
    avg(salary) AS average 
  FROM employee 
  GROUP BY role
)
SELECT 
  employee.role, 
  name, 
  salary, 
  avg_salary
FROM employee 
JOIN avg_salary ON avg_salary.role = employee.role
ORDER BY role, name

Les CTE sont introduits par le mot-clé WITH, et les résultats sont stockés dans une table temporaire nommée. Dans cet exemple, les résultats de l'ETC sont stockés dans la table avg_salary, qui est utilisée par la requête principale pour obtenir le salaire moyen pour chaque fonction.

En fait, les ETC simples comme celui-ci pourraient tout aussi bien être écrits comme des sous-requêtes. L'article "Qu'est-ce qu'un CTE ?" explique les CTE plus en détail.

Quelles sont les différences entre les ETC et les sous-requêtes ?

Je commencerai par énumérer brièvement les principales différences, puis j'examinerai certaines d'entre elles plus en détail.

• Les CTE sont définis au début de la requête, alors que les sous-requêtes sont définies en ligne.
• Les ETC doivent toujours être nommés. Seul PostgreSQL insiste pour que les sous-requêtes aient un nom.
• Les ETC peuvent être utilisés de manière récursive. Je l'expliquerai plus loin dans l'article.
• Les CTE sont beaucoup plus lisibles que les sous-requêtes lorsque vous écrivez un rapport complexe.
• Un CTE peut être utilisé plusieurs fois dans une requête, alors qu'une sous-requête ne peut être utilisée qu'une seule fois. Cela peut rendre la définition de la requête beaucoup plus courte, mais ne se traduit pas nécessairement par une amélioration des performances.
• Les sous-requêtes peuvent être utilisées dans une clause WHERE en conjonction avec les mots-clés IN ou EXISTS, ce qui n'est pas possible avec les CTE.
• Les sous-requêtes peuvent être utilisées pour extraire un seul élément de données d'une table afin de mettre à jour une valeur dans une autre table.

Certaines fonctionnalités sont limitées aux sous-requêtes. Je donnerai deux exemples dans lesquels les sous-requêtes ne peuvent pas être remplacées par des CTE. Dans le premier exemple, la sous-requête fait partie de la clause WHERE. Dans le second, elle est utilisée pour récupérer une valeur unique dans une table afin d'en mettre une autre à jour. Comme je l'ai mentionné plus haut, SQL ne permet pas d'utiliser des CTE pour ces tâches.

Sous-requêtes uniquement : Utilisation de sous-requêtes dans WHERE

Dans le premier exemple, une banque conserve les détails de toutes les transactions de la journée dans une table appelée daily_trans. Les données de cette table comprennent un numéro de compte, un code de transaction et un montant.

La base de données comporte également une table appelée transaction_typesSes colonnes comprennent le code de transaction et un indicateur appelé debit_credit, qui vaut 1 pour les types de transaction qui créditent le compte du client et 2 pour ceux qui débitent le compte.

Si la banque souhaite obtenir la liste de toutes les opérations de crédit de la journée, la requête pourrait ressembler à ceci :

SELECT 
  account_no, 
  tran_code, 
  amount
FROM daily_trans
WHERE tran_code IN
  (SELECT tran_code 
   FROM transaction_types
   WHERE debit_credit = 1);

Ce type de requête ne peut être écrit qu'en utilisant une sous-requête. Vous ne pouvez pas remplacer la sous-requête par un CTE.

Sous-requêtes uniquement : Utilisation de sous-requêtes dans UPDATE

Dans le deuxième exemple, la même banque possède une table appelée customerdont les colonnes comprennent un numéro de compte, un nom de client et le numéro d'employé de la personne désignée pour l'assistance à la clientèle.

La banque a procédé à une redistribution des responsabilités et souhaite réaffecter la personne chargée de l'assistance à certains clients. Pour ce faire, elle a créé une table appelée reassignments qui contient le numéro d'identification de l'ancien employé du service d'assistance et le numéro d'employé de l'employé du service d'assistance qui reprendra ses responsabilités.

Pour effectuer les réaffectations, nous pourrions écrire une requête comme celle-ci :

UPDATE customer 
SET support_person =
  (SELECT new_employee 
   FROM reassignments
   WHERE old_employee = customer.support_person);

Les CTE rendent une requête complexe plus lisible

Pour illustrer la manière dont les ETC peuvent faciliter la compréhension, prenons une requête qui utilise plusieurs sous-requêtes et recodons-la à l'aide d'ETC.

Supposons qu'un magasin vende trois types de produits : des livres, de la musique et des vidéos. Le gérant souhaite savoir combien chaque client a acheté dans chaque catégorie.

Le rapport peut ressembler à ceci :

Voici une requête qui utilise des sous-requêtes pour produire le rapport :

SELECT 
	customer, 
	sum(purchases) AS Total, 
	total_books AS Books, 
	total_music AS Music, 
	total_videos AS Videos
FROM sales
JOIN 
	(SELECT account_no, sum(purchases) AS total_books FROM sales 
	WHERE product_type = 'Books'
	GROUP BY account_no) books
ON books.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_music FROM sales 
	WHERE product_type = 'Music'
	GROUP BY account_no) music
ON music.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_videos FROM sales 
	WHERE product_type = 'Videos'
	GROUP BY account_no) videos
ON videos.account_no = sales.account_no
GROUP BY customer
ORDER BY customer

Comme vous pouvez le voir, c'est assez compliqué. Et il est difficile à suivre si quelqu'un d'autre doit y apporter des modifications ultérieurement.

Voyons maintenant à quoi pourrait ressembler cette requête si nous la réécrivions à l'aide de Common Table Expressions :

WITH books AS (
  SELECT 
    customer, 
    sum(purchases) AS total_books 
  FROM sales 
  WHERE product_type = 'Books'
  GROUP BY customer
),

music AS (
  SELECT 
    customer, 
    sum(purchases) AS total_music 
  FROM sales 
  WHERE product_type = 'Music'
  GROUP BY customer
),

videos as (
  SELECT 
    customer, 
    sum(purchases) AS total_videos 
  FROM sales 
  WHERE product_type = 'Videos'
  GROUP BY customer
)

SELECT 
  customer, 
  sum(purchases) AS Total, 
  total_books AS Books, 
  total_music AS Music, 
  total_videos AS Videos
FROM sales
JOIN books ON books.customer = sales.customer
JOIN music ON music.customer = sales.customer
JOIN videos ON videos.customer = sales.customer
GROUP BY customer
ORDER BY customer

La plupart des gens trouveront cette deuxième version beaucoup plus facile à comprendre, bien que les deux requêtes produisent exactement les mêmes résultats.

Qu'est-ce qu'une ETC récursive ?

J'ai dit plus haut que seuls les CTE peuvent être récursifs. Qu'est-ce qu'une requête récursive ? Les requêtes récursives vous permettent de naviguer dans des données hiérarchiques et de produire des rapports adaptés aux données de type arbre et graphique. Voici quelques exemples de données hiérarchiques :

• Dans une organisation, un employé peut rendre compte à un sous-directeur ; le sous-directeur rend compte à un directeur, et le directeur rend compte au directeur général.
• Dans le domaine de la fabrication, un produit peut être constitué de plusieurs composants. Chaque composant peut également être constitué de nombreux sous-composants, et les sous-composants peuvent être fabriqués à partir de diverses matières premières.

Prenons un exemple. Un glacier propose plusieurs produits sur sa carte. Chaque produit peut être composé de plusieurs ingrédients : un banana split est composé de bananes, de sauce au chocolat et de crème glacée. Mais la sauce au chocolat contient également plusieurs ingrédients. Il peut s'agir de cacao en poudre, de sucre et d'autres éléments.

Le propriétaire veut une liste complète de chaque plat du menu, suivie de tous ses ingrédients. Une partie de la liste peut ressembler à ceci :

Dans la base de données, nous avons deux tables :

• La table Item contient la liste de chaque élément du menu et de chaque ingrédient.
• La table Bill_of_materials contient les liens entre chaque élément et ses ingrédients.

La table Items contient ces informations :

Voici quelques exemples d'entrées dans la Bill_of_materials Voici quelques exemples d'entrées dans la table. La colonne item_id contient un lien vers l'élément parent du tableau, tandis que contient un lien vers l'un de ses ingrédients. Items tandis que component_id contient un lien vers l'un de ses ingrédients. La première entrée indique donc que l' élément 10 : Cacao est un ingrédient de l'élément 9 : Sauce au chocolat.

Voici la requête récursive utilisée pour parcourir ces informations. La requête a été écrite en SQL Server ; d'autres dialectes seraient légèrement différents.

WITH menu_ingredients (id, path, description, item_id)
AS (
  SELECT 
CAST (id AS bigint), 
CAST (description as varchar (255)), 
CAST ('' AS varchar(40)),
CAST (id AS bigint)
   FROM items 
   WHERE description = 'Menu'
   
   UNION ALL
   
   SELECT
CAST (bom.component_id AS bigint), 
CAST (m.path + ' > ' + i.description AS varchar(255)),
i.description,
CAST (bom.item_id AS bigint)
   FROM menu_ingredients m, bill_of_materials bom
   JOIN items i
   ON i.id = bom.component_id
   WHERE bom.item_id = m.id
)

SELECT 
  path, 
  description 
FROM menu_ingredients
ORDER BY path

Une explication complète de ce qui se passe ici n'entre pas dans le cadre de cet article, mais j'expliquerai rapidement les principes de base. En bref, lorsqu'une ligne est ajoutée à l'ensemble de résultats d'une requête récursive, elle peut "voir" la ligne précédente et l'utiliser pour récupérer un élément d'information qui peut être utilisé pour trouver la ligne suivante. Cette requête commence par récupérer l'entrée de premier niveau de la nomenclature : le menu lui-même. À partir de là, elle peut parcourir toutes les lignes "enfants", c'est-à-dire les ingrédients dont il est composé. Et chaque ingrédient peut récupérer ses propres lignes enfants, s'il en a. Pour une explication plus détaillée de la récursivité, consultez la rubrique Qu'est-ce qu'une ETC récursive en SQL ? Et comme je l'ai mentionné précédemment, les requêtes récursives sont entièrement couvertes dans le cours Common Table Expressions de LearnSQL.fr.

CTE ou sous-requête

En résumé, choisissez un CTE lorsque

• Vous voulez rendre une requête complexe plus lisible.
• Vous devez utiliser une requête récursive.

Choisissez une sous-requête dans les cas suivants

• Vous utilisez les mots-clés de la clause WHERE IN ou EXISTS pour reprendre les critères de sélection d'une autre table.
• Vous souhaitez sélectionner un seul élément de données d'une autre table comme nouvelle valeur d'un champ dans une instruction UPDATE.

Dans toutes les autres circonstances, c'est à vous de choisir : vous pouvez utiliser la clause qui vous convient le mieux.

Et si vous avez besoin de vous entraîner avec les CTE ou les sous-requêtes, vous pouvez essayer le site web Exercices Pratiques de SQLqui contient des centaines d'exercices interactifs pour vous aider à consolider vos connaissances.

Bon apprentissage !