Trucsweb.com

ASP

ActiveX® Data Objects (ADO)

RDFFav

Jointures et relations INNER, LEFT, RIGHT, UNION... Optimiser la recherche et l’interrogation de bases de donnée

[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 somodèle relationnel Équi-jointure auto-jointure naturelle hétérogènes surnommage AS enregistrements LEFT OUTER concordances colonne RIGHT inner join bases de données sql requêtes access combiner SGBD DB2 oracle MySQL recherche FROM UNION interrogation

[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ément 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 portiez 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!
Modèle logique de données relationnelles (MLDR)

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

Django (Oznog) Blais
Dernière mise à jour :

Commentaires

  • Bonjour! je voudrais recevoir des cours et vous publications depuis mon compte gmail.
    64x64
    pierrot

    2015-12-06 11:2:18

    • Merci pour le tuto ! Excellent.
      64x64
      thouemma

      2016-01-22 22:34:14

      • Bonjour, Votre article est vraiment très intéressant et bien expliqué. Je voudrais juste revenir sur un point: êtes-vous certain que l'on peut faire un union entre les tables t_clients et t_prods qui ont une structure différente ("select * from t_clients union select * from t_prods")? Si oui, pouvez-vous le préciser dans le paragraphe concerné pour d'autres personnes ? Merci, Vicky.
        64x64
        Vicky

        2016-03-02 10:47:7
        • Salut, Ça fait déjà 16 ans que j'ai écrit ce tutoriel, qui est davantage un rappel qu'un véritable tutoriel. Et toujours d'actualité, une évolution moins rapide que le CSS! L'exemple ne peut pas fonctionner dans la mesure ou il compare chaque champ (*) sans distinction. Mais c'est tout à fait possible sans problème si on procède champ par champ. Ce n'est pas la table qui doit être comparable, mais chaque champ indépendamment. L'important est que chaque champ énuméré doit être du même type que son homologue de union. // Ici les champs de type string « v_nom_client » et « v_nom_produit » // Et les champs de type number « v_client_solde » et « v_nom_produit » sSQL = "select v_nom_client, v_client_solde from t_clients union select v_nom_produit, v_produit_prix from t_prods" // Bon, même nombre, même type bBooleenne, dDate, nNombre, sTexte union bBooleenne, dDate, nNombre, sTexte // Mauvais, le premier et le deuxième champ (booléenne et date) ne sont pas compatible. bBooleenne, dDate, nNombre, sTexte union dDate, bBooleenne, nNombre, sTexte // Mauvais, le type « date » n'est pas compatible avec son homologue de type « nombre ». // Et il manque un champ, 4 versus 3... bBooleenne, dDate, nNombre, sTexte union bBooleenne, nNombre, sTexte Il va sans dire que ce n'est pas utile de récupérer les informations d'un client en même temps qu'un produit. C'est un exemple générique qu'on utilise toujours depuis Mathusalem! Il n'y a aucun lien évident entre les deux. Mais pour une recherche sur tout le site, on peut faire une recherche dans les produits et dans les clients en même temps à l'aide cette méthode. Ce que je fais souvent c'est une première recherche sur les index avec union, et une recherche avancée sans union, sur le texte complet par exemple. NOTE : Tu peux même ajouter des champs bidon, pour ajuster. On l'utilise souvent pour donner un nom à la table et l'identifier lors du résultat : select... v_table_nom = 'Clients' from t_clients union select... v_table_nom = 'Produits' from t_produits... De cette façon on peut trier le résultat par la table « ORDER BY v_table_nom ». Par exemple en ASP : if oRS("v_table_nom") = "Clients" then ' Résultat de la Table t_clients else ' Résultat de la Table t_produits end if
          64x64
          oznog
          http://www.trucsweb.com
          2016-03-02 13:40:56


        • Bonsoir, Si nous nous référons aux données de vos tables, pour le inner, ne devrions nous pas avoir que 6 lignes (ou colonne manquante dans table des produits)? no ID Nom Produit ----------------------------------------------------------- 1 #1 Luc table 2 #2 Claude radio 3 #3 Pierre ordinateur 4 #4 Julien auto 5 #5 Carl maison 6 #6 André table
          64x64
          inner

          2016-04-20 19:30:45
          • Bonsoir, En fait la table « t_prods » est un prétexte pour l'exemple. Ce n'est pas réellement une table de produits. Un véritable système devrait avoir une table de produit digne de ce nom. Mais comme indiqué plus haut dans le shéma, ce n'est pas seulement les produits, mais plutôt les produits d'un « panier » par numéro de client... Tu as un test ici http://www.trucsweb.com/SQL/joins.asp La requête SQL en haut suivit du résultat réel en bas.
            64x64
            oznog
            http://www.trucsweb.com
            2016-04-20 19:57:53


          9/10 sur 1 revues.
                 Visites : 53016 - Pages vues : 154265
          X

          Trucsweb.com Connexion

          Connexion

          X

          Trucsweb.com Mot de passe perdu

          Connexion

          X

          Trucsweb.com Conditions générales

          Conditions

          Responsabilité

          La responsabilité des Trucsweb.com ne pourra être engagée en cas de faits indépendants de sa volonté. Les informations mises à disposition sur ce site le sont uniquement à titre purement informatif et ne sauraient constituer en aucun cas un conseil ou une recommandation de quelque nature que ce soit.

          Aucun contrôle n'est exercé sur les références et ressources externes, l'utilisateur reconnaît que les Trucsweb.com n'assume aucune responsabilité relative à la mise à disposition de ces ressources, et ne peut être tenue responsable quant à leur contenu.

          Droit applicable et juridiction compétente

          Les règles en matière de droit, applicables aux contenus et aux transmissions de données sur et autour du site, sont déterminées par la loi canadienne. En cas de litige, n'ayant pu faire l'objet d'un accord à l'amiable, seuls les tribunaux canadien sont compétents.

          X

          Trucsweb.com Trucsweb

          X

          Trucsweb.com Glossaire

          X

          Trucsweb.com Trucsweb

          X

          Trucsweb.com Trucsweb

          Conditions

          Aucun message!

          Merci.

          X
          Aucun message!
          X

          Trucsweb.com Créer un compte

          Créer un compte

          .
          @