Catégories :: Syntaxe de requête

Utilisation des jointures (ou jonctions)¶

Une jointure combine des lignes de deux tables pour créer une nouvelle ligne combinée qui peut être utilisée dans la requête.

Dans ce chapitre :

Introduction¶

Les jointures sont utiles lorsque les données des tables sont liées. 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.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

Copy

SELECT * FROM employees ORDER BY employee_ID;
+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     | 1000       |
|    10000002 | Maria Inverness | 1000       |
|    10000003 | Pat Wang        | 1001       |
|    10000004 | NewEmployee     | NULL       |
+-------------+-----------------+------------+

Copy

Les deux tables jointes contiennent généralement une ou plusieurs colonnes en commun de sorte que les lignes d’une table puissent ê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é.

L’opération de jointure 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. Par exemple, ce qui suit joint les tables du projet et des employés présentées ci-dessus :

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+

Copy

Bien qu’une seule opération de jointure ne puisse réunir que deux tables, les jointures peuvent être enchaînées les unes aux autres. Le résultat d’une jointure est un objet ressemblant à une table, et cet objet peut ensuite être joint à un autre objet ressemblant à une table. Sur le plan conceptuel, l’idée est similaire à ce qui suit (ce n’est pas la syntaxe réelle) :

table1 join (table2 join table 3)

Copy

Dans ce pseudo-code, la table2 et la table3 sont jointes en premier. La table qui résulte de cette jointure est alors jointe à la table1.

Les jointures peuvent être appliquées non seulement aux tables, mais aussi à d’autres objets ressemblant à des tables. Vous pouvez joindre :

Une table.
Une vue (matérialisée ou non).
Un littéral de table.
Une expression qui correspond à l’équivalent d’une table (contenant une ou plusieurs colonnes et zéro ou plusieurs lignes). Par exemple :
- Le jeu de résultats renvoyé par une fonction de table.
- Le jeu de résultats renvoyé par une sous-requête qui renvoie une table.

Lorsque ce sujet fait référence à la jointure d’une table, il signifie généralement le fait de joindre tout objet ressemblant à une table.

Note

Snowflake peut améliorer les performances en éliminant les jointures inutiles. Pour plus de détails, voir Comprendre comment Snowflake peut éliminer les jointures redondantes.

Types de jointures¶

Snowflake prend en charge les types de jointures suivants :

Jointure intérieure.
Jointure extérieure.
Jointure croisée.
Jointure naturelle.
ASOF JOIN.

Jointure intérieure¶

Une jointure intérieure associe chaque ligne d’une table avec la ou les lignes correspondantes dans l’autre table.

L’exemple ci-dessous utilise une jointure interne :

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+

Copy

Dans cet exemple, la table de sortie contient deux colonnes nommées « Projet_ID ». Une colonne Projet_ID provient de la table des projets, et une autre de la table des employés. Pour chaque ligne de la table de sortie, les valeurs des deux colonnes Projet_ID correspondent, car la requête a spécifié e.project_id = p.project_id.

La sortie ne comprend que des paires valides (c’est-à-dire des lignes qui correspondent à la condition de jointure). Dans cet exemple, il n’y a pas de ligne pour le projet nommé « NewProject » (qui n’a pas encore d’employés affectés) ou le nouvel employé nommé « NewEmployee » (qui n’a pas encore été affecté à un projet).

Jointure extérieure¶

Une jointure extérieure énumère toutes les lignes de la table spécifiée, même si ces lignes ne correspondent pas dans l’autre table. Par exemple, une jointure extérieure gauche entre les projets et les employés énumère tous les projets, y compris les projets auxquels aucun employé n’a encore été affecté.

SELECT p.project_name, e.employee_name
    FROM projects AS p LEFT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

Copy

Le projet nommé « NewProject » est inclus dans cette sortie même s’il n’y a pas de ligne correspondante dans la table des employés. Comme il n’y a pas de noms d’employés correspondants pour le projet intitulé « NewProject », le nom de l’employé est réglé sur NULL.

Une jointure extérieure droite énumère tous les employés (quel que soit le projet).

SELECT p.project_name, e.employee_name
    FROM projects AS p RIGHT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

Copy

Une jointure extérieure complète énumère tous les projets et tous les employés.

SELECT p.project_name, e.employee_name
    FROM projects AS p FULL OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

Copy

Jointure croisée¶

une jointure croisée combine chaque ligne de la première table avec chaque ligne de la seconde table, créant ainsi toutes les combinaisons possibles de lignes (appelées « produit cartésien »). Comme la plupart des lignes de résultats contiennent des parties de lignes qui ne sont pas réellement liées, une jointure croisée est rarement utile en soi. En fait, les jointures croisées sont généralement le résultat de l’omission accidentelle de la condition de jointure.

Le résultat d’une jointure croisée peut être très important (et coûteux). Si la première table comporte N lignes et la deuxième table M lignes, le résultat est N x M lignes. Par exemple, si la première table comporte 100 lignes et la deuxième table 1 000 lignes, alors le jeu de résultats contient 100 000 lignes.

La requête suivante montre une jointure croisée :

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+

Copy

Notez que cette requête ne contient pas de clause ON et ne possède pas de filtre.

La sortie d’une jointure croisée peut être rendue plus utile en appliquant un filtre dans la clause WHERE :

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    WHERE e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Copy

Le résultat de cette jointure croisée et de ce filtre est le même que le résultat de la jointure intérieure suivante :

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Copy

Important

Bien que les deux requêtes de cet exemple produisent le même résultat lorsqu’elles utilisent la même condition (e.project_id = p.project_id) dans des clauses différentes (WHERE contre FROM ... ON ...), il est possible de construire des paires de requêtes qui utilisent la même condition mais qui ne produisent pas le même résultat.

Les exemples les plus courants concernent les jointures extérieures. Si vous exécutez table1 LEFT OUTER JOIN table2, alors pour les lignes de la table1 qui ne correspondent pas, les colonnes qui seraient issues de la table2 contiennent NULL. Un filtre comme WHERE table2.ID = table1.ID filtre les lignes dans lesquelles soit la table2.id soit la table1.id contient un NULL, tandis qu’une jointure externe explicite dans la clause FROM ... ON ... ne filtre pas les lignes avec des valeurs NULL. En d’autres termes, une jointure extérieure avec un filtre pourrait ne pas agir comme une jointure extérieure.

Jointure naturelle¶

Une jointure naturelle est utilisée lorsque deux tables contiennent des colonnes qui ont le même nom et dans lesquelles les données de ces colonnes correspondent. Dans les tables des employés et des projets présentées ci-dessus, les deux tables ont des colonnes nommées « ID_projet ». Une jointure naturelle construit implicitement la clause ON : ON projects.project_ID = employees.project_ID.

Si deux tables ont plusieurs colonnes en commun, alors toutes les colonnes communes sont utilisées dans la clause ON. Par exemple, si vous avez deux tables qui ont chacune des colonnes nommées « ville » et « province », alors une jointure naturelle construirait la clause ON suivante :

ON table2.city = table1.city AND table2.province = table1.province.

La sortie d’une jointure naturelle ne comprend qu’une seule copie de chacune des colonnes partagées. Par exemple, la requête suivante produit une jointure naturelle contenant toutes les colonnes des deux tables, sauf qu’elle omet toutes les copies de la colonne redondante project_ID, sauf une :

SELECT *
    FROM projects NATURAL JOIN employees
    ORDER BY employee_ID;
+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+

Copy

Une jointure naturelle peut être combinée avec une jointure extérieure.

Une jointure naturelle ne peut pas être combinée avec une clause ON car la condition de jointure est déjà implicite. Cependant, vous pouvez utiliser une clause WHERE pour filtrer les résultats d’une jointure naturelle.

Mise en œuvre des jointures¶

Syntaxiquement, il y a deux façons de joindre des tables :

Utilisez l’opérateur JOIN dans la sous-clause ON de la clause FROM.
Utilisez WHERE avec la clause FROM.

Snowflake recommande d’utiliser la sous-clause ON de la clause FROM. La syntaxe est plus souple. Et le fait de spécifier le prédicat dans la clause ON évite le problème du filtrage accidentel des lignes avec NULLs lors de l’utilisation d’une clause WHERE pour spécifier la condition de jointure pour une jointure extérieure.