SQL1b.LID - Les jointures

1. Association hiérarchique parent-enfant

Soit les tables Client et commandes en association hiérarchique :
Table parent : Client :

Table enfant : Commande :

Comment obtenir la réponse à la question :

• Quelle est la liste des numéros de commandes que le client SAI a passé ?

Cette liste correspond à la liste des commandes dont le numéro de client est égal à celui du client qui porte le nom SAI.
La réponse est la liste des commandes 1 et 3

Cette association est dite hiérarchique car :

• pour une ligne de la table enfant (commande) on trouve une et une seule ligne de la table parent (Client).
• et pour une ligne de la table parent (Client) on trouve une à plusieurs lignes de la table enfant (commande).

2. Association non hiérarchique

Soit les tables Client et commandes en association non hiérarchique :
Table Commande

Table Contenir

Table Article

Comment obtenir la réponse à la question :

• Quelle sont les prix des articles de la commande du 02/01/04 ?

Pour cela, il faut observer le contenu de

• Commande pour obtenir le numéro de la commande (2), puis
• Contenir, qui associe la commande aux articles qu'elle contient (la cde 2 contient les articles de ref. A1 et A2) et
• Article pour obtenir les designations des articles trouvés (A1=>ancre et A2=>manille).

La réponse est donc "ancre" et "manille"

Pour y parvenir, nous avons parcourus les liens entre les tables

Cette association est dite non hiérarchique car :

• pour 1 ligne de la table Commande on trouve une à plusieurs lignes de la table à droite (Article).
• et pour 1 ligne de la table Article on trouve une à plusieurs lignes de la table à gauche (Commande).

La table Contenir, représente l'association entre Commande et Article.

Elle possède donc deux "clés étrangères" qui sont chacune en référence, l'une en référence au numéro de la Commande et l'autre en référence au code de l'Article, tous deux clés primaires de leur table respective.

La quantité dans la table contenir représente la quantité d'article commandée par la commande.

On réalise simplement la jointure en SQL en disant que la clé primaire d'une table référencée est égale à la clé étrangère de l'autre table.

1. Exemple 1

Quelle est la liste des numéros de commandes que le client SAI a passé ?
On reprend les tables précédentes et on écrit :

SELECT cdeNum 
  FROM Commande, Client 
  WHERE Commande.noCli=Client.noCli -- = jointure entre Commande et Client
    AND nomCli="SAI";

Explications :

• nomCli="SAI" nous permet de savoir que le client "SAI" a pour numéro la valeur 15. Bon.
• Avec ce numéro, on va chercher toutes les ligne de commande qui ont ce numéro : Commande.noCli=Client.noCli
• Enfin, on récupère uniquement le numéro des commandes dans : SELECT cdeNum

On obtient bien 1 et 3. CQFD.

2. Exemple 2

Quelle est la description des articles contenus dans la commande du 02/01/04 ?
Idem et on écrit :

SELECT descArt 
  FROM Commande, Contenir, Article 
 WHERE Commande.noCde = Contenir.noCde -- = jointure 1, entre Commande et Contenir
   AND Contenir.refArt = Article.refArt -- = jointure 2, entre Contenir et Article
   AND dateCde = #02/01/2004#     -- restriction normale
;

Explications :

• restriction : dateCde = #02/01/2004# permet de retrouver le numéro de la commande (2).
• jointure 1 : Commande.noCde = Contenir.noCde permet de retrouver les lignes de Contenir et,
• jointure 2 : Contenir.refArt = Article.refArt permet de retrouver la description de chaque article

On obtient bien "ancre" et "manille". CQFD itou. Il y a deux jointures !

3. Combien faire de jointures ?

Simple, il doit y avoir autant de jointures que de virgules dans la liste de table de FROM !

4. Combien de données sont récupèrées ?

Si on va dans le sens clé étrangère => clé primaire, une jointure produit une ligne (un enregistrement)
Si on va dans le sens clé primaire => clé étrangère, une jointure produit une liste (plisieurs enregistrements)

Quelle est la liste des articles dans la commande n°1 ? (on a besoin des tables contenir et article)
Quelle est la liste des articles commandés le 01/01/2015 ? (on a besoin des tables commande, contenir et article)

Parfois, il est nécessaire de renommer des champs, on utilise alors le mot clé AS derrière celui-ci : un comptage, une somme, ou juste comme ça.
On peut faire la même chose avec les tables et c'est parfois très utile :
Exemple : quels sont les codes et descriptions des articles achetés en même temps que la Manille (mais qui ne sont pas Manille) pour construire la liste du type : "Les autres clients ont aussi acheté ..." ?
On va chercher les articles non Manille qui sont contenus dans les mêmes commandes que les commandes qui contiennnent l'article Manille :

SELECT DISTINCT refArt, descArt 
  FROM Article **A1**, Contenir **C1**, Contenir **C2**, Article **A2**
 WHERE 
   -- 2 jointures, entre les "Contenir" et les "Article"
       A1.refArt = C1.refArt 
   AND c2.RefArt = A2.refArt 
   -- la 3ème jointure, entre les "Contenir"
   AND C1.cdeNum = C2.cdeNum 
   -- les restrictions
   AND A2.descArt="Manille"        -- rechercher les commandes C2 contenant "Manille"
   AND A1.descArt<>"Manille" -- éliminer les commandes C1 contenant "Manille"
 
  AND descArt<>"Manille";

Un petit DISTINCT, pour supprimer les éventuels doublons de la liste.
Vu ?
C'est surtout pratique lorsqu'on a des requêtes imbriquées dans FROM - voir le chapitre concerné, plus loin -.

On obtient un produit cartésien
Le moteur de requête créée une liste où chaque enregistrement de la table 1 est associé avec chaque enregistrement de la table 2.

M'enfin, c'est pas c'qu'on veut!

Il suffit alors de restreindre le résultat aux seules lignes dont la clé étrangère d'une table correspond à la clé primaire de l'autre et le tour est joué.


Exemple sans et avec jointure. C'est édifiant!

• quelle est la liste des numéros de commandes que le client SAI a passé ?

Les requêtes

Produit cartésien

SELECT * 
FROM Client, Commande

Jointure

SELECT * 
FROM Client, Commande 
WHERE id=idClient

Le résultat obtenu

Table résultat du produit cartésien :

Pas bon : il y a des lignes incohérentes : un client avec la mauvaise commande

Table résultat du produit cartésien :

Là c'est bon, les lignes incohérentes sont supprimées du produit cartésien. Seules les lignes où l'identifiant du client (id) est égal à l'identifiant du client de la commande (idClient) sont justes !!!

JOIN est un opérateur qui remplace la jointure "standard".
on peut l'utiliser très facilement, mais il faut penser en mode ensembliste.

Utilisation

Une requête avec JOIN est assez simple, au début :

SELECT * FROM tableA JOIN tableB ON tableA.champJoint = tableB.champJoint WHERE ...

En fait, les jointures ne sont plus dans le WHERE mais immédiatement après chaque table, dans la clause JOIN ... ON (ici) ...
Ceci permet de distinguer les restrictions normales de celles qui sont des jointures.

La Puissance de JOIN

Elle réside dans le choix de conserver différents types de jointures (et là, on parle d'ensembles) :

• Que ceux qui correspondent (l'intersection entre les deux ensembles = jointure normale) : JOIN normal ou INNER JOIN ; intersection de A et B
• Ceux qui correspondent et ceux de la première table (celle de gauche) qui n'ont pas de correspondance : LEFT JOIN ; on dit aussi la différence A-B
• Ceux qui correspondent et ceux de la seconde table (celle de droite) qui n'ont pas de correspondance : RIGHT JOIN ; L'autre différence : B-A
• Tous les enregistrements : FULL JOIN ; on utilisera plutôt l'union des tables : SELECT FROM A UNION SELECT FROM B, Attention, les champs projetés doivent êtres identiques !!

Rassurez vous, seuls JOIN et Left JOIN sont vraiment utilisés

Exemples

1. Premier exemple

Quelle est la liste des numéros de commandes que le client SAI a passé ?
L'ancienne requête était :

SELECT cdeNum <li>FROM Commande, Client 
 WHERE Commande.noCli=Client.noCli -- = jointure entre Commande et Client
   AND nomCli="SAI";

La nouvelle sera :

SELECT cdeNum 
  FROM Commande 
       JOIN Client ON Commande.noCli = Client.noCli -- = jointure entre Commande et Client
  WHERE AND nomCli="SAI";

2. Second exemple

Quelle est la description des articles contenus dans la commande du 02/01/04 ?
L'ancienne requête était :

SELECT descArt <li>FROM Commande, Contenir, Article 
 WHERE Commande.noCde = Contenir.noCde // = jointure 1, entre Commande et Contenir
   AND Contenir.refArt = Article.refArt // = jointure 2, entre Contenir et Article
   AND dateCde=#02/01/2004#;

La nouvelle sera :

					<ul>
SELECT descArt 
 FROM Commande
 JOIN Contenir ON Commande.noCde = Contenir.noCde // jointure 1
 JOIN Article  ON Contenir.refArt = Article.refArt // jointure 2
WHERE  dateCde = #02/01/2004#;

2. Troisième exemple

Si on a des voitures qui sont pilotées 1 pilote et un copilote, quelles sont les voitures utilisées par chacun ? Soit les tables ci-dessous
Liste des voitures, avec pilote et copi éventuel :

Et la liste des pilotes :

On veut obtenir la liste des copilotes des voitures suivante :

Le nombre de voitures pilotées ou copilotées par chaque personnes

On va écrire la requête géniale :

SELECT modele, nom FROM voiture **LEFT JOIN** personne ON idCopi=persCode

Cette requête va chercher toutes les voitures et associer seulement les noms des copilotes trouvés
Toutes les voitures apparaîtront dans la liste
Mais toutes les voiture n'auront pas de nom de copilote.

Les jointures, c'est comme le manche des casseroles, quand on ne sait pas s'en servir, on ne peut pas faire la cuisine.