Jointures et relations INNER, LEFT, RIGHT, UNION... Optimiser la recherche et l'interrogation de bases de donnée par Django (Oznog) Blais, Trucsweb (2 Août 2000)
[Mise à jour le 30 mars 2004] Les jointures permettent d'utiliser le modèle relationnel d'une base de donnée (ou même plusieurs bases) afin d'optimiser les interrogations. On peut toujours faire le travail à la main en appliquant par exemple plusieurs requêtes de suite dans une transaction mais sachez seulement que les performances des jointures sont sans égales. La jointure permet donc de combiner plusieurs colonnes ensemble, augmentant ainsi les champs de recherche. Mais elles peuvent toutes aussi bien en restreindre le nombre en forçant l'égalité entre deux colonnes pour ne retourner que les enregistrements concordants.
Pour en savoir d'avantage sur les modèles relationnels, voir "Tables relationnelles".
Équi-jointure, auto-jointure, jointure naturelle, jointures hétérogènes, union
Le mot clé « JOIN » permet d'effectuer une requête sur deux ou plusieurs tables peut importe la structure de votre base de données avec ou sans modèle relationnel. Il suffit de créer une jointure basée sur un critère de concordance des valeurs d'une colonne d'une table à une colonne d'une autre table. Une jointure permet donc de combiner les colonnes de plusieurs tables pendant les interrogations. Vous avez aussi l'auto-jointure d'une table sur elle-même, un système hiérarchique par exemple, un forum, un arbre généalogique ou toute arborescence. La jointure naturelle sur un seul nom de colonne, La jointure d'union entre deux tables identiques, avec la même structure. Plus rarement, une jointure peut aussi s'exprimer par inégalité ou par les différences entre deux tables ne comportant aucune valeur de liaison, On parle aussi de jointures hétérogènes, reliant plusieurs base de données ou encore de jointures externes, qui retourne carrémeent une colonne au complet sans concordance. Enfin, la jointure croisée et le produit cartésien de deux tables.
NOTE : Avant d'aller plus loin j'aimerais que vous portier attention à ces quelques règles de base.
1. Toujours préciser les colonnes retourner par la recherche, c'est vrai pour chacune de vos requêtes « SQL » mais ça l'est d'avantage avec une jointure. Non seulement le "SELECT * FROM" peut devenir très lent, particulièrement si votre table contient de longs textes, mais la confusion peut devenir ingérable voir impossible avec plusieurs tables qui contiennent des colonnes de mêmes noms. Profitez-en pour ajouter le nom de la table. Un bon petit truc pour optimiser vos requêtes est de placer les textes le plus à droite possible dans la requête.
SELECT table1.champ1, table1.champ2, table2.longTexte
2. TOUJOURS utiliser le mot clé « JOIN » bien qu'il ne soit pas indispensable. C'est une erreur fréquente que de préciser une jointure directement dans la clause « WHERE ». Bien qu'il n'y ait aucune différence au niveau de la performance et que le résultat soit généralement le même (pas toujours alors!) il est bon de bien organiser notre requête et de bien identifier les critères de recherche des combinaisons de tables.
3. Pour augmenter la lisibilité des requêtes, renommer vos tables, dit "surnommage", et ainsi simplifier les manipulations et combinaisons de plusieurs jointures; FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id ou FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.id.
4. Le « JOIN » n'est pas supporté par toutes les bases de données. Oracle à dû attendre la version 9 alors que MySQL la version 3.23.17 pour le « INNER JOIN »! et MySQL 4.0.11 pour le « UNION » et « CROSS JOIN » (mise à jour 2004). Rien ne vaut alors l'essai erreur. Pour leur part, PostGreSQL se limite au « LEFT », « RIGHT » alors que DB2, bien que très puissant, au « LEFT ».
Type de jointure « SQL »
D'un côté vous avez plusieurs types de jointure, « INNER », « LEFT », « RIGHT » etc. et ces subtiles différences, de l'autre, la combinaison de plusieurs jointures dans une même requête pouvant devenir rapidement très complexe.
Les exemples suivants seront basés sur ces 4 entités ou 4 tables et les valeurs disponibles ici!
1. « INNER JOIN » jointure par défaut qui compare deux tables et retourne tous les enregistrements comportant une concordance.
SELECT ...
FROM <table de gauche> INNER JOIN <table de droite>
ON <Condition de jointure>
sSQL = "select * from t_clients INNER JOIN t_prods ON t_clients.v_client_id = t_prods.v_client_id"
INNER JOIN SIMPLE
no ID Nom Produit
-----------------------------------------------------------
1 #3 Pierre table
2 #1 Luc table
3 #2 Claude radio
4 #3 Pierre ordinateur
5 #3 Pierre auto
6 #4 Julien maison
7 #3 Pierre table
8 #1 Luc ordinateur
9 #2 Claude auto
10 #2 Claude table
11 #3 Pierre maison
2. « LEFT [ OUTER ] JOIN » en plus des concordances entre deux tables, retourne tous les items de la colonne de gauche peu importe les concordances. Par exemple la liste des clients en relation avec la facturation. Les clients sans facture seront aussi affichés.
SELECT ...
FROM <table de gauche> LEFT JOIN <table de droite>
ON <Condition de jointure>
sSQL = "select * from t_clients LEFT JOIN t_prods ON t_clients.v_client_id = t_prods.v_client_id"
LEFT JOIN SIMPLE
no ID Nom Produit
-----------------------------------------------------------
1 #1 Luc table
2 #1 Luc ordinateur
3 #2 Claude radio
4 #2 Claude auto
5 #2 Claude table
6 #3 Pierre table
7 #3 Pierre ordinateur
8 #3 Pierre auto
9 #3 Pierre table
10 #3 Pierre maison
11 #4 Julien maison
' Ajoute de la table de gauche sans relation avec la table de droite.
12 #- Carl - (v_client_id n'existe pas!)
13 #- André - (v_client_id n'existe pas!)
Notez l'erreur de l'ID causée par l'astérisque (*). En ne précisant pas les colonnes retournées, par défaut, l'ID retournée est celle de la table de droite qui n'a aucune concordance avec Carl ou André. Pour éviter ce problème, indiquer d'utiliser la colonne de gauche (t_clients) :
sSQL = "select t_clients.v_client_id, t_clients.v_client_nom, t_prods.v_prod_nom from t_clients LEFT JOIN t_prods ON t_clients.v_client_id = t_prods.v_client_id"
LEFT JOIN SIMPLE AJUSTÉ
no ID Nom Produit
-----------------------------------------------------------
1 #1 Luc table
2 #1 Luc ordinateur
3 #2 Claude radio
4 #2 Claude auto
5 #2 Claude table
6 #3 Pierre table
7 #3 Pierre ordinateur
8 #3 Pierre auto
9 #3 Pierre table
10 #3 Pierre maison
11 #4 Julien maison
12 #5 Carl -
13 #6 André -
3. « RIGHT [ OUTER ] JOIN » en plus des concordances entre deux tables, retourne tout les item de la colonne de droite peut importe les concordances.
SELECT ...
FROM <table de gauche> RIGHT JOIN <table de droite>
ON <Condition de jointure>
sSQL = "select * from t_clients RIGHT JOIN t_prods ON t_clients.v_client_id = t_prods.v_client_id"
' Avec surnommage
sSQL = "select * from t_clients t1 RIGHT JOIN t_prods t2 ON t1.v_client_id = t2.v_client_id"
RIGHT JOIN SIMPLE
no ID Nom Produit
-----------------------------------------------------------
1 #3 Pierre table
2 #1 Luc table
3 #2 Claude radio
4 #3 Pierre ordinateur
5 #3 Pierre auto
6 #4 Julien maison
7 #3 Pierre table
8 #1 Luc ordinateur
9 #2 Claude auto
10 #2 Claude table
11 #3 Pierre maison
12 #0 - moto
4. « UNION et UNION ALL » permet enfin de combiner deux tables identiques, par exemple une table active avec table archivée.
SELECT ...
FROM <table de gauche> UNION [ALL]
SELECT ...
FROM <table de droite>
sSQL = "select * from t_clients UNION SELECT * from t_prods"
Autres exemples
Exemple avancé #1
RELATIONS 3 TABLES
sSQL = "select t1.v_client_id, t1.v_client_nom, t2.v_prod_nom, t3.v_coul_nom from (t_clients t1 INNER JOIN t_prods t2 ON t1.v_client_id = t2.v_client_id) LEFT JOIN t_couls t3 ON t2.v_coul_id = t3.v_coul_id"
no ID Nom Produit Couleur
-----------------------------------------------------------
1 #3 Pierre table Bleu
2 #1 Luc table Bleu
3 #2 Claude radio Bleu
4 #3 Pierre ordinateur Rouge
5 #3 Pierre auto Rouge
6 #4 Julien maison Vert
7 #3 Pierre table Rouge
8 #1 Luc ordinateur Vert
9 #2 Claude auto Bleu
10 #2 Claude table Vert
11 #3 Pierre maison Rouge
Exemple avancé #2
RELATIONS 3 TABLES MIEUX (liste de tous les produits vendus , avec son client et sa couleur).
sSQL = "select t1.v_client_id, t1.v_client_nom, t2.v_prod_nom, t3.v_coul_nom from (t_clients t1 INNER JOIN t_prods t2 ON t1.v_client_id = t2.v_client_id) INNER JOIN t_couls t3 ON t2.v_coul_id = t3.v_coul_id ORDER by t1.v_client_nom, t2.v_prod_nom"
no ID Nom Produit Couleur
-----------------------------------------------------------
1 #2 Claude auto Bleu
2 #2 Claude radio Bleu
3 #2 Claude table Vert
4 #4 Julien maison Vert
5 #1 Luc ordinateur Vert
6 #1 Luc table Bleu
7 #3 Pierre auto Rouge
8 #3 Pierre maison Rouge
9 #3 Pierre ordinateur Rouge
10 #3 Pierre table Rouge
11 #3 Pierre table Bleu
Exemple avancé #3
RELATIONS 4 TABLES
sSQL = "select t1.v_client_id, t1.v_client_nom, t2.v_prod_nom, t3.v_coul_nom, t4.v_piece_nom from ((t_clients t1 INNER JOIN t_prods t2 ON t1.v_client_id = t2.v_client_id) LEFT JOIN t_couls t3 ON t2.v_coul_id = t3.v_coul_id) INNER JOIN t_pieces t4 ON t2.v_prod_id = t4.v_prod_id"
no ID Nom Produit Couleur Pièce
-----------------------------------------------------------
1 #3 Pierre ordinateur Rouge écran
2 #3 Pierre ordinateur Rouge imprimante
3 #3 Pierre ordinateur Rouge clavier
4 #3 Pierre ordinateur Rouge souris
5 #4 Julien maison Vert garage
6 #4 Julien maison Vert Sale à manger
7 #4 Julien maison Vert Chambre
8 #3 Pierre auto Rouge pneu
9 #3 Pierre auto Rouge porte
10 #3 Pierre auto Rouge frein
11 #3 Pierre auto Rouge lumière
| 
