Guide du débutant sur la sous-requête SQL

Les sous-requêtes sont une ressource SQL puissante, qui nous permet de combiner les données de plusieurs tables en une seule requête. Dans cet article, nous allons vous apprendre tout ce dont vous avez besoin pour commencer à utiliser les sous-requêtes.

La définition la plus simple d'une sous-requête SQL est peut-être "Une requête dans une requête". Les sous-requêtes sont si faciles à comprendre qu'elles apparaissent souvent dans les premiers chapitres des cours de SQL.

Cependant, il existe de nombreuses variantes de sous-requêtes qui doivent être expliquées. Et bien que les sous-requêtes soient généralement utilisées dans la clause WHERE, vous pouvez les utiliser dans d'autres clauses, telles que FROM, HAVING et SELECT.

En bref, il y a beaucoup plus à savoir sur les sous-requêtes que ce qu'elles sont et où elles vont. Commençons donc par notre premier exemple de sous-requête SQL pour les débutants.

Sous-requêtes de base par exemple

Avant d'aborder les sous-requêtes, nous devons expliquer les tables de notre base de données. Pour détendre nos esprits en cette période de distanciation sociale, je vais utiliser des exemples liés à des endroits magnifiques et relaxants. Notre base de données d'exemple aura deux tables. La première table s'appelle best_10_places et elle stocke les 10 meilleurs endroits pour différents types d'activités (comme la plongée avec tuba, le ski et le trekking). La table comporte des colonnes pour le nom du lieu, l'activité que l'on peut y pratiquer, le classement de ce lieu et la ville la plus proche. Jetez-y un coup d'œil :

Table : best_10_places

Si vous souhaitez vous rendre dans l'un de ces endroits magnifiques, vous aurez besoin d'un billet. one_way_ticket contient un enregistrement pour chaque paire de villes reliées par un moyen de transport quelconque. Nous utiliserons cette table pour déterminer comment nous rendre d'une ville à une autre. Les colonnes contiennent des informations sur la ville d'origine, la ville de destination, le prix du billet, la durée du trajet et le type de transport (par exemple, train, avion, etc.). Vous trouverez ci-dessous un sous-ensemble de cette table :

Table : one_way_ticket

Nous sommes maintenant prêts pour le premier exemple. Supposons qu'une personne à Paris veuille se rendre au premier endroit au monde pour faire de la plongée libre. Quel type de transport va de Paris à cet endroit ?

Comme vous le savez probablement, la requête SQL la plus simple est formée d'une clause SELECT, FROM et (éventuellement) WHERE. Et comme nous l'avons mentionné précédemment, une sous-requête est une requête à l'intérieur d'une requête. Ainsi, dans l'exemple suivant, vous verrez deux requêtes : la requête principale (également appelée requête externe) et la sous-requête(en bleu) :

SELECT city_destination, transportation, ticket_price, travel_time
FROM one_way_ticket
WHERE city_destination = (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'snorkeling'
      AND ranking_position = 1
  )
  AND city_origin = 'Paris'

La sous-requête est exécutée en premier, renvoyant le site closest_city vers la meilleure destination pour la plongée libre (la ville de Florianopolis au Brésil). Ensuite, la requête principale est exécutée, remplaçant la sous-requête par son résultat (Florianopolis). Le résultat final est le suivant :

Lorsque vous utilisez des sous-requêtes :

• Vous devez toujours mettre la sous-requête entre parenthèses.
• Faites attention à l'opérateur utilisé pour comparer le résultat de la sous-requête. Dans notre exemple précédent, nous avons utilisé "=" ; toutefois, cet opérateur doit être utilisé avec des sous-requêtes qui ne renvoient qu'une seule ligne et une seule colonne (également appelées sous-requêtes "scalaires").

Je vous suggère de lire l'article Sous-requêtes SQL pour voir plus d'exemples de sous-requêtes pour débutants expliqués en détail. Les sous-requêtes font également partie de notre SQL pour les débutants un didacticiel qui vous guide pas à pas à travers les bases de SQL à l'aide d'exemples et d'exercices.

Sous-requêtes scalaires ou non scalaires : Telle est la question

Ainsi, une sous-requête scalaire renvoie une seule colonne avec une seule ligne. Qu'est-ce qu'une sous-requête non scalaire ? Une sous-requête qui renvoie plusieurs lignes.

Il existe de nombreux opérateurs que nous pouvons utiliser pour comparer une colonne avec une sous-requête. Toutefois, certains d'entre eux ne peuvent être utilisés qu'avec des sous-requêtes scalaires : =, >, >=, < et <=. Si vous utilisez l'un de ces opérateurs, votre sous-requête doit être scalaire.

Voyons un exemple avec une sous-requête scalaire. Supposons que vous ayez un client qui souhaite aller de Paris à Bariloche. Avant d'acheter le billet, le client veut voir s'il existe des villes où le billet est moins cher. La requête ci-dessous trouvera ces villes :

SELECT city_destination, ticket_price, travel_time, transportation
FROM one_way_ticket
WHERE ticket_price < (
    SELECT ticket_price
    FROM one_way_ticket
    WHERE city_destination = 'Bariloche'
      AND city_origin = 'Paris'
  )
  AND city_origin = 'Paris'

Ici encore, la sous-requête est exécutée en premier ; son résultat (le prix d'un billet Paris-Bariloche, soit 970 $) est comparé à la colonne ticket_price dans la requête externe. Cela permet d'obtenir tous les enregistrements de one_way_ticket dont la valeur de ticket_price est inférieure à 970 $. Le résultat de la requête est présenté ci-dessous :

D'autres opérateurs, comme IN, EXISTS ou NOT EXISTS, > ALL, = ANY, peuvent être utilisés avec des sous-requêtes scalaires ou non scalaires.

Notre prochain exemple utilise l'opérateur IN. Supposons que la personne qui a demandé quel était le meilleur endroit pour faire de la plongée libre souhaite explorer d'autres destinations ; en fait, elle aimerait voir les trois meilleurs endroits pour faire de la plongée libre. La modification de notre sous-requête est claire : il suffit de remplacer “ranking_position = 1” par “ranking_position <= 3”. Cependant, notre sous-requête renverra trois enregistrements et ne sera plus scalaire. Nous utiliserons l'opérateur IN, comme suit :

SELECT city_destination, transportation, ticket_price, travel_time
FROM one_way_ticket
WHERE city_destination IN (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'snorkeling'
      AND ranking_position <= 3
  )
  AND city_origin = 'Paris'

Comme dans l'exemple précédent, la base de données exécute d'abord la sous-requête, qui renvoie une liste de trois villes (les villes les plus proches des trois principales destinations de plongée avec tuba : Florianopolis, Honolulu et Melbourne). Ensuite, la requête externe est exécutée avec ces conditions :

city_destination IN ('Florianopolis', 'Honolulu', 'Melbourne')

L'opérateur IN renvoie TRUE lorsque la valeur de city_destination est l'une de ces trois villes. Ainsi, la requête principale renvoie le résultat suivant :

Si vous souhaitez approfondir les nuances des sous-requêtes SQL, consultez le chapitre sur les sous-requêtes de notre cours interactif. SQL pour les débutants où vous trouverez plusieurs exemples et de nombreux exercices pratiques.

Sous-requêtes avancées

Le concept de sous-requête est facile à comprendre. Mais grâce à la flexibilité de SQL, les sous-requêtes peuvent être utilisées sous de nombreuses formes différentes. Couvrir toutes les utilisations possibles dépasse le cadre de cet article. Nous allons plutôt démontrer certaines des utilisations les plus importantes.

À combien d'endroits différents peut-on placer une sous-requête ?

Les sous-requêtes peuvent être utilisées à différents endroits dans une requête SQL, y compris dans les clauses WHERE, FROM, HAVING et SELECT; en outre, une sous-requête peut également être utilisée dans le cadre d'une instruction UPDATE, DELETE ou INSERT. Dans l'exemple suivant, nous allons voir comment utiliser une sous-requête dans la clause FROM.

Supposons que le propriétaire de l'agence de voyage souhaite afficher chaque ville avec le prix du billet et le nombre de "meilleurs endroits" à proximité de cette ville. Pour obtenir la quantité de "meilleurs endroits" pour chaque ville, nous utiliserons une sous-requête (indiquée en bleu) dans la clause FROM pour créer une pseudo-table. Ensuite, la requête externe sera JOIN avec one_way_ticket et le pseudo tableau.

SELECT city_destination, ticket_price, pseudo_table.quantity
FROM one_way_ticket
JOIN (
    SELECT closest_city AS city, count(*) AS quantity
    FROM best_10_places
    GROUP BY 1
  ) pseudo_table
  ON one_way_ticket.pseudo_table.city

Le résultat de cette requête est :

Pour en savoir plus sur l'utilisation des sous-requêtes dans d'autres instructions SQL, lisez Subqueries in UPDATE and DELETE statements. Cet article contient plusieurs exemples avec du code SQL prêt à être copié et collé si vous voulez l'essayer.

EXISTS : Un opérateur orienté sous-requêtes

L'un des opérateurs les plus puissants que vous pouvez utiliser avec les sous-requêtes est l'opérateur EXISTS. Comme on peut le voir dans l'exemple ci-dessous, l'opérateur EXISTS doit venir avant la sous-requête. Il renvoie TRUE si la sous-requête renvoie au moins une ligne, quel que soit le contenu de cette ligne. Si la sous-requête renvoie 0 ligne, EXISTS renverra FALSE.

Dans l'exemple suivant, supposons que notre client de Paris souhaite se rendre dans un endroit où il peut faire à la fois du trekking et de la plongée avec tuba. La requête ci-dessous peut être utilisée pour répondre à ce client :

SELECT city_destination, transportation, ticket_price, travel_time
FROM one_way_ticket
WHERE EXISTS (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'snorkeling'
      AND closest_city = one_way_ticket.city_destination
  )
  AND EXISTS (
    SELECT closest_city
    FROM best_10_places
    WHERE activity_type = 'trekking'
    AND closest_city = one_way_ticket.city_destination
  )
  AND city_origin = 'Paris'

Le résultat montre les enregistrements relatifs aux villes proposant des activités de trekking et de snorkeling :

Un point intéressant dans la sous-requête précédente est la référence à la colonne one_way_ticket.city_destination dans la requête externe. Les sous-requêtes qui font référence à des colonnes dans la requête externe sont appelées "sous-requêtes corrélées" et ont des comportements spécifiques. Comme dans l'exemple précédent, les sous-requêtes corrélées ont tendance à être utilisées avec les opérateurs de sous-requête EXISTS et NOT EXISTS.

Les sous-requêtes corrélées sont une ressource SQL puissante. Dans certains scénarios, elles constituent le moyen naturel de résoudre un problème. Si ce sujet vous intéresse, je vous suggère de lire ous-requête corrélée en SQL : Guide du débutant et Apprendre à écrire une sous-requête corrélée en SQL en 5 minutes.

Les opérateurs ALL et ANY

Cette paire d'opérateurs fonctionne en conjonction avec les opérateurs =, <>, >, >=, < et <=, ajoutant ainsi plus d'expressivité au langage. En raison du grand nombre de combinaisons possibles avec ALL et ANY, j'ai inclus un tableau avec les utilisations les plus courantes de ces opérateurs :

Appliquons cet opérateur à un exemple concret. Supposons que nous voulions promouvoir tous les "meilleurs endroits du monde" que vous pouvez visiter avec un billet de moins de 1 000 dollars. Chaque "meilleur endroit" du tableau best_10_places peut avoir de nombreux billets possibles ; nous ne sommes intéressés que par les endroits où au moins un billet coûte moins de 1000 $. La requête est la suivante :

SELECT Place_name, Activity, Ranking_position
FROM best_10_places
WHERE 1000 > ANY (
    SELECT ticket_price
    FROM one_way_ticket
    WHERE city_destination = best_10_places.closest_city
  )

Les résultats de la requête précédente sont présentés ci-dessous. Vous pouvez vous rendre au meilleur endroit pour n'importe quelle activité (plongée avec tuba, ski et trekking) pour moins de 1 000 dollars !

Vos prochaines étapes avec les sous-requêtes

Dans cet article, j'ai expliqué les sous-requêtes et je vous ai montré plusieurs exemples de leur utilisation. Cependant, ce sujet comporte de nombreuses variantes, notamment les différents types de sous-requêtes et d'opérateurs. Pour une compréhension plus complète des sous-requêtes, je vous suggère de suivre un cours en ligne comme celui de LearnSQL.fr. SQL pour les débutants ou de lire les articles supplémentaires que j'ai mentionnés.