join dans nos requêtes, nous aurions dû utiliser
inner join pour bien montrer le caractère "interne" de ces jointures. Cela dit, quand le mot-clé
inner est omis, c'est que la jointure est interne.
Les jointures internes ne répondent malheureusement pas à tous les cas que l'on rencontre. Prenons l'exemple suivant.
On s'intéresse à des bateaux qui livrent des marchandises. Une première table regroupe les bateaux, et une seconde stocke les livraisons de chaque bateau. Un bateau peut livrer plusieurs fois, la relation entre la table bateau et la table livraison est donc de type
1:p.
Voyons tout d'abord la structure de données que nous allons utiliser.
Exemple 65. Structure de données bateau et livraisons
-- table des bateaux create table Bateaux ( id int primary key, nom_bateau varchar(32) ) ; -- table des livraisons create table Livraisons ( id int primary key, id_bateau int, tonnage int ) ; -- une livraison doit référencer un bateau alter table Livraisons add constraint FK_livraisons_bateaux foreign key Livraisons(id_bateau) references Bateaux(id) ;
Exemple 66. Tonnage livré par bateau - 1
select nom_bateau, sum(tonnage)
from Livraisons
join Bateaux on Livraisons.id_bateau = Bateaux.id
group by nom_bateau
order by nom_bateau ;
NOM SUM(TONNAGE) -------------------- ------------ Altaïr 220 Méduse 230 Perle 190On remarque immédiatement qu'un bateau manque : l'Améthyste. Il n'a pas été sélectionné par la requête, tout simplement parce qu'il n'a rien livré, et que le prédicat
Livraisons.id_bateau = Bateaux.id n'est jamais vrai pour l'Améthyste.
Ce que l'on aurait aimé, c'est que l'Améthyste apparaisse dans le tableau de résultat, avec une valeur 0 pour le tonnage, ce qui n'est pas possible avec les jointures internes. C'est là que les jointures externes interviennent.
Exemple 67. Tonnage livré par bateau - 2
select nom_bateau, sum(tonnage)
from Livraisons
right outer join Bateaux on Livraisons.id_bateau = Bateaux.id
group by nom_bateau
order by nom_bateau ;
NOM SUM(TONNAGE) -------------------- ------------ Altaïr 220 Améthyste Méduse 230 Perle 190Utiliser la clause
right outer join plutôt que
join indique au serveur que la table à droite de la jointure doit être préservée dans le résultat, c'est-à-dire que toutes ses lignes doivent apparaître, même si elles ne vérifient pas la condition de jointure.
On peut de la même façon utiliser la clause
left outer join, pour préserver les lignes de la table de gauche, ou
full outer join pour préserver les lignes des deux tables.
Exemple 68. Structure hiérarchique
-- notre table marin avec le lien de subordination create table Marins ( id int primary key, nom varchar(32), id_capitaine int ) ; -- un marin doit avoir un capitaine alter table Marins add constraint FK_marins_capitaine foreign key Marins(id_capitaine) references Marins(id) ; -- liste des marins avec le nom de leur capitaine select t1.nom as nom_marin, t2.nom as nom_capitaine from Marins as t1 join Marins as t2 on t1.id_capitaine = t2.id order by nom_marin ;
select qui permet d'afficher le tableau de tous les marins qui ont un capitaine, et leur capitaine. Le problème ici est que la table
Marins est référencée deux fois dans la clause
from : de part et d'autre de la jointure. Pour que ceci fonctionne, il faut impérativement utiliser un alias :
Marins as t1 et
Marins as t2. De la sorte, on peut différencier la table qui se trouve à droite de la jointure, de celle qui se trouve à gauche. On notera que la liste des champs précise dans quelle table on choisit le champ que l'on veut.
Si l'on avait voulu la liste complète des marins, qu'ils aient un capitaine ou non, on aurait remplacé simplement le
join par
left outer join.