Jointure

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 :

	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 :

	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
:




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.



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.

	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).

---

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 :

	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
:


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

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 :

	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
:


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

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 :

	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
:

---

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 :

	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
:



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) :

	INSERT INTO abonne (prenom) VALUES ('Alex');
	SELECT * FROM abonne;

---

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 :

	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
:



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 :

	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
:



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).

---