1 4 5 1 select
SQL, récapitulatif ¼ : le bloc "select-from-where"¶
Ce chapitre présente les compléments du langage d'interrogation SQL (la partie dite Langage de Manipulation de Données ou LMD) dans le cadre d'un récapitulatif. Ces compléments présentent peu de difficulté dans la mesure où la véritable complexité réside d'une part dans l'interprétation des requêtes complexes qui font parfois appel à des logiques sophistiquées et d'autre part dans la multiplicité des variantes syntaxiques qui peut parfois troubler.
Les deux chapitres précédents devraient avoir réglé ces problèmes d'interprétation. Vous savez maintenant que SQL propose deux paradigmes d'interrogation, l'un déclaratif et l'autre procédural. Les requêtes se comprennent soit via leur équivalent en formules logiques, soit en les considérant comme des opérations ensemblistes.
Dans ce chapitre nous utilisons systématiquement l'approche déclarative. Vous pouvez les reformuler sous leur forme ensembliste si vous le souhaitez.
La base prise comme exemple dans ce chapitre est celle des immeubles.
Le bloc select-from-where
¶
Supports complémentaires
Dans cette session, nous étudions les compléments à la forme de base d'une requête SQL, que nous appelons bloc, résumée ainsi :
select liste_expressions
from relations_sources
[where liste_conditions]
[order by critère_de_tri]
Parmi les quatre clauses select
, form
, where
et order by
, les
deux dernières sont optionnelles. La recherche la plus simple consiste à
récupérer le contenu complet d'une table. On n'utilise pas la clause
where
et le * désigne tous les attributs.
select * from Immeuble
id | nom | adresse |
---|---|---|
1 | Koudalou | 3 rue des Martyrs |
2 | Barabas | 2 allée du Grand Turc |
L'ordre des trois clauses select
from
et where
est trompeur pour
la signification d'une requête. Comme nous l'avons déjà détaillé dans
les chapitres qui précédent, l'inteprétation s'effectue toujours de
la manière suivante :
- la clause
from
définit l'espace de recherche en fonction d'un ensemble de sources de données ;- la clause
where
exprime un ensemble de conditions sur la source : seuls les nuplets pour lesquels ces conditions sont satisfaites sont conservés ;- enfin la clause
select
construit un nuplet-résultat grâce à une liste d'expressions appliquées aux nuplets de la source ayant passé le filtre duwhere
.
La clause from
¶
L'espace de recherche est défini dans la clause from
par une ou
plusieurs tables. Par "table" il ne faut pas ici comprendre forcément
"une des tables de la base" courante même si c'est le cas le plus
souvent rencontré. SQL est beaucoup général que cela : une table dans un
from
peut également être résultat d'une autre requête. On parlera
de table basée et de table calculée pour distinguer ces deux cas. Ce
peut également être une table stockée dans une autre base ou une table
calculée à partir de tables basées dans plusieurs bases ou une
combinaison de tout cela.
Voici une première requête qui ramène les immeubles dont l'id vaut 1.
select nom, adresse
from Immeuble
where id=1
Il n'aura pas échappé au lecteur attentif que le résultat est lui-même une table (calculée et non basée). Pourquoi ne pourrait-on pas interroger cette table calculée comme une autre? C'est possible en SQL comme le montre l'exemple suivant :
select *
from (select nom, adresse from Immeuble where id=1) as Koudalou
On a donc placé une requête SQL dans le from
où elle définit un espace
de recherche constitué de son propre résultat. Le mot-clé as
permet de
donner un nom temporaire au résultat. En d'autres termes Koudalou
est
le nom de la table calculée sur laquelle s'effectue la requête. Cette
table temporaire n'existe que pendant l'exécution.
Note
Comme nous l'avons vu, cette approche est de nature algébrique : on manipule dans lefrom
des ensembles, stockés (les tables) ou calculés (obtenus par des requêtes). C'est une syntaxe en plus pour dire la même chose, donc on peut très bien se passer de la seconde formulation. Il est plus intéressant de prolonger cette idée d'interroger une relation calculée en donnant définivement un nom à la requête qui sélectionne l'immeuble. En SQL cela s'appelle une vue. On crée une vue dans un schéma avec la commandecreate view
.
create view Koudalou as
select nom, adresse from Immeuble where id=1
Une fois créée une vue peut être utilisée comme espace de recherche exactement comme une table basée. Le fait que son contenu soit calculé reste transparent pour l'utilisateur.
select nom, adresse from Koudalou
Les vues sont traitées en détail dans le chapitre consacré aux schémas relationnels.
L'interprétation du from
est indépendante de l'origine des tables :
tables basées, tables calculées, et vues. Comme nous l'avons vu dans
les chapitres précédents, il existe deux manières de spécifier l'espace
de recherche avec le from
. La première est la forme déclarative dans
laquelle on sépare le nom des tables par des virgules.
select * from Immeuble as i, Appart as a
Dans ce cas, le nom d'une table sert à définir une variable nuplet
(voir chapitre SQL, langage déclaratif) à laquelle
on peut affecter tous les nuplets de la table. Les variables peuvent
être explicitement nommées avec la mot-clé as
(elles s'appellent i
et a
dans la requête ci-dessus). On peut aussi omettre le as
, dans
ce cas le nom de la variable est (implicitement) le nom de la table.
select * from Immeuble, Appart
Un cas où le as
est obligatoire est l'auto-jointure : on veut désigner
deux nuplets de la même table. Exemple : on veut les paires
d'appartements du même immeuble.
select a1.no, a2.no
from Appart as a1, Appart as a2
where a1.idImmeuble = a2.idImmeuble
En l'absence du as
et de l'utilisation du nom de la variable comme
préfixe, il y aurait ambiguité sur le nom des attributs.
La deuxième forme du from
définit l'espace de recherche par une
opération algébrique, jointure ou produit cartésien.
select * from Immeuble cross join Appart
Cette formulation revient à définir une table virtuelle (appelons-là Tfrom) qui tient lieu d'espace de recherche par la suite. L'affichage ci-dessus nous montre quel est l'espace de recherche Tfrom de la requête précédente.
id | nom | adresse | id | surface | niveau | idImmeuble | no |
---|---|---|---|---|---|---|---|
1 | Koudalou | 3 rue des Martyrs | 100 | 150 | 14 | 1 | 1 |
2 | Barabas | 2 allée du Grand Turc | 100 | 150 | 14 | 1 | 1 |
1 | Koudalou | 3 rue des Martyrs | 101 | 50 | 15 | 1 | 34 |
2 | Barabas | 2 allée du Grand Turc | 101 | 50 | 15 | 1 | 34 |
1 | Koudalou | 3 rue des Martyrs | 102 | 200 | 2 | 1 | 51 |
2 | Barabas | 2 allée du Grand Turc | 102 | 200 | 2 | 1 | 51 |
1 | Koudalou | 3 rue des Martyrs | 103 | 50 | 5 | 1 | 52 |
2 | Barabas | 2 allée du Grand Turc | 104 | 75 | 3 | 1 | 43 |
1 | Koudalou | 3 rue des Martyrs | 104 | 75 | 3 | 1 | 43 |
2 | Barabas | 2 allée du Grand Turc | 103 | 50 | 5 | 1 | 52 |
1 | Koudalou | 3 rue des Martyrs | 200 | 150 | 0 | 2 | 1 |
2 | Barabas | 2 allée du Grand Turc | 200 | 150 | 0 | 2 | 1 |
1 | Koudalou | 3 rue des Martyrs | 201 | 250 | 1 | 2 | 1 |
2 | Barabas | 2 allée du Grand Turc | 201 | 250 | 1 | 2 | 1 |
1 | Koudalou | 3 rue des Martyrs | 202 | 250 | 2 | 2 | 2 |
2 | Barabas | 2 allée du Grand Turc | 202 | 250 | 2 | 2 | 2 |
La clause de jointure join
définit un espace de recherche constitué
des paires de nuplets pour lesquels la condition de jointure est vraie.
select *
from Immeuble join Appart on (Immeuble.id=Appart.idImmeuble)
On obtient le résultat suivant.
id | nom | adresse | id | surface | niveau | idImmeuble | no |
---|---|---|---|---|---|---|---|
1 | Koudalou | 3 rue des Martyrs | 100 | 150 | 14 | 1 | 1 |
1 | Koudalou | 3 rue des Martyrs | 101 | 50 | 15 | 1 | 34 |
1 | Koudalou | 3 rue des Martyrs | 102 | 200 | 2 | 1 | 51 |
1 | Koudalou | 3 rue des Martyrs | 103 | 50 | 5 | 1 | 52 |
1 | Koudalou | 3 rue des Martyrs | 104 | 75 | 3 | 1 | 43 |
2 | Barabas | 2 allée du Grand Turc | 200 | 150 | 0 | 2 | 1 |
2 | Barabas | 2 allée du Grand Turc | 201 | 250 | 1 | 2 | 1 |
2 | Barabas | 2 allée du Grand Turc | 202 | 250 | 2 | 2 | 2 |
L'obligation d'encadrer les expressions algébriques quand on on en combine plusieurs (par exemple jointure entre trois tables ou plus) les rend difficilement lisibles. C'est une des raisons qui poussent à s'en tenir à la version déclarative de SQL.
Dernière précision au sujet du from
: l'ordre dans lequel on énumère
les tables n'a aucune importance.
La clause where
¶
La clause where
permet d'exprimer des conditions portant sur les
nuplets désignés par la clause from
. Ces conditions suivent en général
la syntaxe expr1 [not]
\(\Theta\) expr2
, où expr1
et expr2
sont
deux expressions construites à partir de noms d'attributs, de
constantes et de fonctions, et \(\Theta\) est l'un des opérateurs de
comparaison classique \< > \<= >= !=.
Les conditions se combinent avec les connecteurs booléens and
or
et
not
. SQL propose également un prédicat in
qui teste l'appartenance
d'une valeur à un ensemble. Il s'agit (du moins tant qu'on n'utilise
pas les requêtes imbriquées) d'une facilité d'écriture pour remplacer
le or
. La requête
select *
from Personne
where profession='Acteur'
or profession='Rentier'
s'écrit de manière équivalente avec un in
comme suit :
select *
from Personne
where profession in ('Acteur', 'Rentier')
id | prénom | nom | profession | idAppart |
---|---|---|---|---|
4 | Barnabé | Simplet | Acteur | 102 |
5 | Alphonsine | Joyeux | Rentier | 201 |
Pour les chaînes de caractères, SQL propose l'opérateur de comparaison
like
, avec deux caractères de substitution :
- le "%" remplace n'importe quelle sous-chaîne ;
- le "_" remplace n'importe quel caractère.
L'expression _ou%ou
est donc interprétée par le like
comme toute
chaîne commençant par un caractère suivi de "ou" suivi de n'importe
quelle chaîne suivie une nouvelle fois de "ou".
select *
from Immeuble
where nom like '_ou%ou'
id | nom | adresse |
---|---|---|
1 | Koudalou | 3 rue des Martyrs |
Il est également possible d'exprimer des conditions sur des tables
calculées par d'autre requêtes SQL incluses dans la clause where
et
habituellement désignées par le terme de "requêtes imbriquées". On
pourra par exemple demander la liste des personnes dont l'appartement
fait partie de la table calculée des appartements situés au-dessus du
troisième niveau.
select * from Personne
where idAppart in (select id from Appart where niveau > 3)
id | prénom | nom | profession | idAppart |
---|---|---|---|---|
2 | Alice | Grincheux | Cadre | 103 |
3 | Léonie | Atchoum | Stagiaire | 100 |
Avec les requêtes imbriquées on entre dans le monde incertain des requêtes qui semblent claires mais finissent par ne plus l'être du tout. La difficulté vient souvent du fait qu'il faut raisonner simultanément sur plusieurs requêtes qui, de plus, sont souvent interdépendantes (les données sélectionnées dans l'une servent de paramètre à l'autre). Il est très souvent possible d'éviter les requêtes imbriquées comme nous l'expliquons dans ce chapitre.
Valeurs manquantes : le null
¶
En théorie, dans une table relationnelle, tous les attributs ont une
valeur. En pratique, certaines valeurs peuvent être inconnues ou
manquantes : on dit qu'elles sont à null
. Le null
n'est pas une
valeur spéciale, c'est une absence de valeur.
Note. Les valeurs à
null
sont une source de problème, car elles rendent parfois le résultat des requêtes difficile à comprendre. Mieux vaut les éviter si c'est possible.
Il est impossible de déterminer quoi que ce soit à partir d'une valeur
à null
. Dans le cas des comparaisons, la présence d'un null
renvoie
un résultat qui n'est ni true
ni false
mais unknown
, une valeur
booléenne intermédiaire. Reprenons à nouveau la table Personne avec un
des prénoms à null
. La requête suivante devrait ramener tous les
nuplets.
select *
from Personne
where prénom like '%'
Mais la présence d'un null
empêche l'inclusion du nuplet
correspondant dans le résultat.
id | prénom | nom | profession | idAppart |
---|---|---|---|---|
2 | Alice | Grincheux | Cadre | 103 |
3 | Léonie | Atchoum | Stagiaire | 100 |
4 | Barnabé | Simplet | Acteur | 102 |
5 | Alphonsine | Joyeux | Rentier | 201 |
6 | Brandon | Timide | Rentier | 104 |
7 | Don-Jean | Dormeur | Musicien | 200 |
Cependant la condition like
n'a pas été évaluée à false
comme le
montre la requête suivante.
select *
from Personne
where prénom not like '%'
On obtient un résultat vide, ce qui montre bien que le like
appliqué à
un null
ne renvoie pas false
(car sinon on aurait
not false = true
). C'est d'ailleurs tout à fait normal puisqu'il
n'y a aucune raison de dire qu'une absence de valeur ressemble à
n'importe quelle chaîne.
Les tables de vérité de la logique trivaluée de SQL sont définies de la manière suivante. Tout d'abord on affecte une valeur aux trois constantes logiques :
true
vaut 1false
vaut 0unknown
vaut 0.5
Les connecteurs booléens s'interprètent alors ainsi :
val1 and val2
= max(val1 val2)val1 or val2
= min(val1 val2)not val1
= 1 - val1.
On peut vérifier notamment que not unknown
vaut toujours unknown
.
Ces définitions sont claires et cohérentes. Cela étant il vaut mieux
prévenir de mauvaises surprises avec les valeurs à null
, soit en les
interdisant à la création de la table avec les options not null
ou
default
, soit en utilisant le test is null
(ou son complément
is not null
). La requête ci-dessous ramène tous les nuplets de la
table, même en présence de null
.
select *
from Personne
where prénom like '%'
or prénom is null
id | prénom | nom | profession | idAppart |
---|---|---|---|---|
1 | Prof | Enseignant | 202 | |
2 | Alice | Grincheux | Cadre | 103 |
3 | Léonie | Atchoum | Stagiaire | 100 |
4 | Barnabé | Simplet | Acteur | 102 |
5 | Alphonsine | Joyeux | Rentier | 201 |
6 | Brandon | Timide | Rentier | 104 |
7 | Don-Jean | Dormeur | Musicien | 200 |
Attention le test valeur = null
n'a pas de sens. On ne peut pas être
égal à une absence de valeur.
La clause select
¶
Finalement, une fois obtenus les nuplets du from
qui satisfont le
where
on crée à partir de ces nuplets le résultat final avec les
expressions du select
.
Si on indique explicitement les attributs au lieu d'utiliser *, leur
nombre détermine le nombre de colonnes de la table calculée. Le nom de
chaque attribut dans cette table est par défaut l'expression du
select
mais on peut indiquer explicitement ce nom avec as
. Voici un
exemple qui illustre également une fonction assez utile, la
concaténation de chaînes.
select concat(prénom, ' ', nom) as 'nomComplet'
from Personne
nomComplet. . null. Alice Grincheux. Léonie Atchoum. Barnabé Simplet. Alphonsine Joyeux. Brandon Timide. Don-Jean Dormeur.
Note La fonction
concat()
ici utilisée est spécifique à MySQL.
Le résultat montre que l'une des valeurs est à null
. Logiquement
toute opération appliquée à un null
renvoie un null
en sortie
puisqu'on ne peut calculer aucun résultat à partir d'une valeur
inconnue. Ici c'est le prénom de l'une des personnes qui manque. La
concaténation du prénom avec le nom est une opération qui "propage"
cette valeur à null
. Dans ce cas, il faut utiliser une fonction
(spécifique à chaque système)à qui remplace la valeur à null
par une
valeur de remplacement. Voici la version MySQL (fonction
ifnull(attribut, remplacement)
).
select concat(ifnull(prénom,' '), ' ', nom) as 'nomComplet'
from Personne
Une "expression" dans la clause select
désigne ici, comme dans tout
langage, une construction syntaxique qui prend une ou plusieurs valeurs
en entrée et produit une valeur en sortie. Dans sa forme la plus simple,
une expression est simplement un nom d'attribut ou une constante comme
dans l'exemple suivant.
select surface, niveau, 18 as 'EurosParm2'
from Appart
surface | niveau | EurosParm2 |
---|---|---|
150 | 14 | 18 |
50 | 15 | 18 |
200 | 2 | 18 |
50 | 5 | 18 |
75 | 3 | 18 |
150 | 0 | 18 |
250 | 1 | 18 |
250 | 2 | 18 |
Les attributs surface
et niveau
proviennent de Appart alors que 18
est une constante qui sera répétée autant de fois qu'il y a de nuplets
dans le résultat. De plus, on peut donner un nom à cette colonne avec la
commande as
. Voici un second exemple qui montre une expression plus
complexe. L'utilisateur (certainement un agent immobilier avisé et
connaissant bien SQL) calcule le loyer d'un appartement en fonction
d'une savante formule qui fait intervenir la surface et le niveau.
select no, surface, niveau,
(surface * 18) * (1 + (0.03 * niveau)) as loyer
from Appart
no | surface | niveau | loyer |
---|---|---|---|
1 | 150 | 14 | 3834.00 |
34 | 50 | 15 | 1305.00 |
51 | 200 | 2 | 3816.00 |
52 | 50 | 5 | 1035.00 |
1 | 250 | 1 | 4635.00 |
2 | 250 | 2 | 4770.00 |
SQL fournit de très nombreux opérateurs et fonctions de toute sorte qui sont clairement énumérées dans la documentation de chaque système. Elles sont particulièrement utiles pour des types de données un peu délicat à manipuler comme les dates.
Une extension rarement utilisée consiste à effectuer des tests sur la
valeur des attributs à l'intérieur de la clause select
avec
l'expression case
dont la syntaxe est:
case
when test then expression
[when ...]
else expression
end
Ces tests peuvent être utilisés par exemple pour effectuer un décodage des valeurs quand celles-ci sont difficiles à interpréter ou quand on souhaite leur donner une signification dérivée. La requête ci-dessous classe les appartements en trois catégories selon la surface.
select no, niveau, surface,
case when surface <= 50 then 'Petit'
when surface > 50 and surface <= 100 then 'Moyen'
else 'Grand'
end as categorie
from Appart
no | niveau | surface | categorie |
---|---|---|---|
1 | 14 | 150 | Grand |
34 | 15 | 50 | Petit |
51 | 2 | 200 | Grand |
52 | 5 | 50 | Petit |
43 | 3 | 75 | Moyen |
10 | 0 | 150 | Grand |
1 | 1 | 250 | Grand |
2 | 2 | 250 | Grand |
Jointure interne, jointure externe¶
La jointure est une opération indispensable dès que l'on souhaite combiner des données réparties dans plusieurs tables. Nous avons déjà étudié en détail la conception et l'expression des jointures. On va se contenter ici de montrer quelques exemples en forme de récapitulatif, sur notre base d'immeubles.
Note
Il existe beaucoup de manières différentes d'exprimer les jointures en SQL. Il est recommandé de se limiter à la forme de base donnée ci-dessous qui est plus facile à interpréter et se généralise à un nombre de tables quelconques.
Jointure interne¶
Prenons l'exemple d'une requête cherchant la surface et le niveau de l'appartement de M. Barnabé Simplet.
select p.nom, p.prénom, a.surface, a.niveau
from Personne as p, Appart as a
where prénom='Barnabé'
and nom='Simplet'
and a.id = p.idAppart
nom | prénom | surface | niveau |
---|---|---|---|
Simplet | Barnabé | 200 | 2 |
Une première difficulté à résoudre quand on utilise plusieurs tables est
la possibilité d'avoir des attributs de même nom dans l'union des
schémas, ce qui soulève des ambiguités dans les clauses where
et
select
. On résout cette ambiguité en préfixant les attributs par le
nom des variables-nuplet dont ils proviennent.
Notez que la levée de l'ambiguité en préfixant par le nom de la
variable-nuplet n'est nécessaire que pour les attributs qui
apparaissent en double soit ici id
qui peut désigner l'identifiant de
la personne ou celui de l'appartement.
Comme dans la très grande majorité des cas la jointure consiste à exprimer une égalité entre la clé primaire de l'une des tables et la clé étrangère correspondante de l'autre. Mais rien n'empêche d'exprimer des conditions de jointure sur n'importe quel attribut et pas seulement sur ceux qui sont des clés.
Imaginons que l'on veuille trouver les appartements d'un même immeuble qui ont la même surface. On veut associer un nuplet de Appart à un autr nuplet de Appart avec les conditions suivantes:
- ils sont dans le même immeuble (attribut
idImmeuble
);- ils ont la même valeur pour l'attribut
surface
;- ils correspondent à des appartements distincts (attributs
id
).
La requête exprimant ces conditions est donc:
select a1.id as idAppart1, a1.surface as surface1, a1.niveau as niveau1,
a2.id as idAppart2, a2.surface as surface2, a2.niveau as niveau2
from Appart a1, Appart a2
where a1.id != a2.id
and a1.surface = a2.surface
and a1.idImmeuble = a2.idImmeuble
Ce qui donne le résultat suivant:
idAppart1 | surface1 | niveau1 | idAppart2 | surface2 | niveau2 |
---|---|---|---|---|---|
103 | 50 | 5 | 101 | 50 | 15 |
101 | 50 | 15 | 103 | 50 | 5 |
202 | 250 | 2 | 201 | 250 | 1 |
201 | 250 | 1 | 202 | 250 | 2 |
On peut noter que dans le résultat la même paire apparaît deux fois avec
des ordres inversés. On peut éliminer cette redondance en remplaçant
a1.id != a2.id
par a1.id < a2.id
.
Voici quelques exemples complémentaires de jointure.
-
Qui habite un appartement de plus de 200 m2?
select prénom, nom, profession from Personne, Appart where idAppart = Appart.id and surface >= 200
Attention à lever l'ambiguité sur les noms d'attributs quand ils peuvent provenir de deux tables (c'est le cas ici pour id
).
-
Qui habite le Barabas?
select prénom, p.nom, no, surface, niveau from Personne as p, Appart as a, Immeuble as i where p.idAppart=a.id and a.idImmeuble=i.id and i.nom='Barabas'
-
Qui habite un appartement qu'il possède et avec quelle quote-part?
select prénom, nom, quotePart from Personne as p, Propriétaire as p2, Appart as a where p.id=p2.idPersonne /* p est propriétaire */ and p2.idAppart=a.id /* de l'appartement a */ and p.idAppart=a.id /* et il y habite */
-
De quel(s) appartement(s) Alice Grincheux est-elle propriétaire et dans quel immeuble?
Voici la requête sur les quatre tables avec des commentaires inclus montrant les jointures.
select i.nom, no, niveau, surface from Personne as p, Appart as a, Immeuble as i, Propriétaire as p2 where p.id=p2.idPersonne /* Jointure PersonnePropriétaire */ and p2.idAppart = a.id /* Jointure PropriétaireAppart */ and a.idImmeuble= i.id /* Jointure AppartImmeuble */ and p.nom='Grincheux' and p.prénom='Alice'
Attention à lever l'ambiguité sur les noms d'attributs quand ils peuvent provenir de deux tables (c'est le cas ici pour
id
).
L'approche déclarative d'expression des jointures est une manière tout à fait recommandable de procéder surtout pour les débutants SQL. Elle permet de se ramener toujours à la même méthode d'interprétation et consolide la compréhension des principes d'interrogation d'une base relationnelle.
Toutes ces jointures peuvent s'exprimer avec d'autres syntaxes: tables
calculées dans le from
opérateur de jointure dans le from
ou (pas
toujours) requêtes imbriquées. À l'exception notable des jointures
externes, elles n'apportent aucune expressivité supplémentaire. Toutes
ces variantes constituent des moyens plus ou moins commodes d'exprimer
différemment la jointure.
Jointure externe¶
Qu'est-ce qu'une jointure externe? Effectuons la requête qui affiche tous les appartements avec leur occupant.
select idImmeuble, no, niveau, surface, nom, prénom
from Appart as a, Personne as p
where p.idAppart=a.id
Voici ce que l'on obtient:
idImmeuble | no | niveau | surface | nom | prénom |
---|---|---|---|---|---|
2 | 2 | 2 | 250 | Prof | null |
1 | 52 | 5 | 50 | Grincheux | Alice |
1 | 1 | 14 | 150 | Atchoum | Léonie |
1 | 51 | 2 | 200 | Simplet | Barnabé |
2 | 1 | 1 | 250 | Joyeux | Alphonsine |
1 | 43 | 3 | 75 | Timide | Brandon |
2 | 10 | 0 | 150 | Dormeur | Don-Jean |
Il manque un appartement, le 34 du Koudalou. En effet cet appartement n'a pas d'occupant. Il n'y a donc aucune possibilité que la condition de jointure soit satisfaite.
La jointure externe permet d'éviter cette élimination parfois indésirable. On considère alors une hiérarchie entre les deux tables. La première table (en général celle de gauche) est dite "directrice" et tous ses nuplets, même ceux qui ne trouvent pas de correspondant dans la table de droite, seront prises en compte. Les nuplets de la table de droite sont en revanche optionnels.
Si pour un nuplet de la table de gauche on trouve un nuplet satisfaisant
le critère de jointure dans la table de droite, alors la jointure
s'effectue normalement. Sinon, les attributs provenant de la table de
droite sont affichés à null
. Voici la jointure externe entre Appart
et Personne. Le mot-clé left
est optionnel.
select idImmeuble, no niveau, surface, nom, prénom
from Appart as a left outer join Personne as p on (p.idAppart=a.id)
idImmeuble | no | niveau | surface | nom | prénom |
---|---|---|---|---|---|
1 | 1 | 14 | 150 | Atchoum | Rachel |
1 | 34 | 15 | 50 | null | null |
1 | 51 | 2 | 200 | Simplet | Barnabé |
1 | 52 | 5 | 50 | Grincheux | Alice |
2 | 1 | 1 | 250 | Joyeux | Alphonsine |
2 | 2 | 2 | 250 | Prof | null |
Notez les deux attributs prénom
et nom
à null
pour l'appartement
34.
Il existe un right outer join
qui prend la table de droite comme table
directrice. On peut combiner la jointure externe avec des jointures
normales des sélections des tris etc. Voici la requête qui affiche le
nom de l'immeuble en plus des informations précédentes et trie par
numéro d'immeuble et numéro d'appartement.
select i.nom as nomImmeuble, no, niveau, surface, p.nom as nomPersonne, prénom
from Immeuble as i
join
(Appart as a left outer join Personne as p
on (p.idAppart=a.id))
on (i.id=a.idImmeuble)
order by i.id, a.no
Tri et élimination de doublons¶
SQL renvoie les nuplets du résultat sans se soucier de la présence de doublons. Si on cherche par exemple les surfaces des appartements avec
select surface
from Appart
on obtient le résultat suivant.
surface. 150. 50. 200. 50. 250. 250.
On a autant de fois une valeur qu'il y a de nuplets dans le résultat
intermédiaire après exécution des clauses from
et where
. En général,
on ne souhaite pas conserver ces nuplets identiques dont la répétition
n'apporte aucune information. Le mot-clé distinct
placé juste après
le select
permet d'éliminer ces doublons.
select distinct surface
from Appart
Le distinct
est à éviter quand c'est possible car l'élimination des
doublons peut entraîner des calculs coûteux. Il faut commencer par
calculer entièrement le résultat, puis le trier ou construire une table
de hachage, et enfin utiliser la structure temporaire obtenue pour
trouver les doublons et les éliminer. Si le résultat est de petite
taille cela ne pose pas de problème. Sinon, on risque de constater une
grande différence de temps de réponse entre une requête sans distinct
et la même avec distinct
.
On peut demander explicitement le tri du résultat sur une ou plusieurs
expressions avec la clause order by
qui vient toujours à la fin d'une
requête select
. La requête suivante trie les appartements par surface
puis, pour ceux de surface identique, par niveau.
select *
from Appart
order by surface, niveau
id | surface | niveau | idImmeuble | no |
---|---|---|---|---|
103 | 50 | 5 | 1 | 52 |
101 | 50 | 15 | 1 | 34 |
100 | 150 | 14 | 1 | 1 |
102 | 200 | 2 | 1 | 51 |
201 | 250 | 1 | 2 | 1 |
202 | 250 | 2 | 2 | 2 |
Par défaut, le tri est en ordre ascendant. On peut inverser l'ordre de
tri d'un attribut avec le mot-clé desc
.
select *
from Appart
order by surface desc, niveau desc
id | surface | niveau | idImmeuble | no |
---|---|---|---|---|
202 | 250 | 2 | 2 | 2 |
201 | 250 | 1 | 2 | 1 |
102 | 200 | 2 | 1 | 51 |
100 | 150 | 14 | 1 | 1 |
101 | 50 | 15 | 1 | 34 |
103 | 50 | 5 | 1 | 52 |
Bien entendu, on peut trier sur des expressions au lieu de trier sur de simples noms d'attribut.