Sous-requête et CTE : une introduction au SQL

Vous êtes-vous déjà interrogé sur les différences entre une sous-requête et une expression de table commune (CTE) en SQL ? Les concepts semblent très similaires, mais connaître la différence - et savoir quand utiliser chacun d'eux - vous aidera à rédiger des requêtes efficaces et lisibles.

Tout d'abord, nous allons expliquer les concepts sous-jacents aux sous-requêtes et aux CTE. Nous examinerons ensuite quelques exemples et analyserons enfin les avantages et les inconvénients de chaque technique.

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

Une sous-requête est une requête dans une requête. Nous pouvons l'utiliser de multiples façons : dans la clause FROM, pour le filtrage, ou même comme colonne. Pour utiliser une sous-requête, il suffit d'ajouter des parenthèses et de placer la requête à l'intérieur de celles-ci.

Dans nos premiers exemples, nous allons travailler avec des données sur les résultats d'un concours de saut en longueur. On nous donne deux tables :

participant - Stocke des informations sur les participants au concours :

• id - Un identifiant unique pour chaque participant.
• first_name - Le prénom du participant.
• last_name - Le nom de famille du participant.

jump - Stocke des informations sur les sauts effectués par les participants :

• id - L'ID de chaque saut.
• participant_id - L'ID du participant qui a effectué le saut.
• contest_id - L'identifiant du concours dans lequel le saut a été effectué.
• length - La longueur du saut, en centimètres.

participant

jump

Puisque vous connaissez les données que nous utilisons, jetez un coup d'oeil aux exemples de sous-requêtes suivants :

SELECT
  first_name,
  last_name,
  length
FROM participant
JOIN jump
  ON jump.participant_id = participant.id
WHERE length > (
  SELECT
    AVG(length)
  FROM jump
);

Cette requête affiche les participants dont les sauts étaient plus longs que la longueur moyenne des sauts. Dans la condition WHERE, nous avons utilisé une sous-requête pour obtenir la longueur moyenne des sauts. Comme cette sous-requête ne renvoie qu'une seule valeur, nous pouvons facilement y comparer les données des colonnes.

Ensuite, un autre exemple :

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

Dans cette requête, nous montrons la plus grande longueur moyenne de saut de tous les participants. Pour obtenir ce résultat, nous devons d'abord calculer la longueur moyenne des sauts pour chaque participant. Pour ce faire, nous avons utilisé une sous-requête dans la clause FROM. Ensuite, nous utilisons simplement MAX() pour renvoyer la plus grande longueur moyenne.

Ce ne sont là que deux exemples de sous-requêtes. C'est un sujet vaste - même si les cas d'utilisation sont plutôt simples - et il y a trop de concepts pour être décrits dans cet article. Vous trouverez un bref aperçu des sous-requêtes dans l'article SQL Subqueries sur le blog LearnSQL.com. Si vous souhaitez travailler avec des sous-requêtes, consultez la partie 6 du cours SQL Subqueries. SQL pour les débutants cours (elle s'appelle même Subqueries).

Vous pouvez également regarder les épisodes de notre série Nous apprenons le SQL sur Youtube. Plusieurs d'entre eux sont consacrés aux sous-requêtes SQL. N'oubliez pas de vous abonner à notre chaîne.

Qu'est-ce qu'une CTE ?

Une expression de table commune (appelée CTE en abrégé) est une requête que nous créons avant d'écrire la requête principale. Ensuite, nous pouvons simplement l'utiliser comme une table régulière dans notre code.

Regardez l'exemple suivant. Une fois encore, nous utilisons les données du concours de saut en longueur :

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Cette requête renvoie exactement le même résultat que l'exemple précédent avec la sous-requête : la plus grande longueur moyenne de saut pour tous les participants. Cependant, au lieu d'écrire notre requête dans la clause FROM, nous l'avons placée dans la clause WITH (qui vient avant la requête principale). Nous avons appelé notre CTE average_length et l'avons utilisé dans la clause FROM de la requête principale.

Bien entendu, les CTE peuvent être bien plus compliqués que cet exemple. Mais nous n'en parlerons pas ici. Si vous souhaitez en savoir plus sur les CTE, consultez le cours Requêtes récursives cours sur LearnSQL.com.

Pas de différence du tout... ?

Au début, vous pourriez penser qu'il n'y a pratiquement aucune différence entre les sous-requêtes et les CTE. Nous avons déjà utilisé une sous-requête et un CTE dans la clause FROM et la syntaxe n'était que légèrement différente. Toutefois, n'oubliez pas le premier exemple : nous avons utilisé une sous-requête dans la clause WHERE. Vous ne pouviez pas utiliser un CTE, et ce n'est pas la seule différence !

Sous-requête et CTE : quelle est la différence ?

Bien entendu, cela ne signifie pas que les CTE sont inférieurs aux sous-requêtes. Examinons les différences entre les deux, en commençant par les CTE.

Différence n° 1 : les CTE peuvent être récursifs

Examinons le premier avantage des CTE. Les CTE vous permettent d'utiliser un concept puissant : la récursion. Grâce à la récursion, SQL est maintenant complet au sens de Turing - tout programme qui peut être écrit dans n'importe quel langage de programmation peut également être écrit en SQL. (Si vous doutez que SQL soit un langage de programmation, consultez la rubrique Is SQL a programming language ? sur le blog LearnSQL.com).

Comment fonctionne la récursion en SQL ? Elle permet à votre CTE de s'appeler lui-même jusqu'à ce qu'une condition finale spécifiée soit remplie. À chaque étape, la requête s'étend et modifie les données dont elle dispose. Voyons un exemple.

Nous allons travailler avec les données suivantes concernant des employés d'une entreprise fictive. Elles sont stockées dans la table employee qui contient les colonnes suivantes :

• id - Un identifiant unique pour chaque employé.
• first_name - Le prénom de l'employé.
• last_name - Le nom de famille de l'employé.
• manager_id - L'ID du responsable de l'employé.

employee

Maintenant, nous aimerions montrer la chaîne de gestion du PDG (une personne sans valeur dans la colonne manager_id ) à chaque employé. La requête ci-dessous résoudra ce problème. Jetez-y un coup d'œil :

WITH RECURSIVE employee_chain AS (
  SELECT
    id,
    first_name,
    last_name,
    first_name || ' ' || last_name AS chain
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    chain || '->' || employee.first_name || ' ' || employee.last_name
  FROM employee_chain
  JOIN employee
    ON employee.manager_id = employee_chain.id
)

SELECT
  first_name,
  last_name,
  chain
FROM employee_chain;

Le résultat ressemblera à ceci :

Nous avons écrit une requête qui peut facilement créer toute une chaîne de relations. Vous pourriez penser que cela pourrait être réalisé avec des sous-requêtes, mais comme la chaîne de gestion est de plus en plus profonde, vous auriez à écrire de plus en plus de code. La quantité de code à écrire dépendrait de la profondeur de la chaîne - et cela ne peut être vérifié qu'avec un CTE récursif.

Comment fonctionne cette requête ? Elle commence par exécuter la première partie (avant le UNION ALL) et sélectionne un employé sans responsable (c'est-à-dire Maisy Bloom). Ensuite, la partie située sous l'UNION ALL sélectionne le ou les employés directement gérés par Maisy (Caine Farrow). Comme la requête s'appelle elle-même, elle exécute à nouveau la même partie et sélectionne tous les employés gérés par Caine (Waqar Jarvis et Lacey-Mai Rahman). Elle répète cette opération tant qu'elle a des lignes à joindre. Après avoir parcouru toute la chaîne de gestion, la requête s'arrête.

Si c'est votre première rencontre avec la récursion en SQL, il se peut que ce soit un peu difficile à comprendre. Et c'est tout à fait normal. Consultez la rubrique Faites-le en SQL : Recursive SQL Tree Traversal pour une explication plus détaillée.

Différence n° 2 : les CTE sont réutilisables

L'un des grands avantages des CTE est qu'ils peuvent être utilisés plusieurs fois dans une requête. Il n'est pas nécessaire de copier l'ensemble du code CTE - il suffit de mettre le nom du CTE.

En utilisant les données de la section précédente, nous aimerions 1) filtrer les employés qui n'ont pas de manager et 2) afficher chaque employé avec son manager - mais seulement s'il a un manager. Le résultat ressemblera à ceci :

Voyons maintenant comment un CTE pourrait résoudre cette tâche :

WITH not_null_manager AS (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
)

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM not_null_manager AS nnm1
JOIN not_null_manager AS nnm2
  ON nnm1.manager_id = nnm2.id;

Voyons maintenant comment une sous-requête permettrait d'obtenir le même résultat :

SELECT
  nnm1.first_name,
  nnm1.last_name,
  nnm2.first_name,
  nnm2.last_name
FROM (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm1
JOIN (
  SELECT
    *
  FROM employee
  WHERE manager_id IS NOT NULL
) AS nnm2
  ON nnm1.manager_id = nnm2.id;

Comme vous pouvez le voir, la requête CTE a moins de code. Elle est également plus lisible : vous répétez simplement le nom du CTE (not_null_manager) au lieu de tout un morceau de code.

Il n'y a pas vraiment de différence dans l'efficacité des performances entre ces deux requêtes. Même si vous ne déclarez le CTE qu'une seule fois, le temps d'exécution est pratiquement le même.

Différence n° 3 : les CTE peuvent être plus lisibles

Vous savez donc que vous pouvez écrire moins de code en utilisant les CTE. Mais qu'en est-il de l'organisation du code ? Voici un autre exemple qui porte sur la clause FROM.

Vous vous souvenez encore des premiers exemples ? Ceux où il s'agissait de retourner la plus grande longueur moyenne de saut ? Si ce n'est pas le cas, voici un bref rappel.

Celui-ci utilise une sous-requête :

SELECT
  MAX(avg_length) AS max_length
FROM (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
) AS average_lengths;

Et celui-là utilise un CTE :

WITH average_lengths AS (
  SELECT
    first_name,
    last_name,
    AVG(length) AS avg_length
  FROM participant
  JOIN jump
    ON jump.participant_id = participant.id
  GROUP BY first_name,
    last_name
)

SELECT
  MAX(avg_length) AS max_length
FROM average_lengths;

Lequel est le plus lisible selon vous ? Je dirais que la deuxième (CTE) est un peu mieux. Le code est moins imbriqué et il y a une petite couche d'abstraction. Vous savez au premier coup d'œil que cette requête sélectionne la longueur moyenne maximale.

La lisibilité des CTE est encore plus évidente dans les exemples de la section précédente. Et lorsque vos requêtes seront de plus en plus volumineuses, l'utilisation des CTE améliorera considérablement la lisibilité de votre code. Et la lisibilité est la clé d'un développement efficace du code.

Si vous voulez en savoir plus sur l'utilisation des CTE pour améliorer la lisibilité de votre code, vous devriez absolument consulter l'article Comment organiser les requêtes SQL avec les CTE.

Mais les sous-requêtes sont parfois irremplaçables

Jusqu'à présent, vous avez appris un certain nombre de différences entre les sous-requêtes et les CTE. Pour être honnête, j'ai essayé de vous convaincre que les CTE sont bien meilleurs que les sous-requêtes. Mais dans cette section, vous apprendrez pourquoi les sous-requêtes peuvent être inestimables.

Filtrer avec une sous-requête

Le premier exemple de cet article utilisait une sous-requête dans la clause WHERE. Je n'ai pas montré d'exemple similaire dans la section sur les ETC. C'est parce que seules les sous-requêtes peuvent être utilisées dans la clause WHERE !

De plus, il existe un grand nombre de mots-clés que vous pouvez utiliser dans la condition WHERE, par exemple ALL, ANY, EXISTS, et bien d'autres encore ! Malheureusement, je ne peux pas les expliquer ici ; cela prendrait trop de temps. Je vous recommande plutôt de consulter la section sur les sous-requêtes de notre SQL pour les débutants cours. Non seulement vous apprendrez à connaître ces mots-clés, mais vous résoudrez aussi quelques problèmes en les utilisant ! Vous pouvez également consulter l'article sur les sous-requêtes SQL sur notre blog si vous souhaitez obtenir une brève explication.

Les sous-requêtes peuvent agir comme des colonnes

Vous pouvez également utiliser des sous-requêtes comme vous le feriez avec une colonne. La seule contrainte est que la sous-requête ne doit renvoyer qu'une seule valeur. Regardez :

SELECT DISTINCT
  contest_id,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length > 600
  ) AS longer_jumps,
  (
    SELECT
      COUNT(length)
    FROM jump AS inside_jump
    WHERE inside_jump.contest_id = outside_jump.contest_id
      AND inside_jump.length <= 600
  ) AS shorter_jumps
FROM jump AS outside_jump;

Pour chaque concours, cette requête renvoie le nombre de sauts supérieurs à 600 cm (calculé dans la première sous-requête) et le nombre de sauts inférieurs ou égaux à 600 cm (calculé dans la deuxième sous-requête). Le résultat ressemblera à ceci :

Sous-requêtes corrélées

Regardez à nouveau l'exemple ci-dessus. Avez-vous remarqué que j'ai utilisé une référence à une table dans la requête externe à l'intérieur de la sous-requête? J'ai même fait référence à la valeur de la ligne actuelle de cette table. C'est ce qu'on appelle une "sous-requête corrélée". Elle vous permet d'utiliser les valeurs de la requête externe dans la sous-requête.

Il s'agit d'une technique très pratique, mais aussi très compliquée ; nous ne l'expliquerons pas dans cet article. Cependant, n'hésitez pas à consulter Correlated Subquery in SQL : A Beginner's Guide sur notre blog pour une explication.

Une différence de plus : Les CTE doivent être nommés

La dernière différence entre les CTE et les sous-requêtes réside dans la dénomination. Les CTE doivent toujours avoir un nom. En revanche, dans la plupart des moteurs de base de données, les sous-requêtes ne nécessitent aucun nom (la seule exception est la clause FROM dans mon moteur de base de données préféré, PostgreSQL).

C'est une bonne pratique de nommer les sous-requêtes placées dans les clauses FROM ou SELECT, mais ce n'est pas une obligation. Et, pour être précis, vous ne pouvez pas nommer les sous-requêtes que vous utilisez dans la clause WHERE.

Vous pourriez penser que le nommage n'est pas une grande différence et ne vous affectera pas beaucoup. Cependant, il se peut que vous deviez vérifier rapidement quelque chose dans la base de données. Dans ce cas, la syntaxe la plus simple peut être votre choix. Même si la requête est moins lisible, gardez ceci à l'esprit : de telles requêtes sont rarement lues après avoir été utilisées.

Sous-requêtes ou CTE - Quel est le meilleur choix ?

Vous avez appris beaucoup de choses sur les différences entre les CTE et les sous-requêtes. Alors, qu'est-ce qui est le mieux ? La réponse est ni l'un ni l'autre, ou cela dépend - les sous-requêtes et les CTE ont tous deux des avantages et des inconvénients. Chaque requête doit être analysée et le choix entre les deux doit être décidé au cas par cas. Mais pour ce faire, vous devrez apprendre en profondeur les deux concepts.

Pour en savoir plus sur les sous-requêtes, je vous suggère la partie Subqueries du cours LearnSQL. SQL pour les débutants de LearnSQL. Si vous souhaitez en savoir plus sur les ETC, le cours Requêtes récursives est votre meilleure option. Ces cours vous permettront d'apprendre rapidement ces concepts. Ainsi, vous serez en mesure de décider quelles requêtes bénéficient des CTE et lesquelles font appel aux sous-requêtes.

Toutefois, si vous êtes déjà un peu familier avec les sous-requêtes corrélées et que vous ne voulez pas réapprendre la même chose, vous pouvez également affiner vos compétences dans notre Exercices Pratiques de SQL cours.