Ceci est le deuxième dune série darticles sur les sous-requêtes. Dans cet article, nous abordons les sous-requêtes dans la liste des colonnes de linstruction SELECT. Dautres articles traitent de leurs utilisations dans dautres clauses.
Tous les exemples de cette leçon sont basés sur Microsoft SQL Server Management Studio et la base de données AdventureWorks2012. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon Guide Premiers pas avec SQL Server.
Utilisation de sous-requêtes dans linstruction Select
Lorsquune sous-requête est placée dans la liste de colonnes, elle est utilisée pour renvoie des valeurs uniques. Dans ce cas, vous pouvez considérer la sous-requête comme une expression à valeur unique. Le résultat renvoyé nest pas différent de lexpression «2 + 2». Bien sûr, les sous-requêtes peuvent également renvoyer du texte, mais vous comprenez!
Lorsque vous travaillez avec des sous-requêtes, linstruction principale est parfois appelée requête externe. Les sous-requêtes sont placées entre parenthèses, ce qui les rend plus faciles à repérer .
Soyez prudent lorsque vous utilisez des sous-requêtes. Elles peuvent être amusantes à utiliser, mais à mesure que vous ajoutez plus à votre requête, elles peuvent commencer à ralentir votre requête.
Sous-requête simple pour calculer la moyenne
Commençons par une simple requête pour afficher SalesOrderDetail et comparons-la à la moyenne globale de SalesOrderDetail LineTotal. Linstruction SELECT que nous utiliserons est:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
Cette requête renvoie les résultats comme suit:
La sous-requête, qui est affichée en rouge ci-dessus, est exécutée en premier pour obtenir le LineTotal moyen.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Ce résultat est ensuite rebranché dans la liste des colonnes, et la requête se poursuit. Il y a plusieurs choses que je veux souligner :
- Les sous-requêtes sont placées entre parenthèses .
- Lorsque des sous-requêtes sont utilisées dans une instruction SELECT, elles ne peuvent renvoyer quune seule valeur. Cela devrait avoir du sens, la simple sélection dune colonne renvoie une valeur pour une ligne, et nous devons suivre le même modèle.
- En général, la sous-requête nest exécutée quune seule fois pour lensemble de la requête, et son résultat est réutilisé . En effet, le résultat de la requête ne varie pas pour chaque ligne renvoyée.
- Il est important dutiliser des alias pour les noms de colonne afin daméliorer la lisibilité.
Sous-requête simple dans lexpression
Comme vous pouvez vous y attendre, le résultat dune sous-requête peut être utilisé dans dautres expressions. En nous basant sur lexemple précédent, utilisons la sous-requête pour déterminer dans quelle mesure notre LineTotal diffère de la moyenne.
La variance est simplement le LineTotal moins le total Average Line. Dans la sous-requête suivante, je lai colorée en bleu. Voici la formule de la variance:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
Linstruction SELECT entre parenthèses est la sous-requête. Comme lexemple précédent, cette requête sexécutera une fois, retournera une valeur numérique, qui est ensuite soustraite de chaque valeur LineTotal.
Voici la requête sous sa forme finale:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Voici le résultat:
Lorsque je travaille avec des sous-requêtes dans des instructions select, je construis habituellement et testez dabord la sous-requête. Les instructions SELECT peuvent se compliquer très rapidement. Il est préférable de les construire petit à petit. En construisant et en testant les différentes parties séparément, cela aide vraiment au débogage.
Requêtes corrélées
Il existe des moyens dincorporer les valeurs de la requête externe dans les clauses de la sous-requête. Ces types de requêtes sont appelés sous-requêtes corrélées, car les résultats de la sous-requête sont connectés, sous une forme ou une autre, aux valeurs de la requête externe. Les requêtes corrélées sont parfois appelées requêtes synchronisées.
Si vous ne parvenez pas à savoir ce que signifie corréler, consultez cette définition de Google:
Corréler: « avoir une relation ou une connexion mutuelle, dans laquelle une chose affecte ou dépend dune autre. »
Une utilisation typique pour une sous-requête corrélée est dutiliser lune des colonnes de la requête externe dans la clause WHERE de la requête interne. Cest du bon sens dans de nombreux cas. restreindre la requête interne à un sous-ensemble de données.
Exemple de sous-requête corrélée
Nous fournirons un exemple de sous-requête corrélée en rapportant chaque SalesOrderDetail LineTotal, et le LineTotal moyen pour lensemble des ventes Commande.
Cette demande diffère considérablement de nos exemples précédents car la moyenne que nous calculons varie pour chaque commande client.
Cest là que les sous-requêtes corrélées entrent en jeu. Nous pouvons utiliser un valeur de la requête externe et intégrez-la dans les critères de filtre de la sous-requête.
Prenons un regardez comment nous calculons le total moyen des lignes. Pour ce faire, jai rassemblé une illustration qui montre linstruction SELECT avec la sous-requête.
Pour en savoir plus sur la diagramme. Linstruction SELECT se compose de deux parties, la requête externe et la sous-requête. La requête externe est utilisée pour récupérer toutes les lignes SalesOrderDetail.La sous-requête est utilisée pour rechercher et résumer les lignes de détails de la commande client pour un SalesOrderID spécifique.
Si je devais verbaliser les étapes nous allons prendre, je les résumerais comme suit:
- Obtenez le SalesOrderID.
- Renvoyez le LineTotal moyen de tous les éléments SalesOrderDetail où le SalesOrderID correspond.
- Passez au SalesOrderID suivant dans la requête externe et répétez les étapes 1 et 2.
La requête que vous pouvez exécuter dans la base de données AdventureWork2012 est:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Voici les résultats de la requête:
Il y a quelques éléments à signaler.
- Vous pouvez voir que jai utilisé des alias de colonne pour faciliter la lecture des résultats de la requête.
- Jai également utilisé un alias de table, SOD, pour le requête externe. Cela permet dutiliser les valeurs de la requête externe dans la sous-requête. Sinon, la requête nest pas corrélée!
- Lutilisation des alias de table permet de savoir quelles colonnes proviennent de chaque table.
Décomposition de la sous-requête corrélée
Essayons maintenant de décomposer cela en utilisant SQL.
Pour commencer, supposons que nous allons simplement prendre notre exemple pour SalesOrderDetailID 20. Le SalesOrderID correspondant est 43661.
Pour obtenir le LineTotal moyen pour cet élément, cest facile
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Ceci renvoie la valeur 2181,765240.
Maintenant que nous avons la moyenne, nous pouvons branchez-le dans notre requête
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
En utilisant des sous-requêtes, cela devient
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
La requête finale est :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Sous-requête corrélée avec une table différente
Une sous-requête corrélée, ou dailleurs nimporte quelle sous-requête, peut utiliser une table différente de celle la requête externe. Cela peut être utile lorsque vous travaillez avec une table « parente », telle que SalesOrderHeader, et que vous souhaitez inclure dans le résultat un résumé des lignes enfants, telles que celles de SalesOrderDetail.
Renvoyons le OrderDate, TotalDue et nombre de lignes de détail de la commande client. Pour ce faire, nous pouvons utiliser le diagramme suivant pour prendre nos repères:
Pour ce faire, nous allons inclure une sous-requête corrélée dans notre instruction SELECT pour renvoyer le COUNT de lignes SalesOrderDetail. Nous nous assurerons que nous comptons lélément SalesOrderDetail correct en filtrant sur SalesOrderID de la requête externe.
Voici linstruction SELECT finale:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Les résultats sont:
Voici quelques points à noter avec cet exemple:
- La sous-requête sélectionne des données dans une table différente de celle de la requête externe.
- Jai utilisé une table et alias de colonne pour faciliter la lecture du SQL et des résultats.
- Assurez-vous de vérifier k votre clause where! Si vous oubliez dinclure le nom ou les alias de la table dans la clause WHERE de la sous-requête, la requête ne sera pas corrélée.
Sous-requêtes corrélées par rapport aux jointures internes
Cest important pour comprendre que vous pouvez obtenir les mêmes résultats en utilisant une sous-requête ou une jointure. Bien que les deux renvoient les mêmes résultats, chaque méthode présente des avantages et des inconvénients!
Prenons le dernier exemple où nous comptons les éléments de campagne pour les éléments SalesHeader.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Cette même requête peut être effectuée en utilisant INNER JOIN avec GROUP BY comme
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
Laquelle est la plus rapide?
Vous constaterez que beaucoup de gens diront déviter les sous-requêtes car elles sont plus lentes. Ils soutiendront que la sous-requête corrélée doit « sexécuter » une fois pour chaque ligne renvoyée dans la requête externe, alors que INNER JOIN ne doit faire quun seul passage dans les données.
Moi-même? Je dis vérifier le plan de requête. Jai suivi mes propres conseils pour les deux exemples ci-dessus et jai trouvé que les plans étaient les mêmes!
Cela ne veut pas dire que les plans changeraient sil y avait plus de données, mais mon point est que vous ne devez pas seulement faire des hypothèses. La plupart des optimiseurs de SGBD SQL sont vraiment efficaces pour déterminer la meilleure façon dexécuter votre requête. Ils prendront vos syntaxes, comme une sous-requête, ou INNER JOIN, et les utiliseront pour créer un plan dexécution réel.
Lequel est le plus facile à lire?
Selon ce avec quoi vous êtes à laise, vous trouverez peut-être lexemple INNER JOIN plus facile à lire que la requête corrélée. Personnellement, dans cet exemple, jaime la sous-requête corrélée car elle semble plus directe. Il mest plus facile de voir ce qui est compté.
Dans mon esprit, INNER JOIN est moins direct. Vous devez dabord voir que toutes les lignes de détails des ventes sont retournées à la main puis résumées. Vous ne comprenez pas vraiment cela tant que vous n’avez pas lu la déclaration en entier.
Laquelle est la meilleure?
Dites-moi ce que vous en pensez. Jaimerais savoir si vous préférez utiliser la sous-requête corrélée ou lexemple INNER JOIN.