Compléments sur les jointures

Compléments sur les jointures

La jointure est un opérateur fondamental en ce qu’il permet de naviguer parmi les données. Cependant, il est nécessaire de bien comprendre le fonctionnement des jointures pour écrire des requêtes correctes et efficaces.

Nous allons nous intéresser :

1. à la pertinence d’utiliser une sous-requête plutôt qu’une jointure
2. aux valeurs dérivées dans une jointure
3. aux jointures généralisées
4. à l’interprétation du résultat d’une jointure

Sous-requête vs jointure

Certaines conditions utilisant une sous-requête (SELECT emboité) peuvent s’exprimer à l’aide d’une jointure

Examinons 2 cas :

1. Le cas des conditions d’association et de non association
2. Le cas de sous-requête et de clé étrangère multicomposants

Equivalence

La requête suivante, qui renvoie les commandes passées par des clients de Grenoble :

SELECT numero, dateCommande
FROM  Commandes
WHERE  numeroClient IN ( SELECT  numero FROM Clients WHERE ville='Grenoble' ) ;

peut s’écrire également sous la forme d’une jointure :

SELECT numero, dateCommande
FROM  Commandes C, Clients CL
WHERE  C.numeroClient = CL.numero
AND CL.ville='Grenoble' ;

Ou en utilisant la syntaxe JOIN :

SELECT numero, dateCommande
FROM  Commandes C
JOIN Clients CL ON C.numeroClient = CL.numero
WHERE CL.ville='Grenoble' ;

Equivalence

La requête suivante renvoie les commandes qui contiennent un produit dont la quantité est inférieure à la quantité commandée pour le produit numéro 1 dans la commande numéro 3 :

SELECT *
FROM Commandes
WHERE numero IN ( SELECT numeroCommande
                  FROM Details
                  WHERE numeroProduit = 1 AND quantite < (SELECT quantite
                                                          FROM Details
                                                          WHERE numeroProduit = 1
                                                          AND numeroCommande = 3) ) ;

Cette requête peut également s’écrire sous la forme de jointures uniquement :

SELECT DISTINCT C.*
FROM Commandes C, Details D1, Details D2
WHERE C.numero = D1.numeroCommande
AND D1.numeroProduit = 1
AND D2.numeroProduit = 1
AND D2.numeroCommande = 3
AND D1.quantite < D2.quantite ;

Non équivalence

Cependant, la requête suivante, qui renvoie les commandes qui ne contiennent pas de produit numéro 1 : (qui pourrait aussi s’écrire avec un NOT EXISTS)

SELECT *
FROM Commandes
WHERE numero NOT IN ( SELECT numeroCommande
                      FROM Details WHERE numeroProduit = 1 ) ;

 

ne peut absolument pas s’écrire sous la forme d’une jointure !

Avec une jointure, on ne peut pas exprimer une condition de non-association

Intuitivement, on pourrait penser que la requête suivante serait similaire à la précédente :

SELECT *
FROM Commandes C, Details D
WHERE C.numero = D.numeroCommande
AND D.numeroProduit != 1 ;

Mais cette requête ne renvoie pas les commandes qui ne contiennent pas le produit numéro 1. Elle renvoie les commandes qui contiennent un produit différent du produit numéro 1.

À retenir

Il faut se souvenir, qu’une jointure, fondée sur un couple de clés primaire/étrangère permet de matérialiser des associations entre lignes et non l’inexistence d’associations.

Comparaison des résultats

Pour s’en assurer, comparons les résultats des deux requêtes :

SELECT numero
FROM Commandes
WHERE numero NOT IN ( SELECT numeroCommande
                    FROM Details
                    WHERE numeroProduit = 1 ) ;

Résultat :

Commandes
numero
2

SELECT C.numero
FROM Commandes C, Details D
WHERE C.numero = D.numeroCommande
AND D.numeroProduit != 1 ;

Résultat :

Commandes
numero
1
1
2
2
3
3
3
3
4
5
5

Conclusion sur les sous-requêtes et les jointures

Conclusion

1. La jointure et la sous-requête permettent d’exprimer des conditions d’association entre lignes

2. En revanche, des conditions de non-association ne sont généralement exprimables que par des sous-requêtes, ainsi que par la forme NOT EXISTS

Calculs sur les jointures

Une jointure permet également d’effectuer des calculs sur des quantités extraites de plusieurs tables

Le raisonnement est simple : la jointure constitue des lignes fictives, dont la clause SELECT extrait des valeurs comme elle le ferait d’une ligne réelle issue d’une table

Par exemple, la requête suivant associe à chaque ligne de Details le montant à payer pour la commande numéro 1:

SELECT D.numeroCommande, D.numeroProduit, D.quantite, D.quantite * P.prix AS 'Montant'
FROM Details D, Produits P
WHERE D.numeroProduit = P.numero
AND D.numeroCommande = 1 ;

Résultat :

Commandes
numeroCommande	numeroProduit	quantite	Montant
1	1	3	75
1	2	1	60
1	3	1	120

La requête suivante, elle, établit le montant de la commande numéro 1 :

SELECT D.numeroCommande, SUM(D.quantite * P.prix) AS 'Montant total'
FROM Details D, Produits P
WHERE D.numeroProduit = P.numero
AND D.numeroCommande = 1 ;

Résultat :

Commandes
numeroCommande	Montant total
1	255

Autres conditions de jointures

Les conditions de jointures présentées jusqu’ici étaient fondées sur l’égalité des valeurs d’une clé étrangère avec celles d’un identifiant

Toutefois, toute comparaison peut servir à indiquer comment associer les lignes des tables concernées.

Autres conditions de jointures

L’exemple ci-dessous illustre une opération fréquente qui consiste à condenser de l’information de manière à la rendre plus lisible.

La table ci-dessous établit des intervalles successifs de valeurs de compte (minimum et maximum sur le compte) et leur attribut un code de classe de compte.

Classes_Comptes
minimumCompte	maximumCompte	codeCompte
10000	32000	A
5000	10000	B
2000	5000	C
1000	2000	D
500	1000	F
0	500	G
-500	0	U
-1000	-500	V
-2000	-1000	W
-5000	-2000	X
-10000	-5000	Y
-32000	-10000	Z

Comment associer à chaque client le code de son compte ?

La requête suivante permet de réaliser cette opération :

SELECT numero, nom, compte, codeCompte
FROM Clients, Classes_Comptes
WHERE compte >= minimumCompte
AND compte < maximumCompte ;

Résultat :

Classes de comptes des clients
numero	nom	compte	codeCompte
1	Lebreton	10000	A
2	Vasseur	1903	D
3	Da Costa	-1500	W
4	Rey	3251	C
5	Weiss	0	G
7	Lefevre	-1000	V
9	Barda	-2000	W
8	Jacques	-1750	W

Compréhension du résultat d’une jointure

La construction d’une requête qui utilise une ou plusieurs jointures peut s’avérer délicate.

Il est important de bien comprendre ce que représente le résultat d’une jointure.

La question est la suivante : sachant que toute ligne d’une table représente une entité du domaine d’application (un client, un achat, un détail, etc.), quelles entités les lignes d’une jointure représentent-elles ?

Par exemple, chaque ligne produite par l’évaluation de la requête :

SELECT C.numero, C.nom, C.ville
FROM Clients C, Commandes Co
WHERE C.numero = Co.numeroClient ;

représente-t-elle :

1. un client ?
2. un client qui a passé une ou plusieurs commandes ?
3. une commande ?

Réponse : des commandes

Règle générale

La règle relative à une jointure élémentaire, fondée sur l’égalité identifiant / clé étrangère est simple :

Règle

Soit :

• une table TA, d’identifiant IA, et des colonnes DA
• une table TB, d’identifiant IB, avec une colonne RA clé étrangère obligatoire et référençant TA, et des colonnes DB

TA (IA, DA)
TB (IB, RA*, DB)

Le résultat de la requête :

SELECT *
FROM TA, TB
WHERE TA.IA = TB.RA ;

contient autant de lignes qu’il y en a dans la table TB.

Autrement dit, chaque ligne du résultat d’une jointure représente une ligne de TB

En bref : le résultat d’une jointure représente des entités de la table contenant la clé étrangère

Exemple :

Soit la requête suivante :

SELECT Co.numero, Co.dateCommande, Co.numeroClient
FROM Commandes Co, Details D
WHERE Co.numero = D.numeroCommande ;

La clé étrangère de Details est numeroCommande, qui référence Commandes.

Le résultat de cette requête représente donc des détails.

Identifiant de la jointure

Ce que nous venons de discuter conduit à une autre règle : l’identifiant du résultat de la jointure

SELECT *
FROM TA, TB
WHERE TA.IA = TB.RA ;

est constitué des colonnes de l’identifiant primaire de TB (soit IB)

Si l’identifiant primaire de TB n’est pas repris dans la sélection des colonnes, le résultat n’a pas d’identifiant.

Exemple :

SELECT C.ville, P.nom
FROM Clients C, Commandes Co, Details D, Produits P
WHERE C.numero = Co.numeroClient
AND Co.numero = D.numeroCommande
AND D.numeroProduit = P.numero ;

Résultat :

Classes de comptes des clients
ville	nom
Meylan	T-shirt
Meylan	Pantalon
Meylan	Chaussures
Meylan	Casquette
Meylan	Sac à dos
Paris	T-shirt
Paris	Pantalon
Paris	Chaussures
Paris	Casquette
Paris	Sac à dos
Vizille	T-shirt
Vizille	Pantalon
Lille	T-shirt
Lille	Pantalon
Lille	Chaussures
Lille	T-shirt
Vizille	T-shirt

Conclusion

• La jointure est un opérateur fondamental en SQL, qui permet de naviguer parmi les données.
• Les jointures permettent d’effectuer des calculs sur des quantités extraites de plusieurs tables.
• Il est nécessaire de bien comprendre le fonctionnement des jointures pour écrire des requêtes correctes et efficaces.
• Les jointures permettent d’exprimer des conditions d’association entre lignes.
  • Les sous-requêtes permettent d’exprimer des conditions de non-association.
• Il est important de bien comprendre ce que représente le résultat d’une jointure.