JOIN¶

Syntaxe¶

Utilisez l’une des méthodes suivantes :

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

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

SELECT ...
FROM <object_ref1> [
                     {
                       NATURAL [
                                 {
                                   INNER
                                   | { LEFT | RIGHT | FULL } [ OUTER ]
                                 }
                                 [ DIRECTED ]
                               ]
                       | CROSS  [ DIRECTED ]
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Paramètres¶

object_ref1 et object_ref2

Chaque référence d’objet est une table ou une source de données sous forme de table.

JOIN

Utilisez le mot-clé JOIN pour spécifier que les tables doivent être jointes. Combinez JOIN avec d’autres mots-clés liés à la jointure (par exemple, INNER ou OUTER) pour spécifier le type de jointure.

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 jointure	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. (Vous pouvez également utiliser une virgule pour spécifier une jointure intérieure. Pour un exemple, voir la section Exemples.) Si vous utilisez INNER JOIN sans la clause ON (ou si vous utilisez une virgule sans clause WHERE), le résultat est le même que si vous utilisez CROSS JOIN : un produit cartésien (chaque ligne de o1 appariée avec 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 à rien dans o2. Les colonnes de résultats se référant à 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 se référant à 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. CROSS JOIN ne peut ê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 des exemples, voir la section Exemples. Un NATURAL JOIN peut être combiné avec un OUTER JOIN. Un NATURAL JOIN ne peut ê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.

Le mot clé DIRECTED spécifie une jointure dirigée, ce qui impose l’ordre de jointure des tables. La première table, ou table de gauche, est analysée avant la deuxième table, ou table de droite. Par exemple, o1 INNER DIRECTED JOIN o2 analyse la table o1 avant la table o2. Les jointures dirigées sont utiles dans les situations suivantes :

• Vous migrez des charges de travail dans Snowflake qui ont des directives d’ordre de jointure.

• Vous souhaitez améliorer les performances en analysant les tables de jointure dans un ordre spécifique.

Note

La fonctionnalité de jointure dirigée est une fonction de prévisualisation disponible pour tous les comptes.

Par défaut : INNER JOIN

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

Si le mot clé DIRECTED est ajouté, le type de jointure (par exemple, INNER ou OUTER) est nécessaire.

Voir aussi :

• LATERAL

• ASOF JOIN

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

Copy

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

La clause ON est interdite pour CROSS JOIN.

La clause ON est inutile, et interdite, pour NATURAL JOIN, car les colonnes de jointure sont implicites.

Pour les autres jonctions, la clause ON est facultative. Toutefois, l’omission de la clause ON donne lieu à un produit cartésien (chaque ligne de object_ref1 appariée avec chaque ligne de object_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 de jointure. Ces colonnes sont utilisées comme colonnes de jointure. 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)

Copy

Dans le cas simple, cela équivaudrait à :

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

Copy

Dans la syntaxe JOIN standard, la liste de projection (la liste des colonnes et autres expressions après le mot-clé SELECT) est *. La requête renvoie donc exactement une fois key_column. Les colonnes sont renvoyées dans l’ordre suivant :

• Les colonnes de la clause USING dans l’ordre spécifié.

• Les colonnes du tableau de gauche ne sont pas spécifiées dans la clause USING.

• Les colonnes de la table de droite ne sont pas spécifiées dans la clause USING.

Pour des exemples d’utilisation standard et non standard, voir la section Exemples.

Notes sur l’utilisation¶

• Les restrictions suivantes s’appliquent aux fonctions de table autres que les UDTFs SQL :

  • Vous ne pouvez pas spécifier la clause ON, USING ou NATURAL JOIN dans une fonction de table latérale (autre qu’une SQL UDTF).

    Par exemple, la syntaxe suivante n’est pas autorisée :

    SELECT ... FROM my_table
      JOIN TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      INNER JOIN TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      JOIN TABLE(my_js_udtf(col_a))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      INNER JOIN TABLE(my_js_udtf(col_a))
      ON ... ;
    

    Copy

  • Vous ne pouvez pas spécifier la clause ON, USING ou NATURAL JOIN dans une jointure latérale externe à une fonction de table (autre qu’une SQL UDTF).

    Par exemple, la syntaxe suivante n’est pas autorisée :

    SELECT ... FROM my_table
      LEFT JOIN TABLE(FLATTEN(input=>[a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      FULL JOIN TABLE(FLATTEN(input=>[a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      LEFT JOIN TABLE(my_js_udtf(a))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      FULL JOIN TABLE(my_js_udtf(a))
      ON ... ;
    

    Copy

    L’utilisation de cette syntaxe entraîne l’erreur suivante :

    000002 (0A000): Unsupported feature
      'lateral table function called with OUTER JOIN syntax
       or a join predicate (ON clause)'
    

  • Ces restrictions ne s’appliquent pas si vous utilisez une virgule plutôt qu’un mot-clé JOIN :

    SELECT ... FROM my_table,
      TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

    Copy

Exemples¶

De nombreux exemples de JOIN utilisent deux tables : t1 et t2. Créer ces tables et insérer des données :

CREATE TABLE t1 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES
  (2),
  (3),
  (4);

CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t2 (col1) VALUES
  (1),
  (2),
  (2),
  (3);

Les exemples suivants exécutent des requêtes avec des jointures :

• Exécuter une requête avec une jointure intérieure

• Exécuter une requête avec une jointure extérieure gauche

• Exécuter une requête avec une jointure extérieure droite

• Exécuter une requête avec une jointure extérieure complète

• Exécuter une requête avec une jointure croisée

• Exécuter une requête avec une jointure naturelle

• Exécuter une requête qui combine les jointures dans la clause FROM

• Exécuter des requêtes avec des jointures qui utilisent la clause USING

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 |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

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 |
+------+------+

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 |
+------+------+

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 |
+------+------+

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 |
+------+------+

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 |
+------+------+

CREATE OR REPLACE TABLE d1 (
  id NUMBER,
  name VARCHAR);

INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');

CREATE OR REPLACE TABLE d2 (
  id NUMBER,
  value VARCHAR);

INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');

SELECT *
  FROM d1 NATURAL INNER JOIN d2
  ORDER BY id;

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

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    |
+----+------+-------+

CREATE TABLE t3 (col1 INTEGER);

INSERT INTO t3 (col1) VALUES
  (2),
  (6);

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 |
+------+------+------+

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 |
+------+------+------+

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

+--------+
| USERID |
|--------|
| a      |
+--------+

WITH
  l AS (
       SELECT 'a' AS userid
     ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT l.userid as UI_L,
       r.userid as UI_R
  FROM l LEFT JOIN r USING(userid);

+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+