5 raisons pour lesquelles vous devriez utiliser les CTE au lieu des sous-requêtes

Les expressions de table commune, ou CTE, ont été introduites dans SQL:1999 pour gérer les cas où la sortie d'une requête est utilisée dans une autre requête. Mais n'avions-nous pas déjà des sous-requêtes pour cela ? Dans cet article, je vais démontrer à l'aide de plusieurs exemples pourquoi les CTE sont meilleurs que les sous-requêtes pour la structure et la lisibilité de vos requêtes SQL.

Commençons par nous rappeler ce que sont les CTE et les sous-requêtes et en quoi ils diffèrent.

Expressions de table commune et sous-requêtes

Une sous-requête est une requête imbriquée dans une autre requête. Elle peut être placée n'importe où dans une requête, même dans une autre sous-requête. La syntaxe est très simple : il suffit de placer la sous-requête entre parenthèses et de l'insérer dans la requête principale, là où elle est nécessaire. Notez que les sous-requêtes sont exécutées avant la requête principale (également appelée requête parent ou requête externe) afin que leurs résultats puissent être utilisés par la requête principale. Il est souvent difficile de lire une requête SQL contenant plusieurs sous-requêtes, car même si elles sont exécutées en premier, elles sont définies quelque part dans la requête principale. Vous pouvez en savoir plus sur les sous-requêtes SQL dans ce guide d'introduction.

Une expression de table commune (CTE), également appelée clause WITH, est un ensemble de résultats temporaires nommés que vous pouvez référencer n'importe où dans votre requête. Contrairement aux sous-requêtes, qui sont insérées exactement là où vous en avez besoin, toutes les CTE sont définies avant la requête principale et sont ensuite référencées dans la requête en utilisant le nom attribué. Dans les exemples ci-dessous, nous allons voir comment cela améliore considérablement la structure et la lisibilité d'une requête SQL. Vous définissez d'abord tous les ensembles de résultats temporaires dont vous avez besoin, puis vous les utilisez dans la requête principale si nécessaire.

Vous trouverez une introduction détaillée aux expressions de table communes ici. Pour en savoir plus sur la différence entre les expressions CTE et les sous-requêtes SQL, consultez cet article de présentation.

5 raisons de choisir les CTE

Les apprenants SQL se demandent souvent s'ils doivent utiliser une expression de table commune ou une sous-requête lorsqu'ils rédigent une requête SQL complexe. Nous allons examiner plusieurs exemples pour expliquer pourquoi vous devez préférer une CTE à une sous-requête. Pour ces exemples, je vais utiliser le tableau suivant qui résume les performances des employés de plusieurs départements.

Pour chaque employé, nous disposons de son ID, de son nom complet, de son département, de ses années d'expérience et d'un score d'achèvement de l'ICP sur une échelle de 0 à 100.

Voyons comment les CTE et les sous-requêtes peuvent nous aider à analyser ces données.

5 raisons d'utiliser les CTE plutôt que les sous-requêtes

#1. Les CTE portent des noms significatifs

Vous pouvez donner des noms significatifs aux CTE pour préciser votre intention et rendre la requête plus lisible. Par exemple, disons que nous voulons comparer les performances de chaque employé avec la moyenne des indicateurs de performance clés dans leurs départements respectifs. Pour ajouter la colonne correspondante à notre tableau, nous devons d'abord calculer le KPI moyen pour chaque département. Notre requête complète avec une sous-requête est la suivante :

SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN 
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department) AS avg
ON p.department = avg.department;

Vous pouvez obtenir le même résultat en utilisant une expression de table commune:

WITH avg_department_kpi AS
  (SELECT department, AVG(kpi) AS average_dep_kpi
   FROM performance
   GROUP BY department)
SELECT p.name, p.department, p.kpi, avg.average_dep_kpi
FROM performance p
JOIN avg_department_kpi avg
ON p.department = avg.department;

Les CTE et les sous-requêtes donnent le même résultat, et les requêtes sont plus ou moins de la même longueur. Mais notez que la lisibilité de la requête CTE est meilleure. La signification d'une requête complexe peut être évidente pour vous lorsque vous regardez votre propre code, mais elle peut ne pas être claire du tout pour vos collègues. Lorsque vous lisez la version CTE :

• Vous voyez la requête dans l'ordre où elle sera exécutée : d'abord la sous-requête, puis la requête principale.
• Vous pouvez déterminer l'objectif de la sous-requête à partir de son nom. Dans notre cas, avg_department_kpi fait référence au CTE qui produit le KPI moyen pour chaque département.

En même temps, vous avez peut-être remarqué que la requête CTE est un peu plus longue que la version sous-requête. Ce n'est pas toujours le cas, notamment lorsque nous avons besoin de la même sous-requête plusieurs fois dans notre requête principale.

Si la syntaxe CTE vous est inconnue, vous pouvez vous exercer aux expressions de table communes dans notre cours interactif. Pour l'instant, passons à la deuxième raison de préférer les CTE aux sous-requêtes.

#2. Les CTE sont réutilisables dans une requête

Contrairement aux sous-requêtes, vous n'avez pas besoin de répéter la définition d'un CTE chaque fois que vous en avez besoin dans la requête. Vous ne le définissez qu'une seule fois, au début de votre requête, puis vous y faites référence lorsque cela est nécessaire.

Disons que nous voulons comparer les performances de différents départements. Plus précisément, nous voulons voir le KPI moyen dans chaque département ainsi que le KPI moyen minimum et maximum dans tous les départements. Nous pouvons résoudre ce problème en utilisant une requête SQL avec trois sous-requêtes :

1. Trouver le KPI moyen pour chaque département.
2. Trouver le KPI moyen minimum dans tous les départements.
3. Trouver le KPI moyen maximum pour tous les départements.

Notez que les deux dernières sous-requêtes auront toutes deux besoin du résultat de la première. Ainsi, si nous utilisons des sous-requêtes, notre solution inclura des sous-requêtes définies dans d'autres sous-requêtes. Tout cela est assez confus :

SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS ad
CROSS JOIN
    (SELECT MIN (average_kpi) AS min_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS min
CROSS JOIN
    (SELECT MAX (average_kpi) AS max_avg_kpi_department
     FROM
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS ad) AS max;

En revanche, lorsque nous utilisons des expressions de table communes, nous définissons nos trois CTE au début de la requête, nous les référençons dans la requête principale lorsque cela est nécessaire, et nous évitons de multiples sous-requêtes imbriquées :

WITH avg_per_department AS (
    SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    min_kpi_department AS (
    SELECT MIN (average_kpi) AS min_avg_kpi_department
    FROM avg_per_department),

    max_kpi_department AS (
    SELECT MAX (average_kpi) AS max_avg_kpi_department
    FROM avg_per_department)
SELECT 
  ad.department, 
  ad.average_kpi, 
  min.min_avg_kpi_department,
  max.max_avg_kpi_department
FROM avg_per_department ad
CROSS JOIN min_kpi_department min
CROSS JOIN max_kpi_department max;

Dans ce cas, vous pouvez observer que les requêtes AVEC rendent votre code non seulement plus lisible mais aussi beaucoup plus court.

#3. Les CTE divisent les calculs complexes en plusieurs parties

Les CTE apportent de la clarté au processus de calcul. Lorsque des sous-requêtes sont utilisées pour des calculs, la requête se transforme souvent en un enchevêtrement de sous-requêtes. Les CTE, par contre, montrent plus clairement le processus de calcul.

Disons que nous voulons savoir combien d'employés débutants et expérimentés se trouvent dans chaque département. Nous considérerons les employés ayant deux ans d'expérience ou moins comme juniors et ceux ayant plus de deux ans d'expérience comme expérimentés.

Nous avons essentiellement besoin de deux sous-requêtes ici :

1. Pour calculer le nombre d'employés juniors dans chaque département.
2. Pour calculer le nombre d'employés expérimentés dans chaque département.

Voici comment nous pouvons aborder ce problème avec des sous-requêtes:

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department) AS e
JOIN (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department) AS j
ON e.department = j.department;

Cet exemple n'est pas très compliqué, donc vous êtes probablement capable de suivre la requête ci-dessus. Cependant, notez que :

• Vous devez deviner le but de chaque sous-requête.
• Il n'est pas immédiatement clair quelles tables sont jointes.
• La clause SELECT énumère les champs à afficher dans la sortie, mais ces champs ne sont définis que plus tard dans les sous-requêtes.

Lorsque les calculs deviennent plus complexes, il est vraiment difficile de suivre le processus de calcul à travers toutes les sous-requêtes. En revanche, le processus de calcul utilisant les CTE est beaucoup plus lisible :

WITH experienced AS
  (SELECT department, COUNT(*) AS experienced_employees
   FROM performance
   WHERE years_experience > 2
   GROUP BY department),

  junior AS
  (SELECT department, COUNT(*) AS junior_employees
   FROM performance
   WHERE years_experience <= 2
   GROUP BY department)

SELECT 
  e.department, 
  e.experienced_employees, 
  j.junior_employees
FROM experienced e
JOIN junior j
ON e.department = j.department;

Ici, nous commençons par définir tous les ensembles de résultats temporaires nécessaires aux calculs. Ensuite, il suffit d'écrire une simple requête pour obtenir la sortie nécessaire. Tout est clair et net.

Croyez-moi, vous apprécierez vraiment la structure que les expressions de table communes fournissent lorsque vous travaillerez avec des calculs plus complexes. Pour prouver ce que je dis, je vous propose un autre exemple de calculs imbriqués.

#4. Les CTE imbriqués sont vraiment cool

Compte tenu de la réutilisabilité des CTE et de leur capacité à clarifier le processus de calcul, il n'est pas surprenant que les clauses WITH soient parfaites pour les calculs imbriqués.

Supposons que nous voulions calculer combien d'employés dans chaque département ont un score d'ICP qui est (1) supérieur à la moyenne ou (2) inférieur à la moyenne dans leur département respectif. Plus précisément, pour chaque département, nous voulons afficher le score KPI moyen, le nombre d'employés ayant un score KPI supérieur à la moyenne et le nombre d'employés ayant un score KPI inférieur à la moyenne.

Nous avons essentiellement besoin de trois sous-requêtes ici :

1. Pour obtenir le KPI moyen pour chaque département.
2. Pour obtenir le nombre d'employés qui ont un score KPI supérieur à la moyenne.
3. Pour obtenir le nombre d'employés qui ont un score KPI inférieur à la moyenne.

Cependant, lorsque vous utilisez des sous-requêtes, vous devez ajouter la première sous-requête trois fois, dont deux fois lorsqu'elle sera imbriquée dans les deux autres sous-requêtes. La requête qui en résulte est assez désordonnée :

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department) AS avg
JOIN
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department) AS aa
ON avg.department = aa.department
JOIN
    (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN
       (SELECT department, AVG(kpi) AS average_kpi
        FROM performance
        GROUP BY department) AS avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department) AS ba
ON avg.department = ba.department;

La requête a fonctionné et nous avons obtenu la sortie que nous voulions, mais remarquez combien il est difficile de suivre les multiples sous-requêtes imbriquées. Dans le monde réel, cela devient souvent encore plus compliqué.

En revanche, voyez à quel point la même requête est soignée lorsqu'on utilise des CTE plutôt que des sous-requêtes :

WITH avg_kpi_department AS
    (SELECT department, AVG(kpi) AS average_kpi
    FROM performance
    GROUP BY department),

    above_average AS
    (SELECT p.department, count(*) AS employees_above_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi > average_kpi
     GROUP BY p.department),

     below_average AS
     (SELECT p.department, count(*) AS employees_below_average
     FROM performance p
     JOIN avg_kpi_department avg
     ON p.department = avg.department
     WHERE kpi < average_kpi
     GROUP BY p.department)

SELECT 
  avg.department, 
  avg.average_kpi, 
  aa.employees_above_average, 
  ba.employees_below_average
FROM avg_kpi_department avg
JOIN above_average aa
ON avg.department = aa.department
JOIN below_average ba
ON avg.department = ba.department;

Vous pouvez clairement suivre la manière dont les trois ensembles de résultats temporaires sont définis. Vous pouvez comprendre leur objectif à partir des noms qui leur sont attribués. Vous n'avez pas besoin de répéter plusieurs fois la même sous-requête dans la même requête principale. Lorsque vous arrivez à la requête principale, tout le travail préparatoire est fait et vous pouvez simplement joindre les CTEs qui ont déjà été définis.

#5. Les CTE permettent la récursion

Enfin, les CTE sont parfaits pour traiter les graphiques, les arbres et autres structures hiérarchiques. En effet, la syntaxe WITH peut traiter la récursion. Une requête récursive est une requête qui se réfère à elle-même.

Par exemple, si nous avons une structure organisationnelle typique, où chaque employé a un supérieur et les supérieurs ont plusieurs subordonnés, les CTE récursifs peuvent nous aider à analyser ces données efficacement. Avec une seule requête SQL et des données au niveau individuel, nous pouvons calculer le salaire total pour chacun des départements et sous-départements ou le nombre total de jours de vacances non utilisés dans chacune des unités organisationnelles.

La syntaxe des CTE récursifs est assez compliquée. Pour une explication détaillée, je vous recommande de lire cet article qui explique comment les requêtes récursives traitent les structures hiérarchiques. Vous pouvez également consulter ces exemples d'application des requêtes récursives aux structures graphiques.

Pratiquons les CTE !

Maintenant que vous avez pris conscience des avantages que les CTE apportent aux requêtes SQL, il est temps de vous exercer ! Je vous recommande de commencer par notre Requêtes récursives cours interactif (114 défis de codage), qui couvre toutes sortes d'expressions de table courantes, y compris les CTE simples, les CTE imbriqués et les CTE récursifs. Vous trouverez plus de détails dans cet article de présentation.

Merci de votre lecture et bon apprentissage !