Sous-requetes (subqueries) MySQL

image_pdfimage_print

ysql show engine

Une sous-requête est une requête SELECT qui apparait a l’intérieur d’une autre requête.

Depuis la version 4.1, MySQL supporte toutes les formes de sous-requêtes, y compris les sous-requêtes corrélées (correlated subqueries).

Les principales syntaxes possibles sont les suivantes:

SELECT . . . FROM table1 WHERE col operateur (SELECT . . . FROM table2);
ou
SELECT . . . FROM  (SELECT . . . FROM table1) AS nom

On parle de requête externe, pour le premier membre et de sous requête, et de requête imbriquée ou de requête interne pour la suite.

Concrètement MySQL évalue d’abord la requête interne, et grâce à son résultat peut évaluer la clause WHERE de la requête. Il faut également procéder dans cet ordre, en partant de la requête la plus interne (la plus basse) vers la plus externe (la plus haute).

Une sous requete peut apparaître également dans un ordre UPDATE ou DELETE.

Voici par exemple deux ordres de mise à jour utilisant des sous-requêtes :

DELETE FROM employes 
WHERE no_service IN (SELECT no_serviceFROM services);
UPDATE employes SET salaire= 1.1*salaire
WHERE no_service IN (SELECT no_service FROM services
WHERE localisation='MARSEILLE'

Pour la distinguer de la requête principale, une sous-requête doit être entre parenthèses.

Les avantages des sous-requêtes:

  • elles sont plus ‘naturelles’ et plus lisibles que les jointures
  • elles sont mieux structurées

En pratique, toute sous requête peut être écrite de manière équivalente avec une jointure. Dans les premières versions de MySQL seules les jointures étaient utilisable pour répondre à des questions complexes…

Les opérateurs spécifiques ANY, ALL, IN, SOME

Dans la mesure ou on ne sait pas en général combien de lignes va ramener la requête interne, l’opérateur ‘=’ sera rarement utilisé à moins d’être sûr que dans tous les cas cette dernière ramène une seule ligne…

Dans le cas général on utilise des opérateurs spécifiques, dont le deuxième opérande peut être une liste de valeur. Le cas le plus intuitif est l’opérateur ‘IN’.

L’opérateur IN

Renvoie les lignes qui vérifie qu’une colonne appartient à une liste de valeurs (renvoyée par un sous SELECT)

Ainsi on pourra écrire, une requête du genre:

SELECT * FROM peinture
WHERE couleur IN (SELECT DISTINCT coloris FROM palette)

Ce SELECT renverra uniquement les lignes de PEINTURE dont la couleur appartient à la liste des différents coloris…

L’opérateur ANY

Renvoie des lignes si la comparaison est vraie pour au moins une des lignes retournées par le sous SELECT. ANY suit obligatoirement un opérateur de comparaison (=, <, >, <=,  . . .).

Voici la syntaxe générale:

SELECT s1 FROM t1
WHERE s1 opérateur ANY (SELECT s1 FROM t2);

Ainsi si l’on veut obtenir la liste des employés dont le salaire est plus important que l’un des salaires (au moins un) du service no 10 :

mysql> SELECT nom,salaire, no_service
FROM employes WHERE salaire > any 
    ( SELECT salaire FROM employes
      WHERE no_service=10 );

Pour mieux comprendre le ‘> any’ on pourrait dire que si quelqu’un a un salaire supérieur à au moins un des salaire du département 10, il  est supérieur à cellui qui gagne le moins, ou en d’autres termes au salaire minimum du service no 10, ce qui en SQL s’écrit:

mysql> SELECT nom,salaire, no_service
FROM employes 
WHERE salaire > 
    ( SELECT MIN(salaire) FROM employes
      WHERE no_service=10 );

’ IN’ est un alias de ‘=ANY’ et ‘SOME’ un alias de ‘ANY’ 

Sous requêtes EXISTS

Une sous requête EXISTS est vraie si le résultat existe ou en d’autres termes si elle retourne au moins une ligne.

La syntaxe est la suivante:

SELECT * FROM t1
WHERE EXISTS
(SELECT * FROM t2)

Voici un exemple:

mysql> SELECT nom, salaire, commission FROM employes
WHERE EXISTS (SELECT commission FROM employes
    WHERE commission IS NOT NULL)

Sous requêtes multi colonnes

Si une sous requête ne retourne qu’une seule ligne, il est possible de faire des comparaisons sur plusieurs colonnes en même temps, avec un nombre correspondant de colonnes dans le sous SELECT.

Voici la syntaxe:

SELECT * FROM t1
WHERE (c1,c2,c3, . . . ) = (SELECT c1,c2,c3, . . . FROM t2)

En fait ceci permet d’écrire de manière plus compacte un ‘ET logique’. Ainsi les deux requêtes suivantes donnent le même résultat :

mysql> SELECT nom, mgr, job FROM employes
WHERE mgr =
    (SELECT mgr, job  FROM employes 
    WHERE nom='Murdock')
AND job = 
    (SELECT job  FROM employes 
        WHERE nom='Murdock')
SELECT nom, mgr, job FROM employes
WHERE (mgr, job ) = 
    (SELECT mgr, job  FROM employes 
    WHERE nom='Murdock')

Sous requêtes corrélées (correlated Subqueries)

Une sous requête corrélée est une requête interne qui référence une table de la requête externe.

En d’autres termes, les données du sous SELECT sont corrélées avec les données du SELECT principal. Chaque valeur d’une colonne de la table principale T1 est comparée avec chaque valeur de la sous table T2, comme dans une boucle imbriquée en programmation…mais sans boucle ici.

La syntaxe est la suivante:

SELECT * FROM t1 WHERE c1 operateur      (SELECT … FROM t2 WHERE t1.c1 = t2.c2)

La table t1 est externe et t2 interne ici. Dans le sous SELECT apparaissent bien t1 et t2…

Supposons par exemple que nous cherchions les employés qui gagnent plus qu’un chef (on suppose ici qu’un chef a le job ‘Manager’ et que le ‘Président’ est exclus de cette liste. Cela nous donne assez facilement le SQL suivant :

mysql> SELECT nom, salaire FROM employes
WHERE salaire > ANY SELECT salaire FROM employes
WHERE job='Manager' ;

Dans cette liste on obtient également les chefs qui gagnent plus qu’un chef. Si l’on voulait éliminer les chefs du résultat, pour n’avoir que les vrais employés, il suffirait de les exclure du WHERE ainsi:

mysql> SELECT nom, salaire FROM employes
WHERE salaire > ANY (SELECT salaire FROM employes
    WHERE job='Manager' )
AND job!= 'Manager'

Attention ici la dernière ligne porte bien sur la requête principale et pas sur le WHERE de la sous-requête. Si l’on y réfléchit d’ailleurs que donnerait un SELECT avec au même niveau une condition WHERE job=’Manager’ AND job!= ‘Manager’ …et bien rien bien sur car ces conditions sont contradictoires et ne peuvent être vraies au même moment.

On vient donc de voir qu’il est assez facile de trouver quelqu’un gagnant plus qu’un manager, mais quelqu’un gagnant plus que SON manager, qu’en est-il ?

La solution est que pour chaque ligne de la table principale des employés la sous-requête regarde le no de manager correspondant, et donc qu’elle garde le ‘contact’ avec la requête principal, à chaque itération. C’est exactement pour cela qu’on été inventées les sous-requêtes corrélées !

Basons nous sur la syntaxe du début de paragraphe pour écrire le SELECT, et nous essaierons en suite de l’analyser.

la syntaxe générale était donc :

SELECT * FROM t1
WHERE c1 operateur
(SELECT … FROM t2 WHERE t1.col1 = t2.col2)

ce qui sur notre question précise cela commencerait comme cela :

mysql> SELECT nom, salaire, mgr FROM employes
WHERE salaire >
(SELECT salaire FROM employes
WHERE t1.c1 = t2.c2)

Mais il nous faut expliciter la condition t1.col1 = t2.col2. Dans notre contexte, quelle condition nous permet de dire que nous sommes en train de lire dans la sous-requête (R2) l’employé qui est bien le manager de l’employé de la requête principale (R1)? Il faut en y réfléchissant un peu que le no du manager de l’employé vu dans R1 soit le no de l’employé vu dans R2.

En clair cette condition s’écrit

employes.manager = employes.no_employe.

Cette formulation est encore un peu ambiguë. Les requêtes R1 et R2 portent sur la même table, pour être sûr de lever toute ambigüité, nous allons nommer la table principale différemment de la table secondaire, grâce à un Alias de table, respectivement table1 et table2. Ceci nous donne la condition, désormais moins sujette à confusion:

…table1.manager = table2.no_employe

et donc le SELECT corrélé complet devient (en introduisant les alias table1 et table2):
SELECT nom, salaire, mgr FROM employes  »table1 »
WHERE salaire >
(SELECT salaire FROM employes  »table2 »
WHERE table1.manager = table2.no_employe)

Quelques exercices pratiques sur les sous-requêtes

Voici donc quelques questions qui vont vous permettre de tester vos connaissances sur les sous-requêtes SQL. (les solutions suivent…mais il vaut mieux ne pas les regarder tout de suite)

  1. Afficher les noms et Job des employés qui font le même travail que Pianetti.
  2. Afficher les noms et numéro de service des employés dont le chef est ‘Deleglise’.
  3. Afficher les noms des employés qui travaillent a Toulouse.
  4. Afficher les employés dont le salaire dépasse celui de tous les chefs.
  5. Afficher les employés dont le salaire  dépasse celui DE LEUR chef.
  6. Afficher les employés dont le salaire est le plus élevé de leur service.

Et voici les solutions:

  1. Afficher les noms et Job des employés qui font le même travail que Pianetti:

mysql> SELECT nom, job FROM employes

-> WHERE JOB = (SELECT job FROM employes

->          WHERE nom=’Pianetti’);

+-----------+---------+
| nom       | job |
+-----------+---------+
| MURDOCK   | MANAGER |
 PIANETTI  | MANAGER |
| DELEGLISE | MANAGER |
+-----------+---------+
3 rows in set (0.03 sec)

Cette requête fonctionne car il n’y a qu’un ‘Pianetti’ dans la table. S’il y en avait plusieurs la sous requête ramenerait plusieurs valeurs et l’opérateur (à 2 opérande seulement) d’égalité serait incorrect. Il serait donc plus judicieux ici d’utiliser un ‘IN’ qui marcherait dans tous les cas…

Avec l’opérateur IN cela nous donne:

mysql> SELECT nom, job FROM employes
-> WHERE JOB IN (SELECT job FROM employes
->          WHERE nom='Pianetti');
+-----------+---------+
| nom       | job |
+-----------+---------+
| MURDOCK   | MANAGER |
| PIANETTI  | MANAGER |
| DELEGLISE | MANAGER |
+-----------+---------+
3 rows in set (0.00 sec)
  1. Afficher les noms et numéro de service des employés dont le chef est ‘Deleglise’:
mysql> SELECT nom, no_service FROM employes
-> WHERE mgr= (SELECT no_employe FROM employes
->          WHERE nom='Deleglise');
+------+------------+
| nom  | no_service |
+------+------------+
| LEE  |   10 |
+------+------------+
1 row in set (0.00 sec)

Même remarque que précédemment. On suppose ici qu’il n’y a qu’un seul Lee dans la société…

  1. Afficher les noms des employés qui travaillent a Dallas:

Truc: la requête portera sur employes et la sous-requete sur services

mysql> SELECT nom FROM employes
-> WHERE no_service IN (
->    SELECT no_service FROM services
->  WHERE lieu = 'Dallas' );
+-------------+
| nom         |
+-------------+
| BAILLARGEON |
| MURDOCK     |
| GORLIN      |
| PARKER      |
| GRIM        |
+-------------+
5 rows in set (0.00 sec)
  1. Afficher les employés dont le salaire dépasse celui de tous les chefs:
mysql> SELECT nom, salaire
-> FROM employes
-> WHERE salaire  >
-> ( SELECT MAX(salaire) FROM employes
> WHERE job = 'MANAGER' );
+--------+---------+
| nom    | salaire |
+--------+---------+
| GORLIN | 3000.00 |
| CHIRAC | 5000.00 |
| GRIM   | 3000.00 |
+--------+---------+
3 rows in set (0.00 sec)

 

  1. Afficher les employés dont le salaire  dépasse celui DE LEUR chef.

On fait appel ici à une sous requête synchronisée:

mysql> SELECT nom, salaire

-> FROM employes E

-> WHERE salaire >

-> ( SELECT salaire FROM employes WHERE no_employe = E.mgr );

+——–+———+

| nom    | salaire |

+——–+———+

| GORLIN | 3000.00 |

| GRIM   | 3000.00 |

+——–+———+

2 rows in set (0.00 sec)

 

  1. Afficher les employés dont le salaire est le plus élevé de leur service:

 

mysql> SELECT nom, no_service, salaire

-> FROM employes E where salaire =

-> (SELECT MAX(salaire) FROM employes

-> WHERE e.no_service = no_service

-> GROUP BY no_service);

+———-+————+———+

| nom      | no_service | salaire |

+———-+————+———+

| PIANETTI |         30 | 2850.00 |

| GORLIN   | 20 | 3000.00 |

| CHIRAC   | 10 | 5000.00 |

| GRIM     | 20 | 3000.00 |

+———-+————+———+

4 rows in set (0.00 sec)

 

 

  • On voit ici que plusieurs personnes peuvent avoir le même salaire et gagner le maximum du département