- Catégories :
JOIN¶
Une opération JOIN
combine les lignes de deux tables (ou d’autres sources similaires aux tables, 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. Pour une explication conceptuelle des jointures, voir Utilisation des jointures.
Cette rubrique explique comment utiliser la sous-clause JOIN
dans la clause FROM. La sous-clause JOIN
spécifie, explicitement ou implicitement, comment relier les lignes d’une table aux lignes correspondantes de l’autre table. Vous pouvez également utiliser la sous-clause ASOF JOIN, qui est utilisé pour joindre des données de séries temporelles sur des colonnes d’horodatage lorsque leurs valeurs se suivent de près, se précèdent ou correspondent exactement.
Bien que la manière recommandée de joindre les tables soit d’utiliser JOIN
avec la sous-clause ON
de la clause FROM
, une autre manière de joindre des tables est d’utiliser la clause WHERE
. Pour plus de détails, voir la documentation relative à la clause WHERE .
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
etobject_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. CombinezJOIN
avec d’autres mots-clés liés à la jointure (par exemple,INNER
ouOUTER
) pour spécifier le type de jointure.La sémantique de jointure est la suivante (par souci de brièveté, cette rubrique utilise
o1
eto2
pourobject_ref1
etobject_ref2
, respectivement).Type de jointure
Sémantique
o1 INNER JOIN o2
Pour chaque ligne de
o1
, une ligne est produite pour chaque ligne deo2
correspondante selon la sous-clauseON condition
. (Vous pouvez également utiliser une virgule pour spécifier une jointure intérieure. Pour un exemple, voir la section Exemples.) Si vous utilisezINNER JOIN
sans la clauseON
(ou si vous utilisez une virgule sans clauseWHERE
), le résultat est le même que si vous utilisezCROSS JOIN
: un produit cartésien (chaque ligne deo1
appariée avec chaque ligne deo2
).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 danso2
. 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 danso1
. 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
eto2
(c’est-à-dire produit cartésien), la table jointe contient une ligne composée de toutes les colonnes deo1
suivies de toutes les colonnes deo2
.CROSS JOIN
ne peut être combiné avec une clauseON condition
. Cependant, vous pouvez utiliser une clauseWHERE
pour filtrer les résultats.o1 NATURAL JOIN o2
Un
NATURAL JOIN
est identique à unJOIN
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. UnNATURAL JOIN
peut être combiné avec unOUTER JOIN
. UnNATURAL JOIN
ne peut être combiné avec une clauseON condition
, car la conditionJOIN
est déjà implicite. Cependant, vous pouvez utiliser une clauseWHERE
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 tableo1
avant la tableo2
. 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écifierINNER
ouOUTER
, alorsJOIN
est une jointure interne.Si le mot clé
DIRECTED
est ajouté, le type de jointure (par exemple,INNER
ouOUTER
) est nécessaire.Voir aussi :
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 documentation relative à la clause WHERE .
La clause
ON
est interdite pourCROSS JOIN
.La clause
ON
est inutile, et interdite, pourNATURAL JOIN
, car les colonnes de jointure sont implicites.Pour les autres jonctions, la clause
ON
est facultative. Toutefois, l’omission de la clauseON
donne lieu à un produit cartésien (chaque ligne deobject_ref1
appariée avec chaque ligne deobject_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)
Dans le cas simple, cela équivaudrait à :
... o1 JOIN o2 ON o2.key_column = o1.key_column
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 foiskey_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
ouNATURAL 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 ... ;
SELECT ... FROM my_table INNER JOIN TABLE(FLATTEN(input=>[col_a])) ON ... ;
SELECT ... FROM my_table JOIN TABLE(my_js_udtf(col_a)) ON ... ;
SELECT ... FROM my_table INNER JOIN TABLE(my_js_udtf(col_a)) ON ... ;
Vous ne pouvez pas spécifier la clause
ON
,USING
ouNATURAL 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 ... ;
SELECT ... FROM my_table FULL JOIN TABLE(FLATTEN(input=>[a])) ON ... ;
SELECT ... FROM my_table LEFT JOIN TABLE(my_js_udtf(a)) ON ... ;
SELECT ... FROM my_table FULL JOIN TABLE(my_js_udtf(a)) ON ... ;
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 ... ;
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 qui combine les jointures dans la clause FROM
Exécuter des requêtes avec des jointures qui utilisent la clause USING
Exécuter une requête avec une jointure intérieure¶
L’exemple suivant exécute une requête avec une 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 |
+------+------+
Exécuter la même requête avec une jointure intérieure pour appliquer l’ordre de jointure afin que la table de gauche soit analysée en premier :
Note
La fonctionnalité de jointure dirigée est une fonction de prévisualisation disponible pour tous les comptes.
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 |
+------+------+
Exécuter une requête avec une jointure extérieure gauche¶
L’exemple suivant exécute une requête avec une jointure extérieure gauche :
SELECT t1.col1, t2.col1
FROM t1 LEFT OUTER JOIN t2
ON t2.col1 = t1.col1
ORDER BY 1,2;
Dans la sortie, il existe une valeur NULL pour la ligne dans la table t1
qui n’a pas de ligne correspondante dans la table t2
:
+------+------+
| COL1 | COL1 |
|------+------|
| 2 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | NULL |
+------+------+
Exécuter une requête avec une jointure extérieure droite¶
L’exemple suivant exécute une requête avec une jointure extérieure droite :
SELECT t1.col1, t2.col1
FROM t1 RIGHT OUTER JOIN t2
ON t2.col1 = t1.col1
ORDER BY 1,2;
Dans la sortie, il existe une valeur NULL pour la ligne dans la table t1
qui n’a pas de ligne correspondante dans la table t2
.
+------+------+
| COL1 | COL1 |
|------+------|
| 2 | 2 |
| 2 | 2 |
| 3 | 3 |
| NULL | 1 |
+------+------+
Exécuter une requête avec une jointure extérieure complète¶
L’exemple suivant exécute une requête avec une jointure extérieure complète :
SELECT t1.col1, t2.col1
FROM t1 FULL OUTER JOIN t2
ON t2.col1 = t1.col1
ORDER BY 1,2;
Chaque table a une ligne qui ne correspond à aucune ligne dans l’autre table, la sortie contient ainsi deux lignes avec des valeurs NULL :
+------+------+
| COL1 | COL1 |
|------+------|
| 2 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | NULL |
| NULL | 1 |
+------+------+
Exécuter une requête avec une jointure croisée¶
L’exemple suivant exécute une requête avec une jointure croisée :
Note
Une jointure croisée n’a pas de clause ON.
SELECT t1.col1, t2.col1
FROM t1 CROSS JOIN t2
ORDER BY 1, 2;
La sortie montre que la requête produit un produit cartésien :
+------+------+
| 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 suivant :
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 |
+------+------+
Exécuter une requête avec une jointure naturelle¶
L’exemple suivant montre une requête avec une jointure naturelle. Tout d’abord, créez deux tables et insérez des données :
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');
Exécuter une requête avec une jointure naturelle :
SELECT *
FROM d1 NATURAL INNER JOIN d2
ORDER BY id;
La sortie montre qu’une jointure naturelle produit la même sortie que la jointure intérieure correspondante, sauf que la sortie n’inclut pas de seconde copie de la colonne de jointure :
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
| 1 | a | xx |
| 2 | b | yy |
+----+------+-------+
L’exemple suivant montre que vous pouvez combiner des jointures naturelles avec des jointures extérieures :
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 |
+----+------+-------+
Exécuter une requête qui combine les jointures dans la clause FROM¶
Vous pouvez combiner dans la clause FROM
. Créer une troisième table :
CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t3 (col1) VALUES
(2),
(6);
Exécuter une requête qui enchaîne deux jointures 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 écrivez la requête 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 |
+------+------+------+
Exécuter des requêtes avec des jointures qui utilisent la clause USING¶
Les deux exemples suivants illustrent l’utilisation standard (ISO 9075) et non standard de la clause USING
. Les deux sont prises en charge par Snowflake.
Ce premier exemple montre une utilisation standard. Plus précisément, la liste de projection contient exactement *
:
WITH
l AS (
SELECT 'a' AS userid
),
r AS (
SELECT 'b' AS userid
)
SELECT *
FROM l LEFT JOIN r USING(userid);
Même si la requête d’exemple 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 :
+--------+
| USERID |
|--------|
| a |
+--------+
L’exemple suivant montre une utilisation non standard. La liste de projection contient autre chose que *
:
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);
La sortie contient deux colonnes, et la deuxième colonne contient soit une valeur de la deuxième table, soit NULL :
+------+------+
| UI_L | UI_R |
|------+------|
| a | NULL |
+------+------+