Les jointures

Nous travaillons sur un SGBD relationnel; les tables sont donc en relation, elles disposent d'une clé primaire, et les relations entre elles vont associer la clé primaire d'une des tables concernées avec un champ de même nature d'une autre table; ce dernier champ est appelé clé étrangère ou clé secondaire. L'intérêt du modèle relationnel n'est plus à démontrer, et nous allons en avoir un aperçu de suite.

On pourrait penser que l'expression suivante suffirait:

SELECT nom, prenom, titre FROM auteur, livre

Jointures simples

Une telle requête sans restriction entraîne un produit cartésien ce qui se traduit par un très grand nombre de lignes qui ne présente pas un grand intérêt. Concrètement on associe en procédant ainsi chaque auteur à chaque livre. Le nombre de lignes est alors égal au nombre d'auteur que multiplie le nombre de livres...Comme si tous les ouvrages avaient été écrits par chaque auteur...Il est évident qu'il faut exprimer des restrictions: les deux tables contiennent un champ commun: id_auteur, qui est clé primaire de la table auteur, et qui devient clé étrangère de la table livre. Ces deux colonnes sont de même nature, et on va demander l'affichage des noms prénoms de la table auteur, et titres de la table livre uniquement pour les enregistrements de ces deux tables qui ont un les même valeurs dans ce champ commun: id_auteur. La clause WHERE va nous permettre d'y arriver.

SELECT nom, prenom, titre , auteur.id_auteur, livre.id_auteur FROM auteur, livre WHERE (auteur.id_auteur=livre.id_auteur)

aron	raymond	Les Modernes	aron1	aron1
aron	jean-paul	Les désillusions du progrès	aron2	aron2
perry	anne	Un plat qui se mange froid	perr1	perr1
hillerman	tony	Moon	hill1	hill1
py	bernard	Statistiques descriptives	py1	py1
aron	jean-paul	Introduction à la philosophie politique	aron2	aron2
camus	albert	L'étranger	camu1	camu1
camus	albert	La peste	camu1	camu1
zola	emile	Au bonheur des dames	zola1	zola1
hillerman	tony	Le premier aigle	hill1	hill1
balzac	honore	La peau de chagrin	balz1	balz1
balzac	honore	Eugénie grandet	balz1	balz1
duras	marguerite	L'amant	dura1	dura1
duras	marguerite	Un barrage contre le pacifique	dura1	dura1
gary	romain	La vie devant soi	gary1	gary1
forta	ben	sql	fort1	fort1
dubois	paul	MySQL	dubo1	dubo1
zola	emile	La debâcle	zola1	zola1
perry	pascal	Le tourisme à la Martinique : sous la plage, les conflits	perr2	perr2
balzac	honore	Le père Goriot	balz1	balz1
zola	emile	Nana	zola1	zola1

h3>On voit bien ci-dessus que les seuls enregistrements affichés sont ceux qui ont des valeurs communes dans leur champ id_auteur. Il n'est.bien sûr pas indispensable de les afficher:

SELECT nom, prenom, titre FROM auteur, livre WHERE (auteur.id_auteur=livre.id_auteur)

aron	raymond	Les Modernes
aron	jean-paul	Les désillusions du progrès
perry	anne	Un plat qui se mange froid
hillerman	tony	Moon
py	bernard	Statistiques descriptives
aron	jean-paul	Introduction à la philosophie politique
camus	albert	L'étranger
camus	albert	La peste
zola	emile	Au bonheur des dames
hillerman	tony	Le premier aigle
balzac	honore	La peau de chagrin
balzac	honore	Eugénie grandet
duras	marguerite	L'amant
duras	marguerite	Un barrage contre le pacifique
gary	romain	La vie devant soi
forta	ben	sql
dubois	paul	MySQL
zola	emile	La debâcle
perry	pascal	Le tourisme à la Martinique : sous la plage, les conflits
balzac	honore	Le père Goriot
zola	emile	Nana

Une autre syntaxe est possible avec l'opérateur INNER ...JOIN ....ON:

SELECT nom, prenom, titre FROM auteur INNER JOIN livre ON auteur.id_auteur=livre.id_auteur

aron	raymond	Les Modernes
aron	jean-paul	Les désillusions du progrès
perry	anne	Un plat qui se mange froid
hillerman	tony	Moon
py	bernard	Statistiques descriptives
aron	jean-paul	Introduction à la philosophie politique
camus	albert	L'étranger
camus	albert	La peste
zola	emile	Au bonheur des dames
hillerman	tony	Le premier aigle
balzac	honore	La peau de chagrin
balzac	honore	Eugénie grandet
duras	marguerite	L'amant
duras	marguerite	Un barrage contre le pacifique
gary	romain	La vie devant soi
forta	ben	sql
dubois	paul	MySQL
zola	emile	La debâcle
perry	pascal	Le tourisme à la Martinique : sous la plage, les conflits
balzac	honore	Le père Goriot
zola	emile	Nana

Bien sûr les éléments de tri, de filtrage et de calcul peuvent être intégrées.

Jontures et Tri

Même requête que ci-dessus mais avec un tri alphabétique du nom de l'auteur et du titre des ouvrages; On affiche également le nombre de pages.

SELECT nom, prenom, titre, nb_pages FROM auteur INNER JOIN livre ON auteur.id_auteur=livre.id_auteur ORDER BY nom, titre

aron	jean-paul	Introduction à la philosophie politique	450
aron	jean-paul	Les désillusions du progrès	286
aron	raymond	Les Modernes	376
balzac	honore	Eugénie grandet	315
balzac	honore	La peau de chagrin	280
balzac	honore	Le père Goriot	360
camus	albert	L'étranger	220
camus	albert	La peste	330
dubois	paul	MySQL	735
duras	marguerite	L'amant	185
duras	marguerite	Un barrage contre le pacifique	380
forta	ben	sql	234
gary	romain	La vie devant soi	285
hillerman	tony	Le premier aigle	325
hillerman	tony	Moon	340
perry	pascal	Le tourisme à la Martinique : sous la plage, les conflits	250
perry	anne	Un plat qui se mange froid	84
py	bernard	Statistiques descriptives	330
zola	emile	Au bonheur des dames	350
zola	emile	La debâcle	225
zola	emile	Nana	350

Les auteurs sont donc classée par ordre alphabétique. Pour ceux qui ont écrit plusieurs livres, les titres sont classés également par ordre alphabétique

Jointures et filtre

Même requête que la précédente mais on se limite aux auteurs suivants: Aron, Balzac, Perry, Zola:

SELECT nom, prenom, titre, nb_pages FROM livre, auteur WHERE (auteur.id_auteur=livre.id_auteur AND nom IN ('aron', 'balzac','perry', 'zola') ORDER BY nom, titre

aron	jean-paul	Introduction à la philosophie politique	450
aron	jean-paul	Les désillusions du progrès	286
aron	raymond	Les Modernes	376
balzac	honore	Eugénie grandet	315
balzac	honore	La peau de chagrin	280
balzac	honore	Le père Goriot	360
perry	pascal	Le tourisme à la Martinique : sous la plage, les conflits	250
perry	anne	Un plat qui se mange froid	84
zola	emile	Au bonheur des dames	350
zola	emile	La debâcle	225
zola	emile	Nana	350

Constatons qu'il suffit de combiner les restrictions (WHERE) en utilsant l'opérateur logique AND et des parenthèses (pas obligatoires) pour arriver simplement à ce résultat.

Jointures et calculs

Objectif: déterminer le nombre d'ouvrages écrits par chaque auteur. Nous avons la réponse en comptant manuellement les lignes dans les réponses ci-dessus...:) On préfèrera une réponse avec le nom, le prénom de chaque auteur et le nombre d'ouvrages par auteur.

SELECT nom, prenom, COUNT(titre) AS Nb_Ouvrages FROM auteur, livre WHERE (auteur.id_auteur=livre.id_auteur) GROUP BY nom, prenom

aron	jean-paul	2
aron	raymond	1
balzac	honore	3
camus	albert	2
dubois	paul	1
duras	marguerite	2
forta	ben	1
gary	romain	1
hillerman	tony	2
perry	anne	1
perry	pascal	1
py	bernard	1
zola	emile	3

Ajoutons un filtre et un tri: même requête mais pour les ouvrages dont le nombre de pages est supérieur à 300. L'afichage se fera en tenant compte de l'ordre décroissant du nombre d'ouvrages, puis par nom d'auteurs croissant.

SELECT nom, prenom, COUNT(titre) AS Nb_Ouvrages FROM auteur, livre WHERE (auteur.id_auteur=livre.id_auteur AND nb_pages>300) GROUP BY nom, prenom ORDER BY Nb_Ouvrages DESC, nom

balzac	honore	2
hillerman	tony	2
zola	emile	2
aron	jean-paul	1
aron	raymond	1
camus	albert	1
dubois	paul	1
duras	marguerite	1
py	bernard	1

Jointures avec plus de deux tables

L'écriture est plus longue mais le principre reste le même. Nous souhaitons disposer des renseignements suivants: Nom et prénoms des auteurs, titres des ouvrages consernés et éditeurs (Tri croissant sur le nom des auteurs qui sera écrit en majuscule).

SELECT UCASE(nom), prenom, titre, nom_editeur FROM auteur, livre, editeur WHERE (auteur.id_auteur=livre.id_auteur AND livre.id_editeur=editeur.id_editeur)ORDER BY nom, prenom

jean-paul	Les désillusions du progrès	Gallimard
jean-paul	Introduction à la philosophie politique	Gallimard
raymond	Les Modernes	Gallimard
honore	La peau de chagrin	Livre de poche
honore	Eugénie grandet	Livre de poche
honore	Le père Goriot	Livre de poche
albert	L'étranger	Gallimard
albert	La peste	Gallimard
paul	MySQL	Campus Press
marguerite	Un barrage contre le pacifique	Gallimard
marguerite	L'amant	Minuit
ben	sql	Campus Press
romain	La vie devant soi	Gallimard
tony	Le premier aigle	Seuil
tony	Moon	Seuil
anne	Un plat qui se mange froid	Seuil
pascal	Le tourisme à la Martinique : sous la plage, les conflits	Khartala
bernard	Statistiques descriptives	Economica
emile	Nana	Livre de poche
emile	La debâcle	Gallimard
emile	Au bonheur des dames	Livre de poche

A vous

1 - Trouver le code SQL pour parvenir au résultat suivant:

Economica	25.00
Khartala	15.00
Seuil	11.67
Minuit	10.00
Livre de poche	7.20
Gallimard	6.50
Campus Press	4.00

2 - Nombre de pages maximum, moyen, et minimum des ouvrages publiés par Gallimard.
3 - Titre, genre, editeur, nom, prenom des ouvrages écrits par les auteurs dont le nom ne comprend pas la lettre E.
4 - Nombre de pages moyen par editeur pour les ouvrages dont le type comprend le mot Roman.