Les jointures

Il existe deux manières d’écrire en SQL la jointure entre plusieurs tables de la base de données. La forme historique et la plus utilisée est la syntaxe de jointure relationnelle (basé sur la norme ANSI SQL/86) ; la deuxième syntaxe est une jointure plus verbale qui respecte la norme AINSI SQL/92 et qui a été introduite dans Oracle 9i.

en savoir

L’opérateur JOIN ON

L’opérateur « JOIN ON » effectue la jointure entre deux tables en se servant des conditions spécifiées respectant la syntaxe suivante :

SELECT [ALL | DISTINCT]{*,[EXPRESSION1 [AS] ALIAS1[,...]}

FROM NOM_TABLE1[ JOIN NOM_TABLE2 ON

     (NOM_TABLE1.NOM_COLONNE = NOM_TABLE2.NOM_COLONNE)] ;

SQL> SELECT SOCIETE FOURNISSEUR, NOM_CATEGORIE, SUM( UNITES_STOCK) UC

  2  FROM CATEGORIES C JOIN PRODUITS P      

  3       ON ( C.CODE_CATEGORIE = P.CODE_CATEGORIE)

  4       JOIN FOURNISSEURS F ON ( P.NO_FOURNISSEUR = F.NO_FOURNISSEUR)

  5  WHERE UNITES_STOCK <> 0 GROUP BY NOM_CATEGORIE, SOCIETE;

 

FOURNISSEUR                       NOM_CATEGORIE        UC

--------------------------------- ------------------ ----

Tokyo Traders                     Conserves            40

Tokyo Traders                     Desserts             20

Pavlova, Ltd.                     Desserts             49

Specialty Biscuits, Ltd.          Produits secs        75

Plutzer Lebensmittelgroßmärkte AG Condiments           32

Plutzer Lebensmittelgroßmärkte AG Boissons            125

Formaggi Fortini s.r.l.           Viande en conserve   50

Karkki Oy                         Boissons             57

Escargots Nouveaux                Condiments           40

Nouvelle-Orléans Cajun Delights   Produits secs        20

Cooperativa de Quesos 'Las Cabras Produits secs        40

...

SQL> SELECT SOCIETE FOURNISSEUR, NOM_CATEGORIE, SUM( UNITES_STOCK) UC

  2  FROM CATEGORIES C,PRODUITS P,FOURNISSEURS F

  3  WHERE C.CODE_CATEGORIE = P.CODE_CATEGORIE

  4    AND P.NO_FOURNISSEUR = F.NO_FOURNISSEUR

  5    AND UNITES_STOCK <> 0 GROUP BY NOM_CATEGORIE, SOCIETE;

La requête précédente affiche les fournisseurs, les catégories des produits et la somme des unités en stock pour les produits qui ont un stock ; la deuxième requête est la traduction dans l’ancienne syntaxe.

SQL> SELECT P.NOM_PRODUIT, SUM( D.QUANTITE) QUANTITE,

  2         AVG(D.PRIX_UNITAIRE) PRIX_UNITAIRE

  3  FROM PRODUITS P JOIN DETAILS_COMMANDES D

  4       ON P.REF_PRODUIT = D.REF_PRODUIT

  5  WHERE P.CODE_CATEGORIE = 1 GROUP BY P.NOM_PRODUIT;

 

NOM_PRODUIT                    QUANTITE PRIX_UNITAIRE

---------------------------- ---------- -------------

Beer                             446119         62,76

Coffee                           413199          47,4

Chai                             416105          72,6

Ipoh Coffee                      450562         88,32

Tea                              449445         64,56

Sasquatch Ale                    451057         37,08

Chartreuse verte                 435834          76,8

Côte de Blaye                    465860         64,92

Lakkalikööri                     396703         63,72

Chang                            455970         51,48

Guaraná Fantástica               428101         79,08

Rhönbräu Klosterbier             410993         42,48

Steeleye Stout                   422337         41,52

Laughing Lumberjack Lager        432044         78,48

Outback Lager                    489108         47,76

Green Tea                        448015          42,6

Dans l’exemple précèdent, la requête joint les tables DETAILS_COMMANDE et PRODUITS à l’aide de l’opérateur « JOIN ON » affichant uniquement les produits pour la catégorie 1 et la somme des quantités vendues et la moyenne du prix unitaire.

SQL> SELECT C.SOCIETE CLIENT, F.SOCIETE FOURNISSEUR, C.VILLE

  2  FROM CLIENTS C, FOURNISSEURS F ON C.VILLE = F.VILLE;

 

CLIENT                 FOURNISSEUR                       VILLE

---------------------- --------------------------------- ---------

Consolidated Holdings  Exotic Liquids                    London

Eastern Connection     Exotic Liquids                    London

Familia Arquibaldo     Refrescos Americanas LTDA         São Paulo

Lehmanns Marktstand    Plutzer Lebensmittelgroßmärkte AG Frankfurt

Mère Paillarde         Ma Maison                         Montréal

North/South            Exotic Liquids                    London

Paris spécialités      Aux joyeux ecclésiastiques        Paris

Queen Cozinha          Refrescos Americanas LTDA         São Paulo

Seven Seas Imports     Exotic Liquids                    London

Spécialités du monde   Aux joyeux ecclésiastiques        Paris

Tradição Hipermercados Refrescos Americanas LTDA         São Paulo

Alfreds Futterkiste    Heli Süßwaren GmbH  Co. KG        Berlin

Around the Horn        Exotic Liquids                    London

B's Beverages          Exotic Liquids                    London

Comércio Mineiro       Refrescos Americanas LTDA         São Paulo

L’opérateur « JOIN ON » effectue la jointure entre deux tables en se servant des conditions spécifiées respectant la syntaxe suivante :

SELECT [ALL | DISTINCT]{*,[EXPRESSION1 [AS] ALIAS1[,...]}

FROM NOM_TABLE1[ JOIN NOM_TABLE2 ON 

    (NOM_TABLE1.NOM_COLONNE = NOM_TABLE2.NOM_COLONNE)

     [{AND | OR} EXPRESSION ]] ;

SQL> SELECT NOM||' '||PRENOM "Vendeur", SOCIETE "Client",

  2  TO_CHAR( DATE_COMMANDE,'FMDD Mon YYYY') "Commande", PORT "Port"

  3  FROM CLIENTS A JOIN COMMANDES B ON A.CODE_CLIENT = B.CODE_CLIENT

  4     JOIN EMPLOYES C ON B.NO_EMPLOYE = C.NO_EMPLOYE

  5     AND DATE_COMMANDE > '29/06/2011' AND PORT BETWEEN 70 AND 80;

 

Vendeur            Client                       Commande       Port

------------------ ---------------------------- ------------- -----

Marielle Michel    QUICK-Stop                   30 Juin 2011   74.8

Thimoleon Georges  Blauer See Delikatessen      30 Juin 2011   79.1

Cleret Doris       Océano Atlántico Ltda.       30 Juin 2011   79.8

Capharsie Gérard   GROSELLA-Restaurante         30 Juin 2011   75.6

Vous avez pu remarquer que les parenthèses qui suivent le mot clé « ON » sont facultatives, mais elles permettent une meilleure lisibilité du code si vous prenez soin de les positionner.

SQL> SELECT DC.REF_PRODUIT, COUNT( DISTINCT NO_COMMANDE) COM

  2  FROM DETAILS_COMMANDES DC JOIN PRODUITS PR

  3       ON ( DC.PRIX_UNITAIRE BETWEEN PR.PRIX_UNITAIRE*0.95

  4            AND PR.PRIX_UNITAIRE*1.05 AND PR.REF_PRODUIT = 57 )

  5       JOIN COMMANDES CO    

  6       ON  ( CO.NO_COMMANDE = DC.NO_COMMANDE AND CO.ANNEE   = 2011 )

  7       JOIN CLIENTS CL      

  8       ON  ( CO.CODE_CLIENT = CL.CODE_CLIENT AND CL.PAYS LIKE 'A%')

  9       GROUP BY DC.REF_PRODUIT;

 

REF_PRODUIT        COM

----------- ----------

         20        276

         62        259

         96        292

        111        228

        120        274

L’opérateur JOIN USING

L’opérateur « JOIN USING » effectue la jointure entre deux tables en se servant des colonnes homonymes dans les deux tables spécifiées respectant la syntaxe suivante :

SELECT [ALL | DISTINCT]{*,[EXPRESSION1 [AS] ALIAS1[,...]}

FROM NOM_TABLE1[ JOIN NOM_TABLE2 USING (NOM_COLONNE1[,...])] ;

SQL> SELECT C.SOCIETE CLIENT, F.SOCIETE FOURNISSEUR, VILLE

  2  FROM CLIENTS C JOIN FOURNISSEURS F USING(VILLE);

 

CLIENT                 FOURNISSEUR                       VILLE

---------------------- --------------------------------- ----------

Consolidated Holdings  Exotic Liquids                    London

Eastern Connection     Exotic Liquids                    London

Familia Arquibaldo     Refrescos Americanas LTDA         São Paulo

Lehmanns Marktstand    Plutzer Lebensmittelgroßmärkte AG Frankfurt

Mère Paillarde         Ma Maison                         Montréal

North/South            Exotic Liquids                    London

Paris spécialités      Aux joyeux ecclésiastiques        Paris

Queen Cozinha          Refrescos Americanas LTDA         São Paulo

Seven Seas Imports     Exotic Liquids                    London

Spécialités du monde   Aux joyeux ecclésiastiques        Paris

Tradição Hipermercados Refrescos Americanas LTDA         São Paulo

Alfreds Futterkiste    Heli Süßwaren GmbH  Co. KG        Berlin

Around the Horn        Exotic Liquids                    London

B's Beverages          Exotic Liquids                    London

Comércio Mineiro       Refrescos Americanas LTDA         São Paulo

La requête précédente affiche les clients qui sont localisés dans une ville d’un fournisseur.

SQL> SELECT PRODUITS.REF_PRODUIT FROM DETAILS_COMMANDES

  2  JOIN PRODUITS USING ( REF_PRODUIT );

SELECT PRODUITS.REF_PRODUIT

       *

ERREUR à la ligne 1 :

ORA-25154: la partie colonne de la clause USING ne peut pas avoir de qualificatif

La requête suivante affiche la somme des ventes par pays du produit 57 dans l’année 2011 uniquement pour les pays qui commencent par une lettre de A÷C. Il convient de remarquer l’utilisation des filtres à l’aide de la clause « WHERE ».

SQL> SELECT CL.PAYS, SUM( DC.PRIX_UNITAIRE*DC.QUANTITE) CA

  2  FROM DETAILS_COMMANDES DC JOIN PRODUITS  PR USING ( REF_PRODUIT )

  3       JOIN COMMANDES CO USING ( NO_COMMANDE )

  4       JOIN CLIENTS   CL USING ( CODE_CLIENT )

  5  WHERE REF_PRODUIT=57 AND CO.ANNEE=2011 AND CL.PAYS BETWEEN 'A' AND 'D'

  6  GROUP BY CL.PAYS;

 

PAYS                    CA

--------------- ----------

Allemagne       1199077,44

Argentine         288452,4

Autriche         196615,92

Canada            387313,2

Brésil          1030623,84

Belgique         240843,12

La requête suivante affiche la somme des ventes par pays, par ville et par année pour les produits vendus dans la même ville ou ils ont été produits. L’utilisation du champ homonyme VILLE dans l’opérateur « USING » est possible car seule la table CLIENTS et la table FOURNISSEURS possèdent ce champ. Sinon il est impératif d’utiliser l’opérateur « ON » en préfixant le nom du champ avec le nom de la table ou son alias.

SQL> SELECT CL.PAYS, VILLE, CO.ANNEE, SUM( DC.PRIX_UNITAIRE*DC.QUANTITE) CA

  2  FROM DETAILS_COMMANDES DC JOIN PRODUITS  PR USING ( REF_PRODUIT )

  3       JOIN COMMANDES CO USING ( NO_COMMANDE )

  4       JOIN CLIENTS   CL USING ( CODE_CLIENT )

  5       JOIN FOURNISSEURS FO USING ( NO_FOURNISSEUR, VILLE)

  6  GROUP BY CL.PAYS, VILLE, CO.ANNEE;

 

PAYS            VILLE               ANNEE         CA

--------------- -------------- ---------- ----------

France          Paris                2011 1200918,72

Royaume-Uni     London               2011 2009131,32

Allemagne       Berlin               2010  613423,92

Royaume-Uni     London               2010 3062000,28

Brésil          São Paulo            2010 1005976,68

Allemagne       Frankfurt a.M.       2011  753836,52

Canada          Montréal             2011  596944,32

France          Paris                2010  1884362,4

Canada          Montréal             2010     508980

Allemagne       Frankfurt a.M.       2010  801738,12

Allemagne       Berlin               2011     317073

Brésil          São Paulo            2011  525328,44

L’opérateur NATURAL JOIN

L’opérateur « NATURAL JOIN » effectue la jointure entre deux tables en se servant de toutes les colonnes des deux tables qui portent le même nom avec la syntaxe suivante :

SELECT [ALL | DISTINCT]{*,[EXPRESSION1 [AS] ALIAS1[,...]}

FROM NOM_TABLE1 NATURAL JOIN NOM_TABLE2 ;

SQL> SELECT PAYS, ANNEE, SUM( PRIX_UNITAIRE*QUANTITE) CA

  2  FROM CLIENTS NATURAL JOIN COMMANDES NATURAL JOIN DETAILS_COMMANDES

  3  GROUP BY PAYS, ANNEE;

 

PAYS                 ANNEE         CA

--------------- ---------- ----------

États-Unis            2010  295589486

Allemagne             2010  248741421

Canada                2010 60489582,5

Belgique              2011 30031948,4

Venezuela             2011 65413824,7

Belgique              2010 54458225,8

Autriche              2010 43122999,8

Danemark              2010 41867956,9

Norvège               2010 16362449,2

Royaume-Uni           2011  112585949

...

La requête précédente affiche, pour chaque produit, le nom de la catégorie correspondante.

SQL> SELECT PAYS, ANNEE, SUM( PRIX_UNITAIRE*QUANTITE) CA

  2  FROM CLIENTS NATURAL JOIN COMMANDES NATURAL JOIN DETAILS_COMMANDES

  3       NATURAL JOIN PRODUITS GROUP BY PAYS, ANNEE;

SELECT PAYS, ANNEE,

*

ERREUR à la ligne 1 :

ORA-01722: Nombre non valide

Dans l’exemple précèdent, la requête joint les tables DETAILS_COMMANDE et PRODUITS à l’aide de l’opérateur « NATURAL JOIN ». Vous pouvez remarquer que la jointure donne un message d’erreur de nombre invalide. En effet la colonne QUANTITE est prise en compte pour la jointure ; son nom est identique dans les deux tables, mais le type de la colonne est différent.

L’opérateur OUTER JOIN

Dans le cas d'une jointure classique, lorsqu'une ligne d'une table ne satisfait pas à la condition de jointure, cette ligne n'apparaît pas dans le résultat final.

Il peut cependant être souhaitable de conserver les lignes d'une table qui ne répondent pas à la condition de jointure. On parle alors de jointure externe (outer join).

L’opérateur « OUTER JOIN ON » effectue une jointure externe entre deux tables en se servant des conditions spécifiées respectant la syntaxe suivante :

SELECT [ALL | DISTINCT]{*,[EXPRESSION1 [AS] ALIAS1[,...]}

FROM NOM_TABLE1[ {LEFT | RIGHT | FULL} OUTER JOIN NOM_TABLE2

   {  ON (NOM_TABLE1.NOM_COLONNE = NOM_TABLE2.NOM_COLONNE)]

    | USING (NOM_COLONNE1[,...])] };

SQL> SELECT A.NOM "Employé",

  2         NVL(B.NOM,'------------') "Supérieur",

  3         NVL(C.NOM,'------------') "Manager"

  4  FROM EMPLOYES A LEFT OUTER JOIN EMPLOYES B

  5       ON (A.REND_COMPTE = B.NO_EMPLOYE)

  6       LEFT OUTER JOIN EMPLOYES C

  7       ON (B.REND_COMPTE = C.NO_EMPLOYE)

  8       AND A.NO_EMPLOYE BETWEEN 6 AND 20;

 

Employé      Supérieur    Manager

------------ ------------ ------------

...

Malejac      Chambaud     Brasseur

Suyama       Chambaud     Brasseur

Letertre     Chambaud     ------------

Lombard      Chambaud     ------------

Arrambide    Chambaud     ------------

Dohr         Chambaud     ------------

Mangeard     Chambaud     ------------

Guerdon      ------------ ------------

Grangirard   ------------ ------------

Etienne      ------------ ------------

Giroux       ------------ ------------

Callahan     ------------ ------------

Maurer       ------------ ------------

...

La requête précédente affiche tous les employés et leur supérieur hiérarchique s’il y a un et le manager de celui-ci si toutefois il existe.

La requête suivante affiche tous les clients français et les fournisseurs qui habitent dans la même ville s’ils existent.

SQL> SELECT NVL(C.SOCIETE,'------------------') CLIENT,

  2         NVL(F.SOCIETE,'------------------') FOURNISSEUR

  3  FROM CLIENTS C LEFT OUTER JOIN FOURNISSEURS F

  4       USING( VILLE,PAYS) WHERE PAYS = 'France';

 

CLIENT                         FOURNISSEUR

------------------------------ ------------------------------

Spécialités du monde           Aux joyeux ecclésiastiques

Paris spécialités              Aux joyeux ecclésiastiques

Bon app'                       ------------------

Blondel père et fils           ------------------

La corne d'abondance           ------------------

Vins et alcools Chevalier      ------------------

Folies gourmandes              ------------------

Victuailles en stock           ------------------

France restauration            ------------------

Du monde entier                ------------------

La maison d'Asie               ------------------

La requête suivante affiche tous les fournisseurs français et les clients qui habitent dans la même ville s’ils existent.

SQL> SELECT NVL(C.SOCIETE,'------------------') CLIENT,

  2         NVL(F.SOCIETE,'------------------') FOURNISSEUR

  3  FROM CLIENTS C RIGHT OUTER JOIN FOURNISSEURS F

  4       USING( VILLE,PAYS) WHERE PAYS = 'France';

 

CLIENT                         FOURNISSEUR

------------------------------ ------------------------------

Paris spécialités              Aux joyeux ecclésiastiques

Spécialités du monde           Aux joyeux ecclésiastiques

------------------             Gai pâturage

------------------             Escargots Nouveaux

La requête suivante affiche tous les fournisseurs français et les clients qui habitent dans la même ville s’ils existent.

SQL> SELECT NVL(C.SOCIETE,'------------------') CLIENT,

  2         NVL(F.SOCIETE,'------------------') FOURNISSEUR

  3  FROM CLIENTS C FULL OUTER JOIN FOURNISSEURS F

  4       USING( VILLE,PAYS) WHERE PAYS = 'France';

 

CLIENT                         FOURNISSEUR

------------------------------ ------------------------------

Du monde entier                ------------------

Folies gourmandes              ------------------

France restauration            ------------------

La corne d'abondance           ------------------

La maison d'Asie               ------------------

Paris spécialités              Aux joyeux ecclésiastiques

Spécialités du monde           Aux joyeux ecclésiastiques

Victuailles en stock           ------------------

Vins et alcools Chevalier      ------------------

Blondel père et fils           ------------------

Bon app'                       ------------------

------------------             Escargots Nouveaux

------------------             Gai pâturage

Le filtre sur la colonne PAYS est simple de traiter car il est utilisé dans la clause « USING ». Si vous utiliser la jointure en précisant la clause « ON » il peut y avoir des problèmes car on ne parle plus d’une seule colonne mais des deux.

SQL> SELECT NVL(C.SOCIETE,'------------------') CLIENT,

  2         NVL(F.SOCIETE,'------------------') FOURNISSEUR

  3  FROM CLIENTS C LEFT OUTER JOIN FOURNISSEURS F

  4  ON (C. VILLE = F.VILLE AND C.PAYS = F.PAYS) WHERE C.PAYS = 'France';

 

CLIENT                                   FOURNISSEUR

---------------------------------------- ----------------------------

Spécialités du monde                     Aux joyeux ecclésiastiques

Paris spécialités                        Aux joyeux ecclésiastiques

Bon app'                                 ------------------

Blondel père et fils                     ------------------

La corne d'abondance                     ------------------

Vins et alcools Chevalier                ------------------

Folies gourmandes                        ------------------

Victuailles en stock                     ------------------

France restauration                      ------------------

Du monde entier                          ------------------

La maison d'Asie                         ------------------

 

SQL> SELECT NVL(C.SOCIETE,'------------------') CLIENT,

  2         NVL(F.SOCIETE,'------------------') FOURNISSEUR

  3  FROM CLIENTS C LEFT OUTER JOIN FOURNISSEURS F

  4  ON (C. VILLE = F.VILLE AND C.PAYS = F.PAYS) WHERE F.PAYS = 'France';

 

CLIENT                                   FOURNISSEUR

---------------------------------------- ----------------------------

Paris spécialités                        Aux joyeux ecclésiastiques

Spécialités du monde                     Aux joyeux ecclésiastiques

Dans le premier exemple le filtre est sur une colonne de la table maître. Comme pour cette table on récupère tous les enregistrements le filtre ne perturbe pas la jointure. Dans le deuxième cas la le filtre transforme la jointure externe dans une équijointure. Le filtre ne doit pas être écrit dans la clause « WHERE » mais inclus directement dans la déclaration de la jointure.

SQL> SELECT NVL(C.SOCIETE,'------------------') CLIENT,

  2         NVL(F.SOCIETE,'------------------') FOURNISSEUR

  3  FROM CLIENTS C LEFT OUTER JOIN FOURNISSEURS F

  4  ON (C. VILLE = F.VILLE AND C.PAYS = F.PAYS AND F.PAYS = 'France' );

 

CLIENT                                   FOURNISSEUR

---------------------------------------- --------------------------------

Spécialités du monde                     Aux joyeux ecclésiastiques

Paris spécialités                        Aux joyeux ecclésiastiques

Great Lakes Food Market                  ------------------

Königlich Essen                          ------------------

Victuailles en stock                     ------------------

Godos Cocina Típica                      ------------------

La maison d'Asie                         ------------------

Wilman Kala                              ------------------

...

SQL >SELECT COUNT(*) FROM CLIENTS;

 

  COUNT(*)

----------

        91

SQL >SELECT COUNT(*) FROM FOURNISSEURS;

 

  COUNT(*)

----------

        29

SQL >SELECT COUNT(DISTINCT C.SOCIETE) C,COUNT(DISTINCT F.SOCIETE) F,

  2  COUNT(*) TOTAL FROM CLIENTS C RIGHT OUTER JOIN FOURNISSEURS F

  3  ON (C. VILLE = F.VILLE );

 

         C          F      TOTAL

---------- ---------- ----------

        15         29         38

SQL >SELECT COUNT(DISTINCT C.SOCIETE) C,COUNT(DISTINCT F.SOCIETE) F,

  2  COUNT(*) TOTAL FROM CLIENTS C LEFT OUTER JOIN FOURNISSEURS F

  3  ON (C. VILLE = F.VILLE );

 

         C          F      TOTAL

---------- ---------- ----------

        91          6         91

SQL >SELECT COUNT(DISTINCT C.SOCIETE) C,COUNT(DISTINCT F.SOCIETE) F,

  2  COUNT(*) TOTAL FROM CLIENTS C FULL OUTER JOIN FOURNISSEURS F

  3  ON (C. VILLE = F.VILLE  );

 

         C          F      TOTAL

---------- ---------- ----------

        91         29        114

SQL >SELECT COUNT(DISTINCT C.SOCIETE) C,COUNT(DISTINCT F.SOCIETE) F,

  2  COUNT(*) TOTAL FROM CLIENTS C RIGHT OUTER JOIN FOURNISSEURS F

  3  ON (C. VILLE = F.VILLE AND F.PAYS = 'France');

 

         C          F      TOTAL

---------- ---------- ----------

         2         29         30

SQL >SELECT COUNT(DISTINCT C.SOCIETE) C,COUNT(DISTINCT F.SOCIETE) F,

  2  COUNT(*) TOTAL FROM CLIENTS C LEFT OUTER JOIN FOURNISSEURS F

  3  ON (C. VILLE = F.VILLE AND F.PAYS = 'France');

 

         C          F      TOTAL

---------- ---------- ----------

        91          1         91

SQL >SELECT COUNT(DISTINCT C.SOCIETE) C,COUNT(DISTINCT F.SOCIETE) F,

  2  COUNT(*) TOTAL FROM CLIENTS C FULL OUTER JOIN FOURNISSEURS F

  3  ON (C. VILLE = F.VILLE AND F.PAYS = 'France');

 

         C          F      TOTAL

---------- ---------- ----------

        91         29        119