Catégories :

Syntaxe de requête

JOIN

Une clause JOIN est une sous-clause d’une clause FROM.

Une opération JOIN combine les lignes de deux tables (ou d’autres sources, telles que des vues ou des fonctions de tables) pour créer une nouvelle ligne combinée pouvant être utilisée dans la requête.

Généralement, les données des deux tables sont liées d’une certaine façon. Par exemple, une table pourrait contenir des informations sur les projets, et une table pourrait contenir des informations sur les employés qui travaillent sur ces projets.

Les deux tables contiennent généralement une ou plusieurs colonnes en commun de sorte que les lignes d’une table peuvent être associées aux lignes correspondantes de l’autre table. Par exemple, chaque ligne de la table projets peut avoir un ID de projet unique et chaque ligne de la table employés peut inclure l’ID du projet auquel l’employé est actuellement affecté.

La clause JOIN spécifie (explicitement ou implicitement) comment relier les lignes d’une table aux lignes correspondantes de l’autre table, typiquement en référençant la ou les colonnes communes, comme l’ID de projet.

Voir aussi :

Jointure latérale

Syntaxe

Utilisez l’une des méthodes suivantes :

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]
SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]
SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

JOIN

La sémantique de jointure est la suivante (par souci de brièveté, cette rubrique utilise o1 et o2 pour object_ref1 et object_ref2, respectivement) :

Type de jonction

Sémantique

o1 INNER JOIN o2

Pour chaque ligne de o1, une ligne est produite pour chaque ligne de o2 correspondante selon la sous-clause ON condition. (Notez que vous pouvez également utiliser une virgule pour spécifier une jonction intérieure. Pour consulter un exemple, voir la rubrique exemples ci-dessous.) Si vous utilisez INNER JOIN sans la clause ON (ou si vous utilisez une virgule sans une clause WHERE), le résultat est identique à l’utilisation de CROSS JOIN : un produit cartésien (chaque ligne de o1 associée à chaque ligne de o2).

o1 LEFT OUTER JOIN o2

Le résultat de la jointure interne est augmenté d’une ligne pour chaque ligne de o1 qui ne correspond pas à o2. Les colonnes de résultats faisant référence à o2 contiennent null.

o1 RIGHT OUTER JOIN o2

Le résultat de la jointure interne est augmenté d’une ligne pour chaque ligne de o2 qui ne correspond à rien dans o1. Les colonnes de résultats faisant référence à o1 contiennent null.

o1 FULL OUTER JOIN o2

Renvoie toutes les lignes jointes, plus une ligne pour chaque ligne du côté gauche non appariée (prolongée avec des zéros à droite), plus une ligne pour chaque ligne du côté droit non appariée (prolongée avec des zéros à gauche).

o1 CROSS JOIN o2

Pour chaque combinaison possible de lignes de o1 et o2 (c’est-à-dire produit cartésien), la table jointe contient une ligne composée de toutes les colonnes de o1 suivies de toutes les colonnes de o2. Un CROSS JOIN ne peut pas être combiné avec une clause ON condition. Cependant, vous pouvez utiliser une clause WHERE pour filtrer les résultats.

o1 NATURAL JOIN o2

Un NATURAL JOIN est identique à un JOIN explicite sur les colonnes communes des deux tables, à ceci près que les colonnes communes ne sont incluses qu’une seule fois dans la sortie. (Une association naturelle suppose que les colonnes portant le même nom, mais situées dans des tables différentes, contiennent des données correspondantes.) Pour plus d’exemples, voir la rubrique exemples ci-dessous. Un NATURAL JOIN peut être combiné avec un OUTER JOIN. Un NATURAL JOIN ne peut pas être combiné avec une clause ON condition, car la condition JOIN est déjà implicite. Cependant, vous pouvez utiliser une clause WHERE pour filtrer les résultats.

Par défaut : INNER JOIN

Si le mot JOIN est utilisé sans spécifier INNER ou OUTER, alors JOIN sera une jonction interne.

ON condition

Une expression booléenne qui définit les lignes des deux côtés de JOIN qui sont considérées comme étant identiques, par exemple :

ON object_ref2.id_number = object_ref1.id_number

Les conditions sont discutées plus en détail dans la clause WHERE.

La clause ON est interdite pour CROSS JOIN.

La clause ON est inutile (et interdite) pour NATURAL JOIN. Les colonnes de jonction sont implicites.

Pour les autres jonctions, la clause ON est facultative. Cependant, l’omission de la clause ON donne un produit cartésien (chaque ligne de objet_ref1 associée à chaque ligne de objet_ref2). Un produit cartésien peut produire un très grand volume de sortie, dont la quasi-totalité se compose de paires de lignes qui ne sont pas réellement liées ; cela consomme beaucoup de ressources et produit souvent une erreur utilisateur.

USING( <column_list> )

Une liste des colonnes communes entre les deux tables en cours d’association ; ces colonnes sont utilisées comme des colonnes d’association. Les colonnes doivent porter le même nom et avoir la même signification dans chacune des tables associées.

Par exemple, supposons que l’instruction SQL contienne :

... o1 JOIN o2
    USING (key_column)

Dans le cas simple, cela équivaudrait à :

... o1 JOIN o2
    ON o2.key_column = o1.key_column

Pour utiliser correctement la clause USING, la liste de projection (la liste des colonnes et d’autres expressions après le mot clé SELECT) doit être « * ». Cela permet au serveur de renvoyer la clé_colonne exactement une fois, ce qui est la manière standard d’utiliser la clause USING. Pour des exemples d’utilisation standard et non standard, voir les exemples ci-dessous.

Exemples

De nombreux exemples de JOIN utilisent deux tables, t1 et t2. Les tables et leurs données sont créées comme indiqué ci-dessous :

CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 (col1) VALUES 
   (2),
   (3),
   (4);
INSERT INTO t2 (col1) VALUES 
   (1),
   (2),
   (2),
   (3);

Jointure intérieure :

SELECT t1.col1, t2.col1 FROM t1 INNER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Cela montre une jointure externe gauche. Notez la valeur NULL pour la ligne de la table t1 qui n’a pas de ligne correspondante dans la table t2.

SELECT t1.col1, t2.col1 FROM t1 LEFT OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

Cela montre une jointure externe droite. Notez la valeur NULL pour la ligne de la table t1 qui n’a pas de ligne correspondante dans la table t2.

SELECT t1.col1, t2.col1 FROM t1 RIGHT OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

Cela montre une jointure externe complète. Attention, chaque table ayant une ligne qui ne correspond à aucune ligne dans l’autre table, la sortie contient deux lignes avec des valeurs NULL :

SELECT t1.col1, t2.col1 FROM t1 FULL OUTER JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1,2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

Voici un exemple de jointure croisée, qui produira le produit cartésien. Attention, l’association croisée ne contient pas de clause ON.

SELECT t1.col1, t2.col1 FROM t1 CROSS JOIN t2 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

Une jointure croisée peut être filtrée par une clause WHERE, comme le montre l’exemple ci-dessous :

SELECT t1.col1, t2.col1 FROM t1 CROSS JOIN t2 WHERE t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Voici un exemple de jointure naturelle. Cela produit le même résultat que la jointure interne correspondante, sauf que la sortie n’inclut pas de seconde copie de la colonne de jointure :

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT * FROM d1 NATURAL INNER JOIN d2 ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

Les jointures naturelles peuvent être combinées avec des jointures extérieures, par exemple :

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY ID;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

L’opérateur virgule est l’ancienne syntaxe de INNER JOIN. Lorsqu’une virgule est utilisée à la place de INNER JOIN, la condition de filtre est placée dans la clause WHERE plutôt que dans la clause ON. Les deux instructions suivants sont équivalentes :

Style plus récent (préféré) :

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Style plus ancien :

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Les jointures peuvent être combinées dans la clause FROM. La requête suivante montre deux jointures chaînées dans la clause FROM :

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
     RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

Dans une telle requête, les résultats sont déterminés en fonction des jointures se déroulant de gauche à droite (bien que l’optimiseur puisse réorganiser les jointures si un ordre différent donne le même résultat). Si la jointure extérieure droite doit avoir lieu avant la jointure extérieure gauche, alors la requête peut être écrite comme suit :

SELECT t1.*, t2.*, t3.*
  FROM t1
     LEFT OUTER JOIN
     (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
     ON (t1.col1 = t2.col1)
  ORDER BY t1.col1;
+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

Les deux exemples ci-dessous illustrent l’utilisation standard et non standard de la clause USING.

Ce premier exemple montre une utilisation correcte. Plus précisément, la liste de projection contient « * » et rien d’autre. Même si la requête joint deux tables et que chaque table a une colonne et que la requête demande toutes les colonnes, la sortie contient une seule colonne et non deux.

WITH 
    l AS (
         SELECT 'a' AS userid
         ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT * 
    FROM l LEFT JOIN r USING(userid)
;
+--------+
| USERID |
|--------|
| a      |
+--------+

L’exemple suivant montre une utilisation non standard. La liste de projection contient autre chose que « * ». Comme l’utilisation n’est pas standard, la sortie contient deux colonnes nommées « userid », et la deuxième occurrence (qui pourrait contenir une valeur de la table “r”) contient une valeur qui ne figure pas dans la table (la valeur “a” n’est pas dans la table “r”).

WITH 
    l AS (
         SELECT 'a' AS userid
       ),
    r AS (
         SELECT 'b' AS userid
         )
  SELECT l.userid as UI_L,
         r.userid as UI_R  -- Incorrect usage!
    FROM l LEFT JOIN r USING(userid)
;
+------+------+
| UI_L | UI_R |
|------+------|
| a    | a    |
+------+------+