Sous-requête corrélée en SQL : Guide du débutant

Parfois, l'utilisation d'une sous-requête corrélée SQL est le seul moyen de résoudre une instruction. Mais ces sous-requêtes peuvent être très lentes. Dans ce post, nous allons parler de quand utiliser une sous-requête corrélée, pourquoi, et comment le faire.

Lessous-requêtes sont une ressource importante pour augmenter la puissance expressive de SQL. Si vous n'avez pas lu notre article précédent, les sous-requêtes sont simplement une instruction SELECT à l'intérieur d'une autre SELECT. Nous pouvons les utiliser à différents endroits d'une SELECT, par exemple dans les clauses WHERE, HAVING ou FROM. Nous pouvons même les utiliser dans d'autres instructions comme UPDATE ou DELETE.

Comme les sous-requêtes simples, une sous-requête corrélée SQL contient une requête dans une requête. Elle doit son nom au fait que les deux requêtes sont liées ; la requête interne utilise des informations obtenues à partir de la requête externe (par exemple, à partir d'une table référencée dans la requête externe). Pour les programmeurs parmi vous, cela ressemble à une structure de boucle imbriquée.

Avant de passer à un exemple de sous-requête corrélée en SQL, rafraîchissons rapidement nos connaissances sur les sous-requêtes simples (non corrélées).

Une sous-requête simple

L'exemple SQL suivant utilise une sous-requête simple pour obtenir une liste des employés qui gagnent plus que le salaire moyen de l'entreprise. Dans ce cas, la sous-requête apparaît en gras, et son résultat est le salaire moyen de l'entreprise. La requête externe compare le salaire de chaque employé à la moyenne de l'entreprise. Facile !

SELECT 
  lastname, 
  firstname, 
  salary 
FROM employee 
WHERE salary > (SELECT avg(salary) 
                FROM employee)

Exemple 1 : Une simple sous-requête SQL non corrélée

Ensuite, il y a les sous-requêtes corrélées. Elles sont très spéciales, car elles constituent parfois le seul moyen de résoudre une requête. Toutefois, réfléchissez bien avant d'utiliser une sous-requête corrélée en SQL. Elles peuvent être lentes, comme nous l'expliquerons plus tard.

Utilisation d'une sous-requête corrélée en SQL

Commençons par un exemple de sous-requête corrélée en SQL. Supposons que nous voulions trouver tous les employés dont le salaire est supérieur au salaire moyen de leur département. Nous utiliserions la requête suivante. Une fois de plus, j'ai mis en gras la sous-requête :

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE e1.salary > (SELECT avg(salary)
                   FROM employee e2 
                   WHERE e2.dept_id = e1.dept_id)

Exemple 2 : Une sous-requête corrélée en SQL

La principale différence entre une sous-requête corrélée SQL et une sous-requête simple est que les sous-requêtes corrélées font référence à des colonnes de la table externe. Dans l'exemple ci-dessus, e1.dept_id i est une référence à la table de sous-requête externe. Pour identifier une requête corrélée, il suffit de rechercher ce type de références. Si vous en trouvez au moins une, vous avez une sous-requête corrélée SQL !

Prenons un autre exemple. Supposons que nous voulions obtenir les noms des départements qui ont plus de 10 employés. Nous pouvons utiliser la sous-requête SQL corrélée suivante :

SELECT deptname
FROM department d1
WHERE 10 < (SELECT count(*)
            FROM employee e
            WHERE e.dept_id = d1.dept_id)

Exemple 3 : Une autre sous-requête corrélée en SQL

Voici un dernier exemple. Nous avons mentionné que les sous-requêtes peuvent faire partie des clauses WHERE, FROM, HAVING et SELECT. Dans cet exemple, nous allons utiliser une sous-requête SQL corrélée dans la liste SELECT pour trouver chaque nom d'employé, son salaire et le salaire moyen de son département. Nous obtiendrons le salaire moyen en utilisant une sous-requête corrélée dans la clause SELECT.

Voici le code :

SELECT 	
  lastname,
  firstname, 
  salary,
  (SELECT avg(salary) 
    FROM employee e2
    WHERE e2.dep_id = e1.dep_id) AS avg_dept_salary
FROM employee e1

Exemple 4 : Une sous-requête SQL corrélée dans la liste SELECT

Combien de fois une sous-requête SQL corrélée est-elle exécutée ?

Supposons que nous ayons une table appelée "assigned_to_project"qui stocke les noms des employés affectés à des projets. Nous voulons trouver tous les employés qui ne sont affectés à aucun projet. La solution est la requête suivante :

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE NOT EXISTS (SELECT project_id
                  FROM assigned_to_project 
                  WHERE employee_id = e1.employee_id)

Exemple 5 : Une sous-requête corrélée en SQL

Tout d'abord, cette requête est facile à analyser. La clause NOT EXISTS est TRUE lorsque la sous-requête renvoie un ensemble de résultats vide. Cela ne se produit que pour les employés qui ne sont affectés à aucun projet. Encore une fois, c'est assez facile !

Cependant, l'objectif de cette section est d'analyser combien de fois la sous-requête corrélée SQL est exécutée. Essayez de déterminer laquelle des affirmations suivantes est la bonne :

1. Elle est exécutée une seule fois.
2. Elle est exécutée une fois pour chaque projet.
3. Elle est exécutée une fois pour chaque employé non affecté à un projet.
4. Elle est exécutée une fois pour chaque employé de l'entreprise.

Analysons ces options. Supposons que nous ayons 1 000 employés et 20 projets. De plus, nous avons 800 employés qui sont déjà affectés à un projet. Dans ce cas, la première option a une exécution, la deuxième a 20 exécutions, la troisième a 200 exécutions et la dernière option a 1 000 exécutions.

Si vous avez dit que la bonne réponse était "une fois pour chaque employé de l'entreprise", vous avez raison. Si nous parcourons l'énoncé, cette réponse devient évidente ; nous devons vérifier si chaque employé a des projets ou non. Mais d'après l'exemple ci-dessus, cela signifie que la sous-requête sera exécutée 1 000 fois ! En termes de performances, c'est le pire scénario des quatre.

Comme les sous-requêtes corrélées ont tendance à impliquer de nombreuses exécutions, elles ont aussi tendance à être lentes. En règle générale, pour cette raison, nous essayons toujours d'éviter d'utiliser une sous-requête corrélée en SQL. Mais comme nous l'avons déjà mentionné, la corrélation est parfois le seul moyen de résoudre une requête.

Quand utiliser une sous-requête corrélée en SQL ?

A ce stade de l'article, le lecteur doit se demander : Quand utiliser une sous-requête corrélée en SQL ? En fait, il y a des cas où nous devons le faire. C'est particulièrement vrai dans les requêtes où l'on recherche ce que l'on pourrait appeler des éléments négatifs.

Voici un exemple de requête "négative". Supposons que nous ayons un tableau d'historique des paiements avec une colonne appelée payment_type qui indique si un paiement est un salaire normal, une prime ou une récompense. Si nous voulons une requête qui renvoie les employés qui n'ont jamais reçu de prime, nous utiliserons cette requête :

SELECT 
  lastname, 
  firstname
FROM employees e1
WHERE NOT EXISTS (SELECT ph.lastname 
                  FROM payment_history ph 
                  WHERE ph.emp_id = e1.employee_id 
                  AND ph.payment_type =’award’)

Exemple 6 : Une sous-requête SQL corrélée utilisant NOT EXISTS

EXISTS NOT EXISTS est un opérateur unaire. Il n'a qu'un seul opérande, qui est une sous-requête (corrélée ou non). Si la sous-requête renvoie au moins un enregistrement, alors EXISTS renvoie TRUE. Si la sous-requête ne renvoie aucun enregistrement, EXISTS renvoie FALSE. Dans ce cas, vous devez utiliser une sous-requête corrélée pour obtenir vos résultats.

Utilisation de sous-requêtes corrélées dans une instruction UPDATE ou DELETE

On trouve parfois des sous-requêtes corrélées dans des instructions UPDATE ou DELETE. L'exemple suivant UPDATE comporte une sous-requête SQL corrélée qui permet d'obtenir la nouvelle valeur de la colonne all_money_made:

UPDATE employee emp
SET all_money_made = (SELECT SUM(payment)
                      FROM payment_history 
                      WHERE employee_id = emp.emp_id)

Exemple 7 : Une sous-requête SQL corrélée dans une mise à jour (UPDATE)

Cet article nous a montré quand utiliser une sous-requête corrélée en SQL. Les requêtes recherchant des valeurs négatives sont de bons candidats, bien qu'il existe d'autres occasions où une corrélation est la seule véritable option. Nous avons également vu combien de fois une sous-requête corrélée est exécutée - généralement de très nombreuses fois. C'est leur plus grand inconvénient.

Essayez vous-même

Lessous-requêtes corrélées constituent une ressource importante pour le développeur SQL. Pour en savoir plus et améliorer vos compétences en matière de sous-requêtes, essayez le cours LearnSQL.fr's SQL pour les débutants. Nous avons une section spécifique pour les sous-requêtes, ainsi que de nombreux exercices et exemples !