Jointure

Vous êtes ici : >> SQL / Les Jointures SQL
Temps d'étude : 2h. Niveau : Moyen.
Tags : Base de données, cours sql, Jointures SQL
Fichier(s) utile(s) pour ce cours : bibliotheque.sql

Les Jointures SQL

Tout comme les requêtes imbriquées, les jointures SQL permettent d'effectuer des requêtes sur plusieurs tables.

De la même manière que pour les requêtes imbriquées, si l'on souhaite pouvoir réaliser une jointure il faut qu'un champ commun se trouve dans les différentes tables concernées.

L'avantage des jointures est que l'on peut obtenir dans le résultat final des colonnes / champs issue de plusieurs tables différentes.

Décomposition d'une requête de jointure

Pour effectuer une jointure, nous respecterons le format suivant :

SQL - Requête de Jointure - SELECT
	SELECT -- liste des champs que je souhaite afficher dans mon résultats
	FROM -- de quelles table cela provient ? de quelle table vais-je avoir besoin ?
	WHERE -- condition de jointure pour assurer le croisement des données.
	AND/OR -- autre conditions éventuelles
	


Pour se mettre en jambe, nous allons prendre un cas concret sur notre base de données bibliotheque.




Demande : Afficher les dates auxquelles Guillaume s'est rendu à la bibliothèque pour emprunter ou pour rendre un livre.

Indices :
- Nous attendons un résultat à 3 colonnes avec les champs suivants : prenom - date_sortie - date rendu

Requête :

SQL - Requête de Jointure - SELECT
	SELECT a.prenom, e.date_sortie, e.date_rendu
	FROM abonne a, emprunt e
	WHERE a.id_abonne=e.id_abonne
	AND a.prenom='guillaume';

Explications :
Ligne 1 - SELECT - Nous selectionnons les champs que nous souhaitons obtenir dans le résultat, c'est à dire : prenom, date_sortie, date_rendu
Sur cette même ligne, vous pourrez noter l'utilisation de préfixe a., e. Pour le moment ces préfixes ne correspondent à rien.

Ligne 2 - FROM - Nous annonçons les tables dont nous aurons besoin pour réussir notre requête, dans notre cas : abonne, emprunt.
Sur cette même ligne, vous pouvez constater la définition des préfixes "abonne a", "emprunt e", cela permet au système de comprendre que le "e" représente la table emprunt et que le "a" représente la table abonne.
L'avantage des préfixes est de pouvoir donner un nom plus court (1 ou plusieurs lettres) aux tables afin de les représenter, nous garderons à l'esprit que le format idéal c'est : bdd.table.champ.
Cela sera légérement plus long à écrire mais cela aura l'avantage d'être clair pour quelqu'un qui nous rejoindrait sans connaitre la modélisation par coeur (surtout dans le cas de modélisation complexe, ça aide).
Il faudra garder à l'esprit qu'on utilise d'abord les préfixes (sur la ligne n°1) avant de les définir (sur la ligne n°2)

Ligne 3 - WHERE - La condition WHERE permet d'assurer le croisement des données entre la table abonne et la table emprunt.
Nous passons par notre champ commun id_abonne. C'est précisément ce qui permet d'effectuer notre jointure.

Ligne 4 - AND - La condition AND permet d'appliquer une condition complémentaire, dans notre cas nous souhaitions connaitre les dates de passages de Guillaume.

Résultat
:

résultat
prenomdate_sortiedate_rendu
Guillaume2014-12-172014-12-18
Guillaume2014-12-192014-12-28



Si nous voulions arriver au même résultat avec une requête imbriquée cela ne serait pas possible puisque les colonnes / champs ne sont pas issues de la même table.

image manquante

Pour qu'une requête imbriquée soit possible, il faut que les colonnes présentes dans le résultat final soient issues de la même table.

SQL - Requête Imbriquée - SELECT
	SELECT date_sortie, date_rendu FROM emprunt WHERE id_abonne = (SELECT id_abonne FROM abonne WHERE prenom='guillaume');

Dans ce résultat, les colonnes du résultat sont issue de la même table (date_sortie et date_rendu provienne d'une seule table : livre).

résultat
date_sortiedate_rendu
2014-12-172014-12-18
2014-12-192014-12-28


fléche Informations
- nous pouvons tout faire avec une jointure, ce qui n'est pas le cas de la requête imbriquée.
- nous utiliserons quand même la requete imbriquée si nous visons uniquement des colonnes d'une seule table dans le résultat final (plus optimisé).





Demande : Afficher les dates auxquelles les livres écrits par Alphonse Daudet ont été empruntés ou rendus à la bibliothèque.

Indices :
- Nous attendons un résultat à 3 colonnes avec les champs suivants : date_sortie - date rendu
- La jointure et la requête imbriquées sont possibles puisque les colonnes du résultat final sont proviennent de la même table.

Requête :

SQL - Requête de Jointure - SELECT
	SELECT e.date_sortie, e.date_rendu
	FROM livre l, emprunt e
	WHERE l.id_livre=e.id_livre
	AND l.auteur = 'Alphonse Daudet';

Explications :
Ligne 1 - SELECT - Nous selectionnons les champs que nous souhaitons obtenir dans le résultat : date_sortie, date_rendu (nous utilisons des préfixes)
Ligne 2 - FROM - Nous annonçons les tables dont nous aurons besoin pour réussir notre requête, dans notre cas : livre, emprunt. (nous définissons les préfixes)
Ligne 3 - WHERE - La condition WHERE permet d'assurer le croisement des données entre la table livre et la table emprunt. Nous passons par notre champ commun id_livre. C'est précisément ce qui permet d'effectuer notre jointure.
Ligne 4 - AND - La condition AND permet d'appliquer une condition complémentaire, dans notre cas nous souhaitions connaitre les dates d'emprunt et dates de rendu de livre pour Alphonse Daudet.

Résultat
:

résultat
date_sortiedate_rendu
2014-12-192014-12-22

La même chose en requête imbriquée :

SQL - Requête Imbriquée - SELECT
SELECT date_sortie, date_rendu FROM emprunt WHERE id_livre IN ( SELECT id_livre FROM livre WHERE auteur='Alphonse Daudet');




Demande : Qui a emprunté le livre 'Une Vie' sur l'année 2014 ?

Indices :
- Nous attendons un résultat à 1 colonne avec le champ prenom
- La jointure et la requête imbriquées sont possibles puisque il n'y a qu'une seule colonne (donc forcément issue d'une seule table).

Requête :

SQL - Requête de Jointure - SELECT
	SELECT a.prenom
	FROM abonne a, emprunt e, livre l
	WHERE l.id_livre = e.id_livre
	AND e.id_abonne = a.id_abonne
	AND l.titre = 'Une vie'
	AND e.date_sortie LIKE '2014%';

Explications :
Ligne 1 - SELECT - Nous selectionnons les champs que nous souhaitons obtenir dans le résultat : prenom (nous utilisons des préfixes)
Ligne 2 - FROM - Nous annonçons les tables dont nous aurons besoin pour réussir notre requête, dans notre cas : abonne, livre, emprunt. (nous définissons les préfixes)
Ligne 3 - WHERE - La condition WHERE permet d'assurer le croisement des données entre la table livre et la table emprunt. Nous passons par notre champ commun id_livre.
Ligne 4 - AND - La condition AND permet également d'effectuer une jointure, cette fois-ci entre la table abonne et la table emprunt. Nous passons par notre champ commun id_abonne.
Ligne 5 - AND - La condition AND permet de cibler le livre ayant pour titre "Une vie" (conformément à la demande de départ).
Ligne 6 - AND - La condition AND permet de cibler la date de sortie à l'année 2014 (conformément à la demande de départ).
Résultat
:

résultat
prenom
Guillaume
Chloe

La même chose en requête imbriquée :

SQL - Requête Imbriquée - SELECT
SELECT prenom FROM abonne WHERE id_abonne IN ( SELECT id_abonne FROM emprunt WHERE date_sortie LIKE '2014%' AND id_livre = ( SELECT id_livre FROM livre WHERE titre='Une vie'));




Demande : Afficher le nombre de livres empruntés par chaque abonné

Indices :
- Nous attendons un résultat à 2 colonnes avec le champ prenom et le nombre (le champ nombre n'existe pas, il faudra donc qu'il soit calculé)

Requête :

SQL - Requête de Jointure - SELECT
	SELECT a.prenom, COUNT(e.id_livre) AS 'nombre de livre emprunte'
	FROM abonne a, emprunt e
	WHERE a.id_abonne=e.id_abonne
	GROUP BY e.id_abonne;

Explications :

Ligne 1 - SELECT - Nous selectionnons le champ prenom et comptons le nombre de références id_livre dans la table emprunt (nous aurions pu choisir n'importe quel champ de la table emprunt pour ce calcul). Nous utilisons des préfixes.

Ligne 2 - FROM - Nous annonçons les tables dont nous aurons besoin pour réussir notre requête, dans notre cas : abonne, emprunt. (nous définissons les préfixes)

Ligne 3 - WHERE - La condition WHERE permet d'assurer le croisement des données entre la table abonne et la table emprunt. Nous passons par notre champ commun id_abonne.

Ligne 4 - GROUP BY - Cette ligne nous permet de faire des regroupements pour répartir le calcul pour chacun des abonnés.

Résultat
:

résultat
prenomnombre de livre emprunte
Guillaume2
Benoit3
Chloe2
Laura1




Demande : Qui a emprunté Quoi ? et Quand ?

Indices :
- Qui = prenom
- Quoi = titre de livre
- Quand = date_sortie
- Nous attendrons un résultat avec 3 colonnes.

Requête :

SQL - Requête de Jointure - SELECT
	SELECT a.prenom, l.titre, e.date_sortie
    FROM abonne a, emprunt e, livre l
    WHERE a.id_abonne=e.id_abonne
	AND e.id_livre = l.id_livre;

Explications :

Ligne 1 - SELECT - Nous selectionnons le champ prenom, titre, et date_sortie.

Ligne 2 - FROM - Nous annonçons les tables dont nous aurons besoin pour réussir notre requête, dans notre cas : abonne, livre, emprunt.

Ligne 3 - WHERE - La condition WHERE permet d'assurer le croisement des données entre la table abonne et la table emprunt. Nous passons par notre champ commun id_abonne.

Ligne 4 - AND - La condition AND permet également d'effectuer une jointure, cette fois-ci entre la table livre et la table emprunt. Nous passons par notre champ commun id_livre.

Résultat
:

résultat
prenomtitredate_sortie
GuillaumeUne vie2014-12-17
GuillaumeLa Reine Margot2014-12-19
BenoitBel-Ami 2014-12-18
BenoitLes Trois Mousquetaires2015-03-20
BenoitUne vie2015-06-15
ChloeUne vie2014-12-19
ChloeLes Trois Mousquetaires2015-06-13
LauraLe Petit chose2014-12-19


Pas de requête imbriquée possible pour arriver à ce résultat.


Avant d'effectuer la prochaine requête, nous allons ajouter un abonné (qui par défaut, n'aura pas emprunté de livre) :

SQL - Requête d'insertion - INSERT
	INSERT INTO abonne (prenom) VALUES ('Alex');
	SELECT * FROM abonne;

résultat
id_abonneprenom
1Guillaume
2Benoit
3Chloe
4Laura
5Alex





Demande : Afficher le prénom des abonnés avec le numéro des livres qu'ils ont emprunté

Indices :
- champs ciblés : prenom, id_livre

Requête :

SQL - Requête de Jointure - SELECT
	SELECT ab.prenom, em.id_livre
	FROM abonne ab, emprunt em
	WHERE ab.id_abonne = em.id_abonne;

Explications :

Ligne 1 - SELECT - Nous selectionnons le champ prenom et id_livre.

Ligne 2 - FROM - Nous annonçons les tables dont nous aurons besoin pour réussir notre requête, dans notre cas : abonne, emprunt.

Ligne 3 - WHERE - La condition WHERE permet d'assurer le croisement des données entre la table abonne et la table emprunt. Nous passons par notre champ commun id_abonne.

Résultat
:

résultat
prenomid_livre
Guillaume100
Guillaume104
Benoit101
Benoit105
Benoit100
Chloe100
Chloe105
Laura103


Comme vous pouvez le constater, Alex notre dernier abonné ne se trouve pas parmi la liste de résultat.

Est-ce normal ? Oui c'est plutôt logique puisqu'il n'a emprunté aucun livre jusqu'à présent.

Il est quand même possible de retrouver sa trace via une jointure externe.




Jointure Externe

Demande : Afficher le prénom des abonnés avec le numéro des livres qu'ils ont emprunté

Indices :
- champs ciblés : prenom, id_livre

Requête :

SQL - Requête de Jointure (externe) - SELECT
	SELECT abonne.prenom, emprunt.id_livre
	FROM abonne LEFT JOIN emprunt 
	ON abonne.id_abonne = emprunt.id_abonne;

Explications :

Ligne 1 - SELECT - Nous selectionnons le champ prenom et id_livre.

Ligne 2 - FROM - LEFT JOIN - La table placée à gauche de l'expression LEFT JOIN sera la table dont tous les résultats seront rapatriés (sans correspondance exigée dans l'autre table).
Nous savons qu'Alex fera partie du résultat même s'il n'a pas emprunté de livre.

Ligne 3 - ON - La condition se fait par le mot clé ON et non plus WHERE dans le cadre d'une requête externe type LEFT JOIN, RIGHT JOIN (FULL JOIN n'étant pas valable sous Mysql).

Résultat
:

résultat
prenomid_livre
Guillaume100
Guillaume104
Benoit101
Benoit105
Benoit100
Chloe100
Chloe105
Laura103
AlexNULL


La requête externe est donc plus complète car nous rapatrions tous les résultats de la table de gauche même s'il n'y a pas de correspondance dans l'autre table dont nous nous servons pour effectuer la jointure.

Imaginez plus tard que dans votre site vous ayez des membres, des produits, et des commandes (les membres achètent des produits).
Imaginez la situation suivante :

Table Membre
id_membre pseudo
28 Romain
29 Fred
Table Produit
id_produit titre
557 tshirt
558 pull
Table Commande
id_commande id_membre id_produit date
1802 28 557 2014
1803 29 558 2015

Si un membre supprime son compte (suppression dans la table membre), la commande restera présente dans la table commande avec l'inscription NULL :

Table Commande
id_commande id_membre id_produit date
1802 NULL 557 2014
1803 29 558 2015

Lorsque vous voudrez afficher le contenu de cette table dans un backOffice, vous pourrez alors avec la jointure externe demander à considérer comme table de gauche (avec LEFT JOIN) la table commande afin qu'aucune donnée ne soit oubliée (et plus particulièrement les commandes n'ayant pas de correspondance dans la table membre, même si le membre n'existe plus).