1 4 4 2 jointure
SQL, langage algébrique 2/4 : la jointure¶
Supports complémentaires
Toutes les requêtes exprimables avec l'algèbre relationnelle peuvent se construire avec les 6 opérateurs présentés ci-dessus. En principe, on pourrait donc s'en contenter. En pratique, il existe d'autres opérations, très couramment utilisées, qui peuvent se contruire par composition des opérations de base. La plus importante est la jointure.
L'opérateur \(\Join\)¶
Afin de comprendre l'intérêt de cet opérateur, regardons le produit cartésien \(\rm{Logement} \times \rm{Activité}\), dont le résultat est rappelé ci-dessous.
code | nom | capacité | type | lieu | codeLogement | codeActivité |
---|---|---|---|---|---|---|
ca | Causses | 45 | Auberge | Cévennes | ca | Randonnée |
ge | Génépi | 134 | Hôtel | Alpes | ca | Randonnée |
pi | U Pinzutu | 10 | Gîte | Corse | ca | Randonnée |
ta | Tabriz | 34 | Hôtel | Bretagne | ca | Randonnée |
ca | Causses | 45 | Auberge | Cévennes | ge | Piscine |
ge | Génépi | 134 | Hôtel | Alpes | ge | Piscine |
pi | U Pinzutu | 10 | Gîte | Corse | ge | Piscine |
ta | Tabriz | 34 | Hôtel | Bretagne | ge | Piscine |
ca | Causses | 45 | Auberge | Cévennes | ge | Ski |
ge | Génépi | 134 | Hôtel | Alpes | ge | Ski |
pi | U Pinzutu | 10 | Gîte | Corse | ge | Ski |
ta | Tabriz | 34 | Hôtel | Bretagne | ge | Ski |
ca | Causses | 45 | Auberge | Cévennes | pi | Plongée |
ge | Génépi | 134 | Hôtel | Alpes | pi | Plongée |
pi | U Pinzutu | 10 | Gîte | Corse | pi | Plongée |
ta | Tabriz | 34 | Hôtel | Bretagne | pi | Plongée |
ca | Causses | 45 | Auberge | Cévennes | pi | Voile |
ge | Génépi | 134 | Hôtel | Alpes | pi | Voile |
pi | U Pinzutu | 10 | Gîte | Corse | pi | Voile |
ta | Tabriz | 34 | Hôtel | Bretagne | pi | Voile |
Si vous regardez attentivement cette relation, vous noterez que le
résultat comprend manifestement un grand nombre de nuplets qui ne nous
intéressent pas. C'est le cas de toutes les lignes pour lesquelles le
code
(provenant de la table Logement
) et le codeLogement
(provenant de la table Activité
) sont distincts. Cela ne présente pas
beaucoup de sens (a priori) de rapprocher des informations sur l'hôtel
Génépi, dans les Alpes, avec l'activité de plongée en Corse.
Note
Il est bien sûr arbitraire de dire qu'un résultat "n'a pas de sens" ou "ne présente aucun intérêt". Nous nous plaçons ici dans un contexte où l'on cherche à reconstruire une information sur certaines entités du monde réel, dont la description a été distribuée dans plusieurs tables par la normalisation. C'est l'utilisation sans doute la plus courante de SQL.
Si, en revanche, on considère le produit cartésien comme un résultat intermédiaire, on voit qu'il permet d'associer des nuplets initialement répartis dans des tables distinctes. Sur notre exemple, on rapproche les informations générales sur un logement et la liste des activités de ce logement.
La sélection qui effectue une rapprochement pertinent est celle qui ne
conserve que les nuplets partageant la même valeur pour les attributs
code
et codeLogement
, soit :
Prenez bien le temps de méditer cette opération de sélection : nous ne
voulons conserver que les nuplets de
\(\rm{Logement} \times \rm{Activité}\) pour lesquelles l'identifiant du
logement (provenant de Logement
) est identique à celui provenant de
Activité
. En regardant le produit cartésien ci-dessous, vous devriez
pouvoir vous convaincre que cela revient à conserver les nuplets qui ont
un sens : chacune contient des informations sur un logement et sur une
activité dans ce même logement.
On obtient le résultat ci-dessous.
code | nom | capacité | type | lieu | codeLogement | codeActivité |
---|---|---|---|---|---|---|
ca | Causses | 45 | Auberge | Cévennes | ca | Randonnée |
ge | Génépi | 134 | Hôtel | Alpes | ge | Piscine |
ge | Génépi | 134 | Hôtel | Alpes | ge | Ski |
pi | U Pinzutu | 10 | Gîte | Corse | pi | Plongée |
pi | U Pinzutu | 10 | Gîte | Corse | pi | Voile |
On a donc effectué une composition de deux opérations (un produit cartésien, une sélection) afin de rapprocher des informations réparties dans plusieurs relations, mais ayant des liens entre elles (toutes les informations dans un nuplet du résultat sont relatives à un seul logement). Cette opération est une jointure, que l'on peut directement, et simplement, noter :
La jointure consiste donc à rapprocher les nuplets de deux relations
pour lesquelles les valeurs d'un (ou plusieurs) attributs sont
identiques. De fait, dans la plupart des cas, ces attributs communs sont
(1) la clé primaire de l'une des relations et (2) la clé étrangère dans
l'autre relation. Dans l'exemple ci-dessus, c'est le cas pour code
(clé primaire de Logement) et codeLogement
(clé étrangère dans
Activité).
Note Le logement Tabriz, qui ne propose pas d'activité, n'apparaît pas dans le résultat de la jointure. C'est normal et conforme à la définition que nous avons donnée, mais peut parfois apparaître comme une contrainte. Nous verrons dans le chapitre final sur SQL que ce dernier propose une variante, la jointure externe, qui permet de la contourner.
La notation de la jointure, \(R \Join_F S\), est un racourci pour \(\sigma_F(R \times S)\).
Note Le critère de rapprochement, \(F\), peut être n'importe quelle opération de comparaison liant un attribut de \(R\) à un attribut de \(S\). En pratique, on emploie peu les \(\not=\) ou '[\<]{.title-ref}' qui sont difficiles à interpréter, et on effectue des égalités. Si on n'exprime pas de critère de rapprochement, la jointure est équivalente à un produit cartésien.
Initialement, SQL ne proposait pour effectuer la jointure que la version déclarative.
select *
from Logement as l, Activité as a
where l.code=a.codeLogement
En 1992, la révision de la norme a introduit l'opérateur algébrique
qui, comme le produit cartésien, et pour les mêmes raisons, prend place
dans le from
.
select *
from Logement join Activité on (code=codeLogement)
Il s'agit donc d'une manière alternative d'exprimer une jointure. Laquelle est la meilleure ? Aucune, puisque toutes les deux ne sont que des spécifications, et n'imposent en aucun cas au système une méthode particulière d'exécution. Il est d'ailleurs exclu pour un système d'appliquer aveuglément la définition de la jointure et d'effectuer un produit cartésien, puis une sélection, car il existe des algorithmes d'évaluation bien plus efficaces.
Résolution des ambiguités¶
Il faut être attentif aux ambiguités dans le nommage des attributs qui peut survenir dans la jointure au même titre que dans le produit cartésien. Les solutions à employer sont les mêmes : on préfixe par le nom de la relation ou par un synonyme, ou bien on renomme des attributs avant d'effectuer la jointure.
Supposons que l'on veuille obtenir les voyageurs et les séjours qu'ils ont effectués. La jointure s'exprime en principe comme suit :
select *
from Voyageur join Séjour on (idVoyageur=idVoyageur)
Le système renvoie une erreur : La clause de jointure
on (idVoyageur=idVoyageur)
est clairement ambiguë. Pour MySQL, le
message est par exemple Column 'idVoyageur' in on clause is
ambiguous. Nouvelle tentative :
select *
from Voyageur join Séjour on (Voyageur.idVoyageur=Séjour.idVoyageur)
Nouveau message d'erreur (cette fois, sous MySQL: Encountered
duplicate field name: 'idVoyageur'). La liste des noms d'attribut
dans le nuplet-résultat obtenu avec select *
comprend encore deux fois
idVoyageur
.
Première solution : on renomme les attributs du nuplet résultat. Cela suppose d'énumérer tous les attributs.
select V.idVoyageur as idV1, V.nom, S.idVoyageur as idV2, début, fin
from Voyageur as V join Séjour as S on (V.idVoyageur=S.idVoyageur)
Cette première solution consiste à effectuer un renommage après la jointure. Une autre solution est d'effectuer le renommage avant la jointure.
select *
from (select idVoyageur as idV1, nom from Voyageur) as V
join
(select idVoyageur as idV2, début, fin from Séjour) as S
on (V.idV1=S.idV2)
En algèbre, la requête ci-dessus correspond à l'expression suivante :
On voit que le from
commence à contenir des expressions de plus en
plus complexes. Dans ses premières versions, SQL ne permettait pas des
constructions algébriques dans le from
, ce qui avait l'avantage
d'éviter des constructions qui ressemblent de plus en plus à de la
programmation. Rappelons qu'il existe une syntaxe alternative à la
requête ci-dessus, dans la forme déclarative de SQL étudiée au chapitre
précédent.
select V.idVoyageur as idV1, V.nom, S.idVoyageur as idV2, début, fin
from Voyageur as V, Séjour as S
where V.idVoyageur= S.idVoyageur
Bref, vous commencez à avoir l'embarras du choix.
La jointure dite "naturelle"
il reste à vrai dire, avec SQL, un troisième choix, la jointure dite "naturelle". Elle s'applique uniquement quand les attributs de jointure ont des noms identiques dans les deux tables. C'est le cas ici (l'attribut de jointure estidVoyageur
, que ce soit dansLogement
ou dansSéjour
). La jointure naturelle s'effectue alors automatiquement sur ces attributs communs, et ne conserve que l'un des attributs dans le résultat, ce qui élimine l'ambiguité. La syntaxe devient alors très simple.
select *
from Voyageur as V natural join Séjour
Si les attributs de jointures sont nommés différemment, la jointure naturelle devient plus délicate à utiliser puisqu'il faut au préalable effectuer des renommages pour faire coïncider les noms des attributs à comparer.
À partir de là, vous savez comment effectuer plusieurs jointures. Un exemple devrait suffire : supposons que l'on veuille les noms des voyageurs et les noms des logements qu'ils ont visités. La requête algébrique devient un peu compliquée. On va s'autoriser une construction en plusieurs étapes.
Tout d'abord on effectue un renommage sur la table Voyageur
pour
éviter les futures ambiguités.
Opération semblable sur les logements.
Et finalement, voici la requête algébrique complète, utilisant V2
et
L2
.
En SQL, il faut tout écrire avec une seule requête. Allons-y.
select nomVoyageur, nomLogement
from ( (select idVoyageur as idV, nom as nomVoyageur from Voyageur) as V
join
Séjour as S on idV=idVoyageur)
join
(select code, nom as nomLogement from Logement) as L
on codeLogement = code
Ce n'est pas très lisible... Pour comparaison, la version déclarative de ces jointures.
select V.nom as nomVoyageur, L.nom as nomLogement
from Voyageur as V, Séjour as S, Logement as L
where V.idVoyageur = S.idVoyageur
and S.codelogement = L. code
À vous de voir quel style (ou mélange des styles) vous souhaitez adopter.