CTE dans MySQL et son utilisation

Les expressions de tables communes (CTE) dans MySQL offrent encore plus de polyvalence à ce système de gestion de base de données populaire. Découvrez comment utiliser les CTE pour simplifier les requêtes complexes et mettre en œuvre la récursivité.

Tous les langages de programmation permettent de simplifier les problèmes en les décomposant en parties qui peuvent être résolues individuellement. Les utilisateurs peuvent unifier les résultats partiels pour obtenir un seul résultat final. Grâce à ce que l'on appelle les expressions de table communes (CTE), MySQL fait désormais exception. Une CTE MySQL vous permet d'attribuer un nom à un ensemble de résultats temporaire, puis de faire référence à cet ensemble de résultats par son nom (comme s'il s'agissait d'une table ou d'une vue) dans les instructions SELECT, INSERT, UPDATE, ou DELETE.

Les CTE font partie de la norme SQL depuis 1999. Toutefois, leur mise en œuvre effective dans les dialectes de chaque système de gestion de base de données relationnelle (SGBDR) s'est faite progressivement, au fur et à mesure que les utilisateurs commençaient à trouver cette nouvelle fonctionnalité utile. MySQL a été l'un des derniers SGBDR populaires à intégrer la prise en charge des CTE. Ce n'est que dans la version 8, publiée en avril 2018, que la prise en charge des CTE est apparue dans MySQL.

Comme nous le verrons plus loin, l'une des principales utilisations de le CTE de MySQL est la mise en œuvre de la récursivité. Vous pouvez suivre notre cours interactif sur les requêtes récursives dans MySQL si vous souhaitez vous plonger directement dans ce sujet. Dans ce cours, vous obtiendrez toutes les connaissances nécessaires pour maîtriser les requêtes récursives dans MySQL 8. Tout ce dont vous avez besoin, c'est d'un navigateur Web, d'une connexion Internet et d'une connaissance du langage SQL de base (y compris les clauses JOIN et GROUP BY ). Il y a 114 exercices interactifs avec des conseils et de l'aide en ligne pour vous permettre d'affiner vos compétences.

Pour l'instant, concentrons-nous sur les bases des CTE MySQL, en commençant par la syntaxe la plus simple.

Syntaxe de base d'un CTE MySQL

La syntaxe d'écriture d'un CTE MySQL commence par le mot WITH, suivi du nom du CTE et d'une liste des colonnes que le CTE retournera. Cette liste n'est pas obligatoire, puisqu'elle peut être définie par le résultat de la requête qui définit le CTE. Cette requête doit être écrite entre parenthèses après le mot AS.

La syntaxe de base des expressions de table commune dans MySQL peut être résumée comme suit :

WITH cte_name (column_list) AS (
    query
) 
SELECT * 
FROM cte_name;

Après la parenthèse entourant la requête CTE, nous avons une instruction SQL conventionnelle qui fait référence à la CTE par son nom, comme s'il s'agissait d'une vue. Cette instruction peut être une commande DML (Data Manipulation Language) telle que SELECT, INSERT, DELETE ou UPDATE.

Si vous souhaitez obtenir un guide de référence rapide sur la syntaxe ou les commandes MySQL, mettez en signet notre Aide-mémoire MySQL gratuit. Elle vous aidera à vous rafraîchir la mémoire sur les expressions courantes (et moins courantes) de MySQL.

Un CTE dans MySQL qui renvoie une seule valeur

Voyons un exemple de CTE créé dans une base de données MySQL. Nous allons utiliser des informations sur les ventes et montrer la différence entre le montant de chaque vente et le montant moyen de la vente.

Nous disposons d'une table sales contenant des informations détaillées sur les ventes. Pour cet exemple, nous nous intéressons à trois colonnes : customer_id sale_id et amount. Voici quelques exemples de données :

Nous allons créer un CTE appelé sales_average qui renverra une seule valeur : la moyenne de toutes les valeurs de la colonne amount de la table (c'est-à-dire le montant global de la vente). sales (c'est-à-dire la moyenne globale de tous les montants des ventes). Dans le SELECT externe - celui qui invoque le CTE - nous utiliserons la valeur renvoyée par le CTE pour calculer la différence entre le montant de chaque vente et la moyenne générale. Cela nous permettra d'identifier les ventes les plus importantes :

WITH sales_average AS (
  SELECT AVG(amount) AS amount_avg
  FROM sales
)
SELECT 
  customer_id, 
  sale_id, 
  amount, 
  amount - amount_avg as difference
FROM sales, sales_average;

Lorsque MySQL rencontre une requête comme celle ci-dessus, il exécute d'abord la requête dans la clause WITH et enregistre temporairement ses résultats sous le nom du CTE (sales_average). Ensuite, il exécute la requête externe SELECT en utilisant sales_average comme une table ordinaire, ce qui donne le résultat final.

L'exécution de l'ensemble de la requête avec l'échantillon de données détaillé ci-dessus donne les résultats suivants :

CTE avec fonctions de regroupement et d'agrégation

Prenons un exemple un peu plus complexe : nous allons créer une requête qui utilise un CTE pour trouver les appels les plus anciens dans un centre d'appel. Nous disposons d'une table appelée calls qui contient les adresses caller_id et call_time de chaque appel (ainsi que d'autres données comme la durée de l'appel que nous n'inclurons pas ici). Voici quelques lignes de la table calls tableau :

Notre CTE, appelé first_contact, renvoie la date et l'heure de l'appel le plus ancien de chaque appelant. Dans l'application externe SELECT, nous joignons le CTE au tableau calls pour obtenir toutes les données de chaque appel le plus ancien. Dans cet exemple (contrairement au précédent), nous détaillons les noms des colonnes retournées par le CTE dans sa déclaration :

WITH first_contact (caller_id, first_call_time) AS (
	SELECT 
          caller_id, 
          MIN(call_time) AS first_call_time
	FROM calls
	GROUP BY caller_id
)
SELECT c.*
FROM calls AS c
INNER JOIN first_contact AS fc 
ON fc.caller_id = c.caller_id 
  AND fc.first_call_time = c.call_time;

Pour exécuter la requête ci-dessus, MySQL récupère d'abord les résultats de la sous-requête sous le nom first_contact. Cela permettra de stocker temporairement toutes les valeurs distinctes de caller_id ainsi que la plus ancienne call_time de chacune d'entre elles. Ensuite, l'application externe SELECT joindra les résultats temporaires de la sous-requête précédente à la table calls pour obtenir toutes les données à partir de l'appel le plus ancien de chaque ID d'appelant.

Voici les résultats de la requête :

Si vous vous familiarisez avec les CTE de MySQL pour passer un entretien d'embauche, lisez cette compilation de questions d'entretien sur les CTE SQL pour savoir à quoi vous serez confronté.

CTE multiples et imbriqués

Dans MySQL, une seule instruction SQL peut contenir plusieurs CTE. Quel que soit le nombre de CTE définis dans une requête, le mot WITH n'est utilisé qu'une seule fois au début de la requête ; les définitions de CTE sont séparées par des virgules.

Utilisation de plusieurs CTE dans une seule requête

Dans l'exemple suivant, nous disposons d'une table appelée customers qui stocke la zone dans laquelle se trouve chaque client :

Supposons que nous souhaitions utiliser les informations de cette table en conjonction avec la table sales Supposons que nous souhaitions utiliser les informations de cette table en conjonction avec la table Supposons que nous souhaitions utiliser les informations de cette table en conjonction avec la table. Plus précisément, nous voulons obtenir les totaux des ventes des clients appartenant aux zones "OUEST" et "EST".

Pour ce faire, nous allons définir deux CTE qui nous permettront de filtrer uniquement les clients des zones qui nous intéressent. Ensuite, dans l'instruction externe SELECT, nous combinerons les données des deux CTE avec la table sales pour obtenir des moyennes de ventes pour chacune de ces zones :

WITH customers_west AS (
	SELECT *
	FROM customers
	WHERE area = 'WEST'),
customers_east AS (
	SELECT *
	FROM customers
	WHERE area = 'EAST')
SELECT 
   cw.area, 
   AVG(sw.amount) AS amount_avg
FROM customers_west AS cw
INNER JOIN sales sw 
ON sw.customer_id = cw.customer_id

UNION

SELECT 
  ce.area, 
  AVG(se.amount)
FROM customers_east AS ce
INNER JOIN sales se 
ON se.customer_id = ce.customer_id;

Voici le résultat :

Imbrication des CTE

MySQL nous permet également d'imbriquer des CTE de sorte qu'un CTE puisse faire référence à un CTE précédemment défini, le tout au sein d'une seule et même instruction SQL.

La technique d'imbrication des CTE permet de décomposer les requêtes complexes et volumineuses en sous-requêtes plus petites et plus faciles à gérer. Cela nous permet d'approcher progressivement la solution d'un problème, pour finalement réduire l'étape finale à une simple SELECT.

Dans l'exemple suivant, nous utilisons deux CTE imbriqués pour obtenir les ventes totales regroupées par pays et par région. Le tableau sales comprend une colonne "pays" qui indique le pays où chaque vente a été réalisée.

Le tableau countries comprend une colonne région qui indique la région géographique à laquelle appartient chaque pays :

Pour obtenir les totaux des ventes par région, nous utilisons deux CTE qui procèdent par étapes jusqu'à la solution finale :

WITH sales_by_country AS (
SELECT 
  country, 
  SUM(amount) AS total_sales_by_country
FROM sales
group BY country
),
sales_by_region AS (
SELECT 
  c.region, 
  SUM(s.total_sales_by_country) AS total_sales_by_region
FROM sales_by_country s
INNER JOIN countries c 
ON c.country = s.country
GROUP BY c.region
)
SELECT * 
FROM sales_by_region;

Dans la requête ci-dessus, nous définissons d'abord un CTE appelé sales_by_country qui regroupe les données par pays et renvoie les ventes totales de chaque pays. Ensuite, nous définissons un deuxième CTE appelé sales_by_region qui joint le CTE sales_by_country à la table des pays et regroupe les totaux des ventes par région. Enfin, l'ensemble de la requête est résolu par un simple SELECT à partir de le CTE sales_by_region.

Utilisation des CTE MySQL avec d'autres commandes DML

Les exemples précédents ont utilisé des CTE pour résoudre des requêtes SELECT complexes, en réduisant leur complexité pour finalement les résoudre avec un simple SELECT. Mais les CTE peuvent également être utilisés avec d'autres commandes. Dans cette section, nous verrons comment utiliser les CTE dans les commandes INSERT et UPDATE.

Utilisation d'un CTE avec INSERT

Supposons que nous ayons une table employees avec les colonnes empl_id (INT), empl_name (VARCHAR) et salary (DECIMAL). Nous avons ensuite une table applicants avec les mêmes colonnes que employees plus la colonne aptitude (TINYINT) qui indique si un candidat a réussi le test d'aptitude (aptitude = 1) ou non (aptitude = 0). Seuls les candidats qui ont réussi le test sont qualifiés pour devenir employés.

Périodiquement, les lignes du tableau applicants tableau qui ont la valeur 1 dans la colonne aptitude doivent être insérées dans le tableau. employees tableau. Pour ce faire, nous utiliserons un CTE appelé qualified_applicants pour filtrer les candidats qui ont réussi le test d'aptitude mais qui ne sont pas encore devenus employés. Les données renvoyées par le CTE qualified_applicants sont les données sources de le CTE INSERT qui les incorpore dans la table. employees tableau.

INSERT INTO employees (empl_id, empl_name, salary)
WITH qualified_applicants (empl_id, empl_name, salary) AS (
SELECT 
    empl_id, 
    empl_name, 
    salary
FROM applicants AS a
WHERE a.aptitude = 1
AND NOT EXISTS 
       (SELECT * 
        FROM employees AS e 
        WHERE e.empl_id = a.empl_id)
)
SELECT 
  empl_id, 
  empl_name, 
  salary
FROM qualified_applicants;

Lorsqu'un CTE est utilisé en combinaison avec un INSERT, tout le code du CTE, de la clause WITH à la SELECT externe, est écrit après la ligne INSERT INTO table (column1, column2, ...) est écrit après la ligne Pour effectuer cette insertion, MySQL exécute d'abord l'intégralité de la déclaration CTE, puis insère ses résultats dans la table spécifiée.

L'avantage de l'instruction ci-dessus est qu'elle peut être exécutée de manière répétée sans craindre de créer des données dupliquées ou de violer une clé primaire. En effet, la condition WHERE de la définition de le CTE comprend une clause qui empêche le CTE INSERT de tenter de réinsérer des données qui se trouvent déjà dans la table. employees table.

Utilisation d'un CTE avec UPDATE

Tout comme nous avons utilisé un CTE MySQL pour insérer des lignes dans une table, nous pouvons également utiliser un CTE pour mettre à jour la table avec de nouvelles informations. Dans l'exemple suivant, nous verrons comment utiliser un CTE en conjonction avec une commande UPDATE.

Nous utiliserons la même table employees et un nouveau tableau salaries avec les colonnes empl_id (INT) et salary (DECIMAL). Cette table stocke les salaires actualisés de chaque employé. Le CTE renverra les lignes de salaries dans lesquelles le salaire de l'employé est plus élevé que dans la table employees pour le même employé.

Voici la requête complète :

WITH raised_salaries (empl_id, salary) AS (
	SELECT s.empl_id, s.salary
	FROM salaries s
	INNER JOIN employees e 
      ON e.empl_id = s.empl_id
	WHERE s.salary > e.salary
)
UPDATE employees e
INNER JOIN raised_salaries rs 
ON rs.empl_id = e.empl_id
SET e.salary = rs.salary;

La syntaxe requise pour mettre à jour les données à partir d'un CTE suit la forme générale de la commande MySQL UPDATE JOIN. Lorsqu'elle est utilisée avec un CTE, la commande UPDATE JOIN doit être utilisée en remplacement de la commande externe SELECT du CTE. Le nom de le CTE est placé dans la clause JOIN pour l'associer à la table à mettre à jour.

Il est évident que vous ne pouvez pas mettre à jour les champs de le CTE - vous ne pouvez mettre à jour que les champs de la (des) table(s) jointe(s) à le CTE.

Comme l'instruction INSERT utilisée précédemment, cette combinaison de MySQL CTE et de l'instruction UPDATE peut être exécutée plusieurs fois sans changer une seule lettre. Chaque fois qu'elle est exécutée, elle ne met à jour que les lignes de la table employees dont le salaire est inférieur à celui indiqué dans la table salaries tableau.

Les CTE récursifs dans MySQL

En plus de simplifier et de clarifier les requêtes complexes, les CTE permettent d'implémenter la récursivité dans MySQL. En étant capable de résoudre la récursivité en utilisant uniquement des instructions SQL, nous évitons d'avoir recours à d'autres langages de programmation. Cela nous permet d'écrire des requêtes plus efficaces.

Les CTE récursifs sont composés des éléments suivants :

• Un membre d'ancrage qui fournit la ou les valeurs de départ de la séquence récursive.
• Un membre récursif qui reprend les résultats des itérations précédentes et y ajoute de nouvelles informations. Le(s) membre(s) d'ancrage et le(s) membre(s) récursif(s) sont combinés par des clauses UNION.
• Une condition de terminaison, qui est une clause WHERE (annexée au membre récursif) qui définit la condition qui déterminera la fin du cycle récursif.
• Une invocation, ou l'adresse SELECT externe qui fait référence àu CTE récursif par son nom (comme pour tout autre CTE).

Vous trouverez des informations plus détaillées dans cet article sur les CTE récursives.

Une utilisation typique des fonctions récursives en programmation est la génération de nombres de Fibonacci jusqu'à une valeur déterminée. Les nombres de Fibonacci sont utilisés dans des domaines aussi variés que le développement d'algorithmes de recherche et la simulation de la croissance démographique. Dans le CTE MySQL récursif suivant, nous obtenons les 10 premiers nombres de la séquence de Fibonacci :

WITH RECURSIVE cte_fib AS (
SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

UNION ALL

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0
)
SELECT counter, fibo
FROM cte_fib
ORDER BY counter;

Dans l'exemple ci-dessus, le membre d'ancrage est formé par les deux premiers membres de UNION, car ils fournissent les éléments initiaux de la série de Fibonacci (les deux premières lignes des données résultantes) :

SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

Le membre récursif est la troisième partie de UNION, car il ajoute une ligne aux données renvoyées par l'itération précédente du même CTE :

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0

La condition de terminaison est la clause WHERE entre parenthèses :

WHERE counter < 10
AND fibo > 0

Elle indique que la récursion doit s'arrêter lorsque le compteur atteint une valeur de 10. L'invocation est la clause SELECT à l'extérieur des parenthèses, qui renvoie le compteur et le nombre de Fibonacci pour chaque ligne renvoyée par le CTE.

Il existe de nombreuses autres utilisations des CTE récursifs dans MySQL. Par exemple, ils peuvent être utilisés pour parcourir des tables contenant des informations hiérarchiquement ordonnées (par exemple, des organigrammes) ou des structures de données arborescentes ou graphiques. Elles peuvent même être utilisées pour des tâches SQL non conventionnelles, comme dessiner un arbre de Noël avec les résultats d'une requête.

Les CTE MySQL en bref

Tout au long de cet article, nous avons fait le tour des CTE dans MySQL. Nous avons appris que les expressions de table communes :

• simplifient les requêtes complexes en les décomposant et en les organisant en parties plus petites et plus faciles à comprendre. Cela améliore également la lisibilité des requêtes.
• nous aident à écrire un code plus efficace. Une sous-requête définie comme une CTE peut être réutilisée dans différentes parties de la même requête - même dans d'autres sous-requêtes - sans avoir à la répéter.
• Encapsuler la logique sans créer d'objets inutiles. Les CTE encapsulent la logique des sous-requêtes sous un nom, tout comme le font les vues. Mais contrairement aux vues, ils n'impliquent pas la création d'objets pérennes dans la base de données.
• Mettre en œuvre la récursivité. C'est l'une des principales raisons de la popularité des CTE, car sans eux, il est assez compliqué d'implémenter la récursivité dans MySQL.

Vous devriez maintenant avoir une idée de ce qu'il est possible de faire avec les CTE récursifs dans MySQL. Après avoir lu cet article, vous serez prêt à consolider vos connaissances grâce à des exercices pratiques sur les CTE.

Mais ce que vous avez vu dans cet article n'est que la partie émergée de l'iceberg. Pour découvrir tout le potentiel des CTE dans MySQL, envisagez de suivre le cours Requêtes récursives de LearnSQL.fr dans MySQL. Avec ses 114 exercices interactifs, ce cours vous apportera toutes les connaissances nécessaires pour maîtriser les requêtes récursives dans MySQL 8. Vous n'avez même pas besoin d'avoir accès à un serveur MySQL ; l'environnement du cours vous fournira tous les outils nécessaires. Tout ce dont vous avez besoin, c'est d'un navigateur Web, d'une connexion Internet et d'une connaissance de base du langage SQL. Vous pouvez même répondre gratuitement au quiz d'introduction pour vous assurer que ce cours répond à vos besoins !

En conclusion, les CTE sont un outil puissant dans MySQL, en particulier lorsque la propreté et la lisibilité du code SQL sont importantes. Dans les équipes multidisciplinaires, où des ingénieurs de base de données SQL expérimentés collaborent avec des analystes de données, des concepteurs de bases de données et des administrateurs de bases de données, les CTE sont une ressource précieuse pour augmenter la productivité de l'équipe et obtenir un logiciel plus facile à maintenir et plus durable.