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
: renvoietrue
si R n'est pas videfalse
sinon.- t
in R
où t est un nuplet dont le type (le nombre et le type des attributs) est celui de R : renvoietrue
si t appartient à Rfalse
sinon.- v cmp
any R
où cmp est un comparateur SQL (\< > = etc.) : renvoietrue
si la comparaison avec au moins un des nuplets de la table R renvoietrue
.- v cmp
all R
où cmp est un comparateur SQL (\< > = etc.) : renvoietrue
si la comparaison avec tous les nuplets de la table R renvoietrue
.
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 duselect
puisque la condition se résume à : cette requête ramène-t-elle au moins un nuplet ou non ? On peut donc systématiquement utiliserselect *
ouselect ''
.
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)