Conversion de sous-requêtes en jointures

Toutes les requêtes ne se ressemblent pas, notamment en termes de performances. Dans cet article, nous allons voir comment vous pouvez convertir des sous-requêtes SQL en jointures pour une meilleure efficacité.

Quand dois-je utiliser des sous-requêtes SQL ?

Bonne question ! Malheureusement, il n'y a pas de réponse concrète. Les débutants en SQL ont tendance à abuser des sous-requêtes. Généralement, une fois qu'ils ont découvert que la construction SQL fonctionne dans une situation donnée, ils essaient d'appliquer cette même approche à d'autres situations. C'est tout à fait naturel.

Cependant, dans certains cas, une sous-requête peut être remplacée par un JOIN plus efficace. Si vous pouvez éviter une sous-requête et la remplacer par une clause JOIN, vous devez le faire sans hésiter. Mais bien sûr, dans certains cas, l'utilisation d'une sous-requête est le seul moyen de résoudre une question de données. Dans cet article, je vais montrer des exemples des deux cas : quand une sous-requête est indispensable et quand une sous-requête doit être évitée et remplacée par un JOIN.

Les données

Avant de passer aux exemples, examinons brièvement la base de données d'exemple que nous allons utiliser. La base de données comporte deux tables représentant les statistiques de production d'une entreprise fictive d'exploitation de pommes nommée EverRed. L'entreprise possède trois fermes.

La première table est current_year_productionqui contient des informations sur le nombre de pommes produites l'année en cours par chaque exploitation, ainsi que sur la superficie et le nombre d'arbres de chaque exploitation. Le deuxième tableau, production_historycontient des informations sur la production passée de chaque exploitation. Vous trouverez ci-dessous quelques exemples de données provenant de ces deux tableaux.

Exemple : remplacement d'une sous-requête par un JOIN

Supposons que vous soyez un analyste de données SQL travaillant chez EverRed. Le propriétaire de l'entreprise souhaite que vous obteniez le nom des exploitations où l'entreprise produit plus de pommes pendant l'année en cours que pendant l'année précédente (2017).

Solution utilisant une sous-requête :

SELECT farm_name, 
FROM current_year_production CYP
WHERE production_in_kg > (
  SELECT production_in_kg 
  FROM production_history PH  
  WHERE PH.farm_id = CYP.farm_id
  AND year = 2017
) 

Solution utilisant une clause JOIN :

SELECT farm_name, 
FROM current_year_production CYP 
JOIN production_history PH
  ON PH.farm_id = CYP.farm_id 
WHERE PH.year = 2017
  AND CYP.production_in_kg > PH.production_in_kg 

La différence entre ces deux approches réside dans les performances. Alors que la clause JOIN dans le deuxième exemple ne doit être exécutée qu'une seule fois, la sous-requête dans le premier exemple sera exécutée une fois par exploitation. Dans ce cas, nous n'avons que trois fermes, la différence est donc négligeable. Mais que se passerait-il si vous travailliez pour une entreprise plus importante qui possède 10 000 fermes mondiales ? La sous-requête devrait être exécutée 10 000 fois. Il est clair qu'une sous-requête est inefficace pour nos besoins ici.

De plus, dans une base de données de test avec seulement quelques fermes, les deux requêtes s'exécutent avec un temps de réponse acceptable ; cependant, lorsque nous passons à une base de données productive (où le volume de données est généralement beaucoup plus élevé), le temps de réponse de l'approche par sous-requête augmentera de manière significative, tandis que le temps de réponse de l'approche JOIN restera stable.

Le résultat des deux requêtes équivalentes précédentes est :

Exemple : quand les sous-requêtes sont la seule solution possible

Supposons maintenant que le propriétaire de l'entreprise, après avoir lu les résultats que vous avez fournis dans la requête précédente, vous demande d'obtenir les noms des exploitations qui produisent cette année plus de pommes au mètre carré que la moyenne historique. Cela semble complexe, mais c'est plus facile qu'il n'y paraît.

Solution utilisant une sous-requête :

SELECT
  farm_name, 
  production_in_kg / area AS "production_per_meter"
FROM Current_year_production 
WHERE production_in_kg / area > (
  SELECT AVG(PH.production_in_kg / CYP.area) 
  FROM production_history PH 
  JOIN Current_year_production CYP  
    ON PH.farm_id = CYP.farm_id 
) 

Nous ne pouvons pas remplacer cette sous-requête par un JOIN car nous n'avons pas de table avec la moyenne calculée précédemment. En d'autres termes, nous devons d'abord calculer la moyenne historique. Et pour ce faire, nous avons besoin d'un GROUP BY, qui peut briser la relation biunivoque nécessaire pour un JOIN. Un autre point à noter est que la métrique "pommes par mètre carré" est obtenue avec l'expression suivante :

	production_in_kg / area

Nous avons utilisé la métrique "pommes par mètre carré" car nous avons besoin d'un moyen de comparer la productivité des différentes exploitations et de les classer. La "production_en_kg" totale d'une ferme n'est pas une valeur comparable, car il est probable, par exemple, que la plus grande ferme aura un meilleur production_in_kg. Nous divisons donc la "production_en_kg" par la superficie de chaque exploitation pour normaliser les valeurs et créer une mesure comparable.

Nous trouvons que la moyenne historique de la production par mètre carré est de 0,42. Alors le résultat de la requête précédente est :

Exemple : quand les JOIN et les sous-requêtes sont aussi efficaces les uns que les autres.

Comme dernière question sur les données, essayons d'obtenir les années où l'entreprise a produit moins de pommes que l'année en cours. Nous pouvons écrire cette requête en utilisant deux approches différentes.

Solution utilisant une sous-requête :

SELECT year, sum(production_in_kg) 
FROM production_history PH
GROUP BY year
HAVING sum(production_in_kg) < (
  SELECT sum(production_in_kg)
  FROM current_year_production
)

Solution utilisant une clause JOIN :

SELECT year, sum(PH.production_in_kg) 
FROM production_history PH 
JOIN current_year_production CYP
  ON PH.farm_id = CYP.farm_id
GROUP BY year
HAVING sum(PH.production_in_kg) < sum(CYP.production_in_kg)

Vous pouvez constater que ces deux requêtes sont très similaires ; la principale différence réside dans la clause JOIN et la sous-requête. Dans ce cas, les deux requêtes sont aussi efficaces l'une que l'autre - la sous-requête est exécutée une fois dans la clause HAVING, ce qui évite tout problème de performance.

Essayez vous-même !

Pour conclure, il est important de noter que les sous-requêtes et les JOIN sont deux ressources très importantes pour un développeur SQL. Nous avons vu des exemples où nous pouvons remplacer une sous-requête par un JOIN et des exemples où nous ne pouvons pas effectuer un tel remplacement. Et parfois, les sous-requêtes et les JOIN sont aussi efficaces les unes que les autres.

Mais comment savoir quand utiliser une sous-requête et quand utiliser un JOIN ? En toute honnêteté, la seule façon de développer votre intuition est de résoudre régulièrement des exercices SQL. Si vous cherchez à affiner vos compétences en SQL, notre site Web Exercices Pratiques de SQL propose 88 problèmes pratiques pour une révision complète.

Restez à l'écoute pour d'autres articles !