Skip to content

1 4 5 2 requetes

SQL, récapitulatif 2/4 : Requêtes et sous-requêtes

Supports complémentaires

Pas de support vidéo pour cette session qui ne fait que récapituler les différentes syntaxes équivalentes pour exprimer une même requête. Ne vous laissez pas troubler par la multiplicité des options offertes pqr SQL. En choisissant un dialecte et un seul (vous avez compris que je vous recommande la partie déclarative, logique de SQL) vous pourrez tout exprimer sans avoir à vous poser des questions sans fin. Vos requêtes n'en seront que plus cohérentes et lisibles.

Dans tout ce qui précède, les requêtes étaient "à plat", avec un seul bloc select-from-where. SQL est assez riche (ou assez inutilement compliqué, selon les goûts) pour permettre des expressions complexes combinant plusieurs blocs. On a dans ce cas une requête principale, et des sous-requêtes, ou requêtes imbriquées.

Disons-le tout de suite : à l'exception des requêtes avec négation not exists, toutes les requêtes imbriquées peuvent s'écrire de manière équivalente à plat, et on peut juger que c'est préférable pour des raisons de lisibilité et de cohérence d'écriture. Cette session essaie en tout cas de clarifier les choses.

Requêtes imbriquées

Reprenons l'exemple de la requête trouvant la surface et le niveau de l'appartement de M. Simplet. On peut l'exprimer avec une requête imbriquée de deux manières. La première est la forme déclarative classique.

select surface, niveau
from Appart as a, Personne as p
where p.prénom='Barnabé' and p.nom='Simplet'
and a.id = p.idAppart

On remarque qu'aucun attribut de la table Personne n'est utilisé pour construire le résultat. On peut donc utiliser une sous-requête (ou requête imbriquée).

select surface, niveau 
from Appart
where  id  in (select idAppart  
               from Personne
               where prénom='Barnabé' and nom='Simplet')

Le mot-clé in exprime la condition d'appartenance de l'identifiant de l'appartement à l'ensemble d'identifiants constitué avec la requête imbriquée. Il doit y avoir correspondance entre le nombre et le type des attributs auxquels s'applique la comparaison par in. L'exemple suivant montre une comparaison entre des paires d'attributs (ici on cherche des informations sur les propriétaires).

select prénom, nom, surface, niveau
from   Appart as a, Personne as p 
where  a.id = p.idAppart
and    (p.id, p.idAppart)
          in (select idPersonne, idAppart from Propriétaire)
prénom nom surface niveau
null Prof 250 2
Alice Grincheux 50 5
Alphonsine Joyeux 250 1

Il est bien entendu assez direct de réécrire la requête ci-dessus comme une jointure classique (exercice). Parfois l'expression avec requête imbriquée peut s'avérer plus naturelle. Supposons que l'on cherche les immeubles dans lesquels on trouve un appartement de 50 m2. Voici l'expression avec requête imbriquée.

select * 
from Immeuble 
where id in (select idImmeuble from Appart where surface=50)
id nom adresse
1 Koudalou 3 rue des Martyrs

La requête directement réécrite en jointure donne le résultat suivant :

select i.* 
from   Immeuble as i,Appart as a 
where  i.id=a.idImmeuble
and    surface=50
id nom adresse
1 Koudalou 3 rue des Martyrs
1 Koudalou 3 rue des Martyrs

On obtient deux fois le même immeuble puisqu'il peut être associé à deux appartements différents de 50 m2. Il suffit d'ajouter un distinct après le select pour régler le problème, mais on peut considérer que dans ce cas la requête imbriquée est plus appropriée. Attention cependant, il n'est pas possible de placer dans le résultat des attributs appartenant aux tables des requêtes imbriquées.

Le principe général des requêtes imbriquées est d'exprimer des conditions sur des tables calculées par des requêtes. Cela revient, dans le cadre formel qui soutient SQL, à appliquer une quantification sur une collection constituée par une requête (voir chapitre chap-calcul).

Ces conditions sont les suivantes :

  • exists R : renvoie true si R n'est pas vide false sinon.
  • t in Rt est un nuplet dont le type (le nombre et le type des attributs) est celui de R : renvoie true si t appartient à R false sinon.
  • v cmp any Rcmp est un comparateur SQL (\< > = etc.) : renvoie true si la comparaison avec au moins un des nuplets de la table R renvoie true.
  • v cmp all Rcmp est un comparateur SQL (\< > = etc.) : renvoie true si la comparaison avec tous les nuplets de la table R renvoie true.

De plus toutes ces expressions peuvent être préfixées par not pour obtenir la négation. La richesse des expressions possibles permet d'effectuer une même interrogation en choisissant parmi plusieurs syntaxes possibles. En général, tout ce qui n'est pas basé sur une négation not in ou not exists peut s'exprimer sans requête imbriquée.

Le all peut se réécrire avec une négation puisque si une propriété est toujours vraie il n'existe pas de cas où elle est fausse. La requête ci-dessous applique le all pour chercher le niveau le plus élevé de l'immeuble 1.

select * from Appart
    where idImmeuble=1
    and    niveau >= all (select niveau from Appart where idImmeuble=1)

Le all exprime une comparaison qui vaut pour toutes les nuplets ramenés par la requête imbriquée. La formulation avec any s'écrit :

select * from Appart
    where idImmeuble=1
    and   not (niveau < any (select niveau from Appart where idImmeuble=1))

Attention aux valeurs à null dans ce genre de situation : toute comparaison avec une de ces valeurs renvoie unknown et cela peut entraîner l'échec du all. Il n'existe pas d'expression avec jointure qui puisse exprimer ce genre de condition.

Requêtes corrélées

Les exemples de requêtes imbriquées donnés précédemment pouvaient être évalués indépendamment de la requête principale, ce qui permet au système (s'il le juge nécessaire) d'exécuter la requête en deux phases. La clause exists fournit encore un nouveau moyen d'exprimer les requêtes vues précédemment en basant la sous-requête sur une ou plusieurs valeurs issues de la requête principale. On parle alors de requêtes corrélées.

Voici encore une fois la recherche de l'appartement de M. Barnabé Simplet exprimée avec exists :

select * from Appart
where  exists  (select * from Personne
                where  prénom='Barnabé' and nom='Simplet'
                and    Personne.idAppart=Appart.id)

On obtient donc une nouvelle technique d'expression qui permet d'aborder le critère de recherche sous une troisième perspective : on conserve un appartement si, pour cet appartement, l'occupant s'appelle Barnabé Simplet. Il s'agit assez visiblement d'une jointure mais entre deux tables situées dans des requêtes (ou plutôt des "blocs") distinctes. La condition de jointure est appelée corrélation d'où le nom de ce type de technique.

Les jointures dans lesquelles le résultat est construit à partir d'une seule table peuvent d'exprimer avec exists ou in. Voici quelques exemples reprenant des requêtes déjà vues précédemment.

  • Qui habite un appartement de plus de 200 m2?

    Avec in :

    select prénom, nom, profession      
    from Personne      
    where idAppart in (select id from Appart where surface >= 200)
    

    Avec exists :

    select prénom, nom, profession      
    from Personne  p    
    where exists (select * from Appart a 
                  where a.id=p.idAppart 
                  and surface >= 200)
    
  • Qui habite le Barabas?

    Avec in :

    select prénom, nom, no, surface, niveau
    from   Personne as p, Appart as a
    where  p.idAppart=a.id
    and a.idImmeuble in 
             (select id from Immeuble
              where  nom='Barabas')
    

    Avec exists :

    select prénom, nom, no, surface, niveau 
    from Personne as p, Appart as a 
    where  p.idAppart=a.id 
    and exists (select * from Immeuble i
                where  i.id=a.idImmeuble 
                and i.nom='Barabas')
    

Important

Dans une sous-requête associée à la clause exists peu importent les attributs du select puisque la condition se résume à : cette requête ramène-t-elle au moins un nuplet ou non ? On peut donc systématiquement utiliser select * ou select ''.

Enfin rien n'empêche d'utiliser plusieurs niveaux d'imbrication au prix d'une forte dégradation de la lisibilité. Voici la requête "De quel(s) appartement(s) Alice Grincheux est-elle propriétaire et dans quel immeuble ?" écrite avec plusieurs niveaux.

select i.nom, no, niveau, surface
from  Immeuble as i, Appart as a
where  a.idImmeuble= i.id
and    a.id in 
             (select idAppart 
              from Propriétaire
              where idPersonne in
                       (select id 
                        from Personne
                        where  nom='Grincheux' 
                        and prénom='Alice'))

En résumé une jointure entre les tables R et S de la forme :

select R.*
from R S
where R.a = S.b

peut s'écrire de manière équivalente avec une requête imbriquée :

select [distinct] *
from R
where R.a in (select S.b from S)

ou bien encore sous forme de requête corrélée :

select [distinct] *
from R
where exists (select S.b from S where S.b = R.a)

Le choix de la forme est matière de goût ou de lisibilité, ces deux critères relevant de considérations essentiellement subjectives.

Requêtes avec négation

Les sous-requêtes sont en revanche irremplaçables pour exprimer des négations. On utilise alors not in ou (de manière équivalente) not exists. Voici un premier exemple avec la requête : donner les appartements sans occupant.

select * from Appart
where  id not in (select idAppart from Personne)

On obtient comme résultat.

id no surface niveau idImmeuble
101 34 50 15 1

La négation est aussi un moyen d'exprimer des requêtes courantes comme celle recherchant l'appartement le plus élevé de son immeuble. En SQL, on utilisera typiquement une sous-requête pour prendre le niveau maximal d'un immeuble, et on utilisera cet niveau pour sélectionner un ou plusieurs appartements, le tout avec une requête corrélée pour ne comparer que des appartements situés dans le même immeuble.

select *
from Appart as a1
where niveau =  (select max(niveau) from Appart as a2
                where a1.idImmeuble=a2.idImmeuble)
id surface niveau idImmeuble no
101 50 15 1 34
202 250 2 2 2

Il existe en fait beaucoup de manières d'exprimer la même chose. Tout d'abord cette requête peut en fait s'exprimer sans la fonction max() avec la négation : si a est l'appartement le plus élevé, c'est qu'il n'existe pas de niveau plus elevé que a. On utilise alors habituellement une requête dite "corrélée" dans laquelle la sous-requête est basée sur une ou plusieurs valeurs issues des tables de la requête principale.

select *
from Appart as a1
where not exists  (select * from Appart as a2
                   where a2.niveau > a1.niveau
                   and a1.idImmeuble = a2.idImmeuble)

Autre manière d'exprimer la même chose : si le niveau est le plus élevé, tous les autres sont situés à un niveau inférieur. On peut utiliser le mot-clé all qui indique que la comparaison est vraie avec tous les éléments de l'ensemble constitué par la sous-requête.

select *
from Appart as a1
where niveau >= all (select niveau from Appart as a2
                where a1.idImmeuble=a2.idImmeuble)

Dernier exemple de négation : quels sont les personnes qui ne possèdent aucun appartement même partiellement ? Les deux formulations ci-dessous sont équivalentes, l'une s'appuyant sur not in, et l'autre sur not exists.

select *
from Personne
where id not in (select idPersonne from Propriétaire)

select *
from Personne as p1
where not exists (select * from Propriétaire as p2
                  where p1.id=p2.idPersonne)