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.

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(*)

----------

SQL >SELECT COUNT(*) FROM FOURNISSEURS;

COUNT(*)

----------

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