Utiliser des sous-requêtes autonomes ou corrélées

  • 3 minutes

Auparavant, nous avons examiné les sous-requêtes autonomes, dans lesquelles la requête interne est indépendante de la requête externe, s’exécute une fois et renvoie ses résultats à la requête externe. T-SQL prend également en charge les sous-requêtes corrélées, dans lesquelles la requête interne fait référence à la colonne dans la requête externe et s’exécute une fois par ligne, par concept.

Utilisation des sous-requêtes corrélées

Comme les sous-requêtes autonomes, les sous-requêtes corrélées sont des instructions SELECT imbriquées dans une requête externe. Les sous-requêtes corrélées peuvent également être des sous-requêtes scalaires ou à valeurs multiples. Elles sont généralement utilisées lorsque la requête interne doit faire référence à une valeur dans la requête externe.

Toutefois, à la différence des sous-requêtes autonomes, il existe des considérations spéciales à prendre en compte lors de l’utilisation de sous-requêtes corrélées :

  • Les sous-requêtes corrélées ne peuvent pas être exécutées séparément de la requête externe. Cette restriction complique le test et le débogage.
  • Contrairement aux sous-requêtes autonomes, qui sont traitées une fois, les sous-requêtes corrélées s’exécutent plusieurs fois. Logiquement, la requête externe s’exécute en premier et, pour chaque ligne retournée, la requête interne est traitée.

L’exemple suivant utilise une sous-requête corrélée pour retourner la commande la plus récente pour chaque client. La sous-requête se rapporte à la requête externe et fait référence à sa valeur CustomerID dans sa clause WHERE. Pour chaque ligne de la requête externe, la sous-requête recherche l’ID de commande maximal pour le client référencé dans cette ligne, et la requête externe vérifie si la ligne qu’elle examine est la ligne avec cet ID de commande.

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

Écriture de sous-requêtes corrélées

Pour écrire des sous-requêtes corrélées, tenez compte des instructions suivantes :

  • Écrivez la requête externe pour accepter le résultat de retour approprié de la requête interne. Si la requête interne est scalaire, vous pouvez utiliser des opérateurs d’égalité et de comparaison, tels que =, <, > et <>, dans la clause WHERE. Si la requête interne peut retourner plusieurs valeurs, utilisez un prédicat IN. Planifiez la gestion des résultats NULL.
  • Identifiez la colonne de la requête externe qui sera référencée par la sous-requête corrélée. Déclarez un alias pour la table qui est la source de la colonne dans la requête externe.
  • Identifiez la colonne de la table interne qui sera comparée à la colonne de la table externe. Créez un alias pour la table source, comme vous l’avez fait pour la requête externe.
  • Écrivez la requête interne pour récupérer des valeurs à partir de sa source, en fonction de la valeur d’entrée de la requête externe. Par exemple, utilisez la colonne externe de la clause WHERE de la requête interne.

La corrélation entre les requêtes interne et externe se produit lorsque la valeur externe est référencée par la requête interne pour comparaison. C’est cette corrélation qui donne à la sous-requête son nom.

Utilisation d’EXISTS

En plus de récupérer des valeurs d’une sous-requête, T-SQL fournit un mécanisme permettant de vérifier que des résultats seront retournés à partir d’une requête. Le prédicat EXISTS détermine si des lignes qui répondent à une condition spécifiée existent, mais retourne la valeur TRUE ou FALSE plutôt que les lignes elles-mêmes. Cette technique est utile pour valider des données sans impliquer la surcharge liée à la récupération et au traitement des résultats.

Lorsqu’une sous-requête est associée à la requête externe à l’aide du prédicat EXISTS, SQL Server gère les résultats de la sous-requête d’une manière spéciale. Au lieu de récupérer une valeur scalaire ou une liste à valeurs multiples à partir de la sous-requête, EXISTS vérifie simplement s’il existe des lignes dans le résultat.

Conceptuellement, un prédicat EXISTS équivaut à récupérer les résultats, à compter les lignes retournées et à comparer le total à zéro. Comparez les requêtes suivantes, qui retournent des détails sur les clients qui ont passé des commandes :

Le premier exemple de requête utilise COUNT dans une sous-requête :

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

La deuxième requête, qui retourne les mêmes résultats, utilise EXISTS :

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

Dans le premier exemple, la sous-requête doit compter chaque occurrence de chaque custid trouvée dans la table Sales.SalesOrderHeader, et comparer le résultat du comptage à zéro, simplement pour indiquer que le client a passé des commandes.

Dans la deuxième requête, EXISTS retourne la valeur TRUE pour un custid dès qu’une commande pertinente a été trouvée dans la table Sales.SalesOrderHeader. Un décompte complet de chaque occurrence est inutile. Notez également qu’avec EXISTS, la sous-requête n’est pas limitée au retour d’une seule colonne. Ici, nous avons SELECT *. Les colonnes retournées ne sont pas pertinentes, car nous vérifions uniquement si des lignes sont retournées, et non les valeurs contenues dans ces lignes.

Du point de vue du traitement logique, ces deux formes de requête sont équivalentes. Du point de vue des performances, le moteur de base de données peut traiter les requêtes différemment, car il les optimise en vue de leur exécution. Envisagez de tester chaque solution pour votre propre utilisation.

Notes

Si vous convertissez une sous-requête qui utilise COUNT(*) en sous-requête qui utilise EXISTS, vérifiez que la sous-requête utilise SELECT * et non SELECT COUNT(*). Dans la mesure où SELECT COUNT(*) retourne toujours une ligne, EXISTS retourne toujours TRUE.

Une autre application utile d’EXISTS consiste à appliquer NOT à la sous-requête, comme dans l’exemple suivant, qui renverra tout client qui n’a jamais passé de commande :

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);

SQL Server n’aura pas à retourner de données sur les commandes associées pour les clients qui ont passé des commandes. Si un custid est trouvé dans la table Sales.SalesOrderHeader, la valeur de NOT EXISTS est FALSE et l’évaluation se termine rapidement.

Pour écrire des requêtes qui utilisent EXISTS avec des sous-requêtes, tenez compte des instructions suivantes :

  • Le mot clé EXISTS se trouve directement après WHERE. Aucun nom de colonne (ou autre expression) ne le précède, à moins qu’il soit également utilisé.
  • Dans la sous-requête, utilisez SELECT *. Aucune ligne n’est retournée par la sous-requête, donc aucune colonne ne doit être spécifiée.