Sous-requête et JOIN

L'un des défis de la rédaction de requêtes SQL consiste à choisir entre une sous-requête et une jointure. Il existe de nombreuses situations dans lesquelles une JOIN est la meilleure solution, et d'autres dans lesquelles une sous-requête est préférable. Examinons ce sujet en détail.

Les sous-requêtes sont utilisées dans les requêtes SQL complexes. En général, il y a une requête externe principale et une ou plusieurs sous-requêtes imbriquées dans la requête externe.

Les sous-requêtes peuvent être simples ou corrélées. Les sous-requêtes simples ne reposent pas sur les colonnes de la requête externe, tandis que les sous-requêtes corrélées font référence aux données de la requête externe.

Vous pouvez découvrir les sous-requêtes dans la section Sous-requêtes du cours interactif "SQL pour les débutants" ou vous entraîner à écrire des sous-requêtes dans la section Sous-requêtes du cours "Exercices Pratiques de SQL". Vous pouvez également lire l'article "SQL Subqueries" de Maria Alcaraz.

La clause JOIN ne contient pas de requêtes supplémentaires. Elle relie deux ou plusieurs tables et sélectionne les données de celles-ci dans un seul ensemble de résultats. Elle est le plus souvent utilisée pour joindre des tables avec des clés primaires et étrangères. Vous pouvez pratiquer Les jointures en SQL dans notre Les jointures en SQL cours interactif. Il contient plus de 90 exercices pour réviser et pratiquer différents types de jointures. Vous pouvez également en savoir plus sur JOINs dans l'article "How to Practice Les jointures en SQL" d'Emil Drkušić.

Les sous-requêtes et JOINs peuvent toutes deux être utilisées dans une requête complexe pour sélectionner des données dans plusieurs tables, mais elles le font de manière différente. Parfois, vous avez le choix entre les deux, mais il existe des cas où une sous-requête est la seule véritable option. Nous allons décrire les différents scénarios ci-dessous.

Considérons deux tableaux simples, product et saleque nous allons utiliser dans nos exemples.

Voici la table product tableau.

Cette table contient les colonnes suivantes :

• id: l'identifiant du produit.
• name: le nom du produit.
• cost: le coût du produit.
• year: l'année de fabrication du produit.
• city: la ville dans laquelle le produit a été fabriqué.

Et l'autre tableau, sale:

qui comporte les colonnes suivantes :

• id: l'identifiant de la vente.
• product_id: l'identifiant du produit vendu.
• price: le prix de vente.
• year: l'année de la vente du produit.
• city: la ville où le produit a été vendu.

Nous allons utiliser ces deux tables pour écrire des requêtes complexes avec des sous-requêtes et des JOINs.

Quand réécrire des sous-requêtes avec des JOINs ?

Les débutants en SQL utilisent souvent des sous-requêtes lorsque les mêmes résultats peuvent être obtenus avec JOINs. Bien que les sous-requêtes soient plus faciles à comprendre et à utiliser pour de nombreux utilisateurs de SQL, JOINs est souvent plus efficace. JOINs est également plus facile à lire lorsque les requêtes deviennent plus complexes. Nous allons donc nous concentrer sur les cas où vous pouvez remplacer une sous-requête par une JOIN pour une meilleure efficacité et lisibilité.

Sous-requête scalaire

Le premier cas de figure est la sous-requête scalaire. Une sous-requête scalaire renvoie une seule valeur (une colonne et une ligne) qui sera utilisée par la requête externe. Voici un exemple.

Supposons que nous ayons besoin des noms et des coûts des produits qui ont été vendus pour 2 000 $.

Regardons le code avec une sous-requête :

SELECT name, cost 
FROM product
WHERE id=(SELECT product_id 
  FROM sale 
    WHERE price=2000 
    AND product_id=product.id
  );

et le résultat :

La requête externe sélectionne les noms (name) et le coût (cost) des produits. Comme nous ne voulons pas tous les produits, nous utilisons une clause WHERE pour filtrer les lignes sur les ID des produits renvoyés par la sous-requête.

Maintenant regardons la sous-requête. La table sale contient les enregistrements des ventes des produits. La sous-requête filtre d'abord les enregistrements pour ne retenir que ceux dont le prix de vente est égal à 2 000 $ (price=2000). Elle utilise ensuite les identifiants des produits (product_id) dans les ventes sélectionnées pour identifier les enregistrements de la table ( ). product table (product_id=product.id). Il s'agit d'une sous-requête corrélée, puisque la deuxième condition de la sous-requête fait référence à une colonne de la requête externe. Seuls deux produits ont été vendus à 2 000 $ : le fauteuil et la table de télévision.

Cette requête n'est pas très efficace. Comment la modifier ?

Nous pouvons construire une structure JOIN et obtenir le même résultat. Regardez la requête avec une JOIN:

SELECT p.name, p.cost 
FROM product p 
JOIN sale s ON p.id=s.product_id
WHERE s.price=2000;

Dans cette requête, nous connectons les deux tables product et sale avec un opérateur JOIN. Dans la condition JOIN, les enregistrements de la table product sont liés aux enregistrements de la table sale par le biais des identifiants des produits. À la fin, les lignes sont filtrées par une clause WHERE pour sélectionner l'enregistrement lorsque le prix de vente du produit est égal à 2 000 dollars.

Sous-requête dans la clause IN

Une autre sous-requête qui est facilement remplacée par une JOIN est celle utilisée dans un opérateur IN. Dans ce cas, la sous-requête renvoie à la requête externe une liste de valeurs.

Disons que nous voulons obtenir les noms et les coûts des produits vendus dans notre exemple.

SELECT name, cost 
FROM product 
WHERE id IN (SELECT product_id FROM sale);

La requête externe sélectionne les noms et les coûts des produits ; elle filtre ensuite les enregistrements dont les identifiants de produit figurent dans la liste renvoyée par la sous-requête. La sous-requête sélectionne les identifiants des produits dans la table sale (SELECT product_id FROM sale), de sorte que seuls les produits vendus sont renvoyés par cette requête dans le jeu de résultats final, comme ceci :

Il y a plus de produits dans la table product mais seuls quatre d'entre eux ont été vendus.

La requête ci-dessous renvoie le même résultat en utilisant un JOIN:

SELECT DISTINCT p.name, p.cost 
FROM product p 
JOIN sale s ON s.product_id=p.id;

Cette requête devient très simple. Elle relie les deux tables par ID de produit et sélectionne les noms et les coûts de ces produits. Il s'agit d'un INNER JOIN, donc si un produit n'a pas son ID dans la table, il ne sera pas renvoyé. sale dans la table, il ne sera pas renvoyé.

Remarquez que nous utilisons également le mot-clé DISTINCT pour supprimer les enregistrements en double. Cela est souvent nécessaire si vous transformez des sous-requêtes avec un IN ou un NOT IN en JOIN.

Vous voulez en savoir plus sur les sous-requêtes SQL avec l'opérateur IN ? Regardez un épisode de notre série Nous apprenons le SQL sur Youtube. N'oubliez pas de vous abonner à notre chaîne.

Sous-requête dans la clause NOT IN

Cette situation est identique à la précédente, mais ici la sous-requête est utilisée dans un opérateur NOT IN. Nous voulons sélectionner les noms et les coûts des produits qui n'ont pas été vendus.

Voici un exemple avec une sous-requête dans l'opérateur NOT IN:

SELECT name, cost 
FROM product 
WHERE id NOT IN (SELECT product_id FROM sale);

Les résultats :

La sous-requête renvoie les ID des produits de la table sale (les produits vendus) et les compare aux identifiants des produits dans la requête externe. Si un enregistrement de la requête externe ne trouve pas son ID de produit dans la liste renvoyée par la sous-requête, l'enregistrement est renvoyé.

Comment réécrire cette sous-requête avec JOIN? Vous pouvez le faire comme suit :

SELECT DISTINCT p.name, p.cost
FROM product p 
LEFT JOIN sale s ON s.product_id=p.id 
WHERE s.product_id IS NULL;

Cette requête relie les deux tables product et sale par les identifiants des produits. Vous devez également utiliser le mot-clé DISTINCT, comme nous l'avons fait lorsque nous avons transformé la sous-requête précédente avec un IN en un JOIN.

Remarquez que lors de la réécriture de la sous-requête dans NOT IN, nous avons utilisé LEFT JOIN et WHERE. De cette façon, vous commencez par tous les produits, y compris ceux qui ne sont pas vendus, puis vous ne sélectionnez que les enregistrements qui sont NULL dans la colonne product_id. L'adresse NULL indique que le produit n'a pas été vendu.

Sous-requêtes corrélées dans EXISTS et NOT EXISTS

Les sous-requêtes dans une EXISTS ou dans une NOT EXISTS sont également faciles à réécrire avec des JOIN.

La requête ci-dessous utilise une sous-requête pour obtenir les détails sur les produits qui n'ont pas été vendus en 2020.

SELECT name, cost, city
FROM product  
WHERE NOT EXISTS ( SELECT id  
  FROM sale WHERE year=2020 AND product_id=product.id );

Le résultat :

Pour chaque produit de la requête externe, la sous-requête sélectionne les enregistrements dont l'année de vente est 2020 (year=2020). S'il n'y a pas d'enregistrements pour un produit donné dans la sous-requête, la clause NOT EXISTS renvoie vrai.

Le jeu de résultats contient les produits dont l'année de vente est différente de 2020, ainsi que les produits sans aucun enregistrement dans la table sale table. Vous pouvez réécrire la même requête en utilisant un JOIN:

SELECT p.name, p.cost, p.city FROM product p 
LEFT JOIN  sale s ON s.product_id=p.id 
WHERE s.year!=2020 OR s.year IS NULL;

Ici, nous connectons la table product avec la table sale par le biais d'un opérateur LEFT JOIN. Cela nous permet d'inclure les produits qui n'ont jamais été vendus dans le jeu de résultats. La clause WHERE filtre les enregistrements en sélectionnant les produits n'ayant aucun enregistrement dans la table sale (s.year IS NULL) ainsi que les produits dont l'année de vente est différente de 2020 (s.year!=2020).

Quand vous ne pouvez pas remplacer une sous-requête par un JOIN

JOINLes jointures peuvent être efficaces, mais il existe des situations qui nécessitent une sous-requête et non une JOIN. Vous trouverez ci-dessous quelques-unes de ces situations.

Sous-requête dans FROM avec un GROUP BY

La première de ces situations est une sous-requête dans une clause FROM utilisant une GROUP BY pour calculer des valeurs agrégées.

Examinons l'exemple suivant :

SELECT city, sum_price  
 FROM  
(
  SELECT city, SUM(price) AS sum_price FROM sale 
  GROUP BY city 
) AS s
WHERE sum_price < 2100;

et le résultat :

Ici, la sous-requête sélectionne les villes et calcule la somme des prix de vente par ville. La somme de tous les prix de vente dans chaque ville de la table sale tableau est calculée par la fonction d'agrégation SUM(). En utilisant les résultats de la sous-requête, la requête externe sélectionne uniquement les villes dont le prix de vente total est inférieur à 2 100 $ (WHERE sum_price < 2100). Les leçons précédentes vous ont appris à utiliser des alias pour les sous-requêtes et à sélectionner une valeur agrégée dans une requête externe.

Sous-requête retournant une valeur agrégée dans une clause WHERE

Une autre situation dans laquelle vous ne pouvez pas réécrire une structure de sous-requête avec une JOIN est une valeur agrégée comparée dans une clause WHERE. Regardez cet exemple :

SELECT name FROM product
WHERE cost<(SELECT AVG(price) from sale);

Le résultat :

Cette requête récupère les noms des produits dont les coûts sont inférieurs au prix de vente moyen. Le prix de vente moyen est calculé à l'aide de la fonction d'agrégation AVG() et est renvoyé par la sous-requête. Le coût de chaque produit est comparé à cette valeur dans la requête externe.

Sous-requête dans une clause ALL

Une autre situation encore est celle d'une sous-requête avec une clause ALL.

SELECT name FROM product
WHERE cost > ALL(SELECT price from sale);

La sous-requête renvoie tous les prix de vente de la table sale table. La requête externe renvoie le nom du produit dont le prix de vente est supérieur au coût.

Le résultat :

Quand utiliser une sous-requête ou un JOIN ?

Nous avons passé en revue certaines utilisations courantes des sous-requêtes et les situations dans lesquelles certaines sous-requêtes peuvent être réécrites avec JOINà la place. Une JOIN est plus efficace dans la plupart des cas, mais il existe des cas où des constructions autres qu'une sous-requête ne sont pas possibles. Alors que les sous-requêtes peuvent être plus lisibles pour les débutants, les JOINs sont plus lisibles pour les codeurs SQL expérimentés lorsque les requêtes deviennent plus complexes. Une bonne pratique consiste à éviter les niveaux multiples de sous-requêtes imbriquées, car elles ne sont pas facilement lisibles et n'ont pas de bonnes performances. En général, il est préférable d'écrire une requête avec JOINs plutôt qu'avec des sous-requêtes si possible, surtout si les sous-requêtes sont corrélées.

Si vous souhaitez en savoir plus ou si vous voulez vous entraîner, consultez les sections sur les sous-requêtes dans le cours "SQL pour les débutants" ou le cours "Exercices Pratiques de SQL".