Sous-requêtes

Les sous-requêtes

Il est possible de combiner plusieurs requêtes dans les clauses from et where, avec ce qu'on appelle une sous-requête ou sub-query.

Par exemple, imaginez qu'on veut connaître tous les intervenants qui interviennent à Paris. On peut formuler une requête ainsi :

select ea.animatorId
from
  action as a,
  action_event as e,
  event_animator as ea
where 
  a.actionId = e.actionId and
  e.eventId = ea.eventId and
  a.address_french_departmentId = 75
;

Ou bien la même requête à l'aide des sub-queries :

select ea.animatorId
from
  event_animator ea
where 
  ea.eventId in (
    select e.eventId 
    from
      action_event as e
    where 
      e.actionId in (
    	select a.actionId
    	from action as a
    	where a.address_french_departmentId = 75
      )
    )
;

Ici, on divise la requête en 3 parties. D'abord on récupère la liste d'interventions dans le 75 :

select a.actionId
from action as a
where a.address_french_departmentId = 75

Ceci produira une table bien plus limitée que la table d'interventions initiale. On peut utiliser cette table temporaire pour trouver uniquement les événements liés à ces intervenants déjà filtrés, avec l'opérateur in :

select e.eventId 
from
  action_event as e
where 
  e.actionId in (
    select a.actionId
    from action as a
    where a.address_french_departmentId = 75
  )

Et, pour aller plus loin, on a maintenant une liste de eventId qui est beaucoup plus limité que la table d'événements initiale. On répète le processus pour trouver les intervenants.

Il y a plusieurs avantages :

• La requête est souvent plus facile à lire et comprendre

• On limite des tables impliquées dans les produits cartésiens : des requêtes plus optimisées ! Et, ceci est connu et suggéré par les développeurs de MySQL

Parenthèse : optimisation et le produit cartésien

Pendant les phases de développement d'une application, on ne se rend pas compte des usages qui vont émerger.

Par exemple, on crée une base de données en mettant en avant son interactivité (mises à jour et résultats en temps réel). On utilise tous les règles, outils et méthodologies de database design comme la normalisation (voir les chapitres suivants) pour ce faire.

Seulement, après quelques mois ou années d'opération, vous avez maintenant une base de données bien remplie (quelques milliards de lignes par table).

Présentement, vous devez extraire des rapports, nécessitant plusieurs jointures de table. Et, les requêtes sont beaucoup trop longues. Pourquoi ? Et, que faire ?

À la racine du problème de performance est notre idée de produit cartésien. S'il faut joindre 2 tables immenses sur des critères pas assez uniques, la seule stratégie à la disposition de votre SGBDR est de démultiplier des lignes des 2 tables et extraire des lignes correspondantes. Si vous avez deux tables, chacune avec 100k lignes, il y aura finalement 10 milliards de lignes à traiter !

Qu'est-ce que je veux dire par critère pas assez unique ? Souvent, on stocke des caractéristiques d'une entité dans une colonne de notre base. Si, même en filtrant les lignes par un attribut pas vraiment distinguant, on peut se trouver avec quelques milliers de lignes de chaque côté de la jointure.

On utilise le terme cardinalité pour parler du degré d'unicité d'un attribut ou colonne. Une cardinalité élevée veut dire qu'il y a beaucoup de valeurs uniques. Une cardinalité basse veut dire qu'il y a beaucoup de répétition.

Attention ! On va revoir le terme cardinalité dans le contexte des liens entre des tables dans le chapitre de la modélisation de données.

Utiliser les sous-requêtes donc est souvent une stratégie d'optimisation ! On essaye de limiter le nombre de lignes de chaque côté d'une jointure pour limiter autant possible le produit cartésien obligatoire.

Dans les SGBDR modernes, notre requête SQL est transformée en plan d'action (ou query plan), et fréquemment, il est possible que ce plan d'action arrive à sous-diviser notre requête pour la meilleure performance (regardez cette page pour plus d'information).

Fréquemment, on donne au SGDBR d'autres aides à l'optimisation de son plan d'action : la création des indexes (voir les chapitres suivants), ou bien la reformulation nous-même d'une requête.

Inclusion : in

On utilise l'opérateur in pour créer une requête qui utilise les résultats d'une sous-requête dans une clause where.

Par exemple, je veux extraire le nombre d'interventions avec, au moins, un événement annulé en 2020 :

select count(a.actionId) 
from action a
where 
  a.actionId in (
  	select distinct actionId from action_event
  	where
  	  year(startUtc) = 2020 and
  	  state = 'canceled'
  );

Notez bien l'usage de distinct dans la sous-requête : cela optimise d'abord le set d'interventions à utiliser dans le croisement, parce qu'il peut y avoir plusieurs événements par intervention.

Exclusion : not in

Au contraire, pour savoir les interventions non annulées en 2022, je peux inverser ma requête :

select count(a.actionId) 
from action a
where 
  a.actionId not in (
  	select distinct actionId from action_event
  	where
  	  year(startUtc) = 2020 and
  	  state = 'canceled'
  );

in et not in pour les valeurs simples

Je peux trouver toutes les interventions avec un état (state) de plusieurs valeurs avec l'opérateur in :

 select count(a.actionId)
 from action a
 where
   a.state in ('confirmed', 'done', 'finished');

Inclusion : tout ou une partie avec some et all

L'opérateur in permet de tester si une valeur se trouve dans un set (récupéré via une sous-requête). C'est une comparaison d'égalité. En revanche, parfois, je veux faire une comparaison de grandeur :

• est qu'une valeur est supérieure à au moins une autre ?

• est qu'une valeur est supérieure à toutes les autres ?

Pour cela, les opérateurs some et all existent. Vous vous souvenez le problème suivant ?

Je veux savoir s'il existe des interventions qui sont facturées plus chères que celles en Île de France.

La solution qu'on a trouvée utilisait l'opérateur as :

select distinct S.actionId, S.name, S.budget, S.address_french_regionId
from action as S, action as A
where 
  S.address_french_regionId != 'IDF' and S.address_french_regionId is not null 
  and 
  A.address_french_regionId = 'IDF' 
  and 
  S.budget > A.budget;

En revanche, la requête était très longue (> 2 minutes )! On peut optimiser avec une sous-requête et l'opérateur some :

select actionId, name, budget, address_french_regionId 
 from action
 where
   address_french_regionId != 'IDF' and 
   address_french_regionId is not null and
   budget > some (
     select budget
     from action
     where 
     address_french_regionId = 'IDF'
   );

La requête se fait en quelques millisecondes !!

Attention, par contre, dans le résultat on sort toutes les interventions avec un budget supérieur à au moins une intervention hors Île-de-France.

Si je veux extraire les interventions avec budget supérieur à toutes les interventions en Île-de-France, j'utilise l'opérateur > all :

 select actionId, name, budget, address_french_regionId 
 from action
 where
   address_french_regionId != 'IDF' and 
   address_french_regionId is not null and
   budget > all (
     select budget
     from action
     where 
     address_french_regionId = 'IDF'
   );

Les options de some et all sont :

au moins un	toute
> some	> all
>= some	>= all
< some	< all
<= some	<= all
<> some	<> all

Tester l'existence : exists et not exists

Parfois, je veux exprimer une condition basée sur l'existence d'un résultat (le nombre ou valeur ne compte pas).

Par exemple, je veux extraire la liste d'intervenants qui n'ont jamais participé à un événement (pour une purge éventuelle de la base). Dans ce cas, il faut juste tester l'existence d'une ligne dans event_animator pour chaque intervenant de la plateforme :

 select u.givenName, u.familyName, a.animatorId
 from 
   animator a,
   user u
 where
   u.userId = a.userId and
   not exists (
     select ea.animatorId 
     from event_animator ea
     where ea.animatorId = a.animatorId     
   )
 order by u.familyName, u.givenName;

Notez que dans cet exemple, on utilise une information provenant de la requête "mère" (a.animatorId) dans la sous-requête. Dans ce cas, l'utilisation des alias est important.

Sous requêtes dans la clause from

Je peux aussi considérer les résultats d'une requête comme une table éphémère qui peut aussi être interrogé.

Par exemple, je veux extraire la liste de départements où le budget est supérieur à 1000€. Je peux diviser cette requête en 2 parties :

• extraire le moyen de budget par département (avec avg et group by)

• ensuite, filtrer les lignes de la requête avant

 select T.code, T.avg_budget
 from (
   select 
     address_french_departmentId as code, 
     avg(budget) as avg_budget
   from action
   group by address_french_departmentId
 ) as T
 where T.avg_budget > 1000;

Il y a toujours plusieurs façons de faire la même chose. L'équivalent de la requête dessus est :

select address_french_departmentId, avg(budget) as avg_budget
from action
group by address_french_departmentId
having avg_budget > 1000;

Votre choix de requête dépendra plusieurs facteurs :

• La performance de la requête : en fonction de votre schéma, parfois l'utilisation d'une sous-requête pourrait être plus rapide.

• La lisibilité de la requête : si la différence en performance est négligeable, le fait de pouvoir lire et facilement comprendre la requête compte pour beaucoup !

On peut également utiliser la clause with afin de définir de façon plus stricte et explicite la structure de la table éphémère. La requête suivant aura les mêmes résultats :

with action_temp(code, avg_budget) as 
(
  select 
    address_french_departmentId as code, 
    avg(budget) as avg_budget
  from action
  group by address_french_departmentId
)
select code, avg_budget
from action_temp
where avg_budget > 1000;

Ici, on déclare et remplit d'abord une table éphémère qui s'appelle action_temp, avec 2 attributs code et avg_budget. Ensuite, on exécute une requête select sur cette table éphémère.

Sous requêtes dans la clause select

Je peux aussi utiliser une sous-requête dans la clause `select.

Imaginons que je veux savoir le nombre d'interventions par région.

 select fr as 'Région', (
   select count(*) 
   from action 
   where 
     action.address_french_regionId = french_region.regionId
   ) as 'Interventions'
from french_region;

Il y a toujours plusieurs façons de faire la même chose. L'équivalent de la requête dessus est :

select 
  fr.fr as 'Région', 
  count(a.actionId) as 'Interventions'
from 
  french_region as fr,
  action as a
where 
  fr.regionId = a.address_french_regionId
group by 
  fr.fr

Exercices

Les exercices suivants sont basés sur la base de données SAKILA, une base de données de test fourni par MySQL.

Récupérez la base SAKILA ici.

Question 1

Dans l’exercice 3, nous avons affiché une liste d’acteurs qui partagent le même nom de famille. Comment récupérer juste le nombre de lignes retournées par cette requête ?

Solution

Résultat : 55

with shared_names as (
  select 
    last_name, 
    count(*) as shared_surname
  from actor
  group by last_name
  having shared_surname > 1
)
select count(*) as "Shared Surname"
from shared_names;

Question 2

Utilisez des sous-requêtes afin de trouver tous les acteurs qui sont dans le film « Alone Trip »

Solution

Résultat : 8 lignes

select 
  first_name, 
  last_name 
from actor
where actor_id in (
  select actor_id 
  from film_actor 
  where film_id in (
    select film_id 
    from film 
    where lower(title) = lower('Alone Trip')
  )
);

Question 3

Utilisez l’opérateur in afin de trouver tous les films avec un classement « G », « PG », ou « PG-13 »

Solution

Résultat : 595 lignes

select title, rating
from film
where rating in ('G', 'PG', 'PG-13');

Question 4

Calculez la durée moyenne des films avec le classement « G », « PG », ou « PG-13 ».

Solution

Résultat : 114,8

select avg(T.length) 
from (
  select title, rating, length
  from film
  where rating in ('G', 'PG', 'PG-13')
) as T;