Catégories :: Syntaxe de requête

Utilisation des jointures¶

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.

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.

CREATE TABLE projects (
  project_id INT,
  project_name VARCHAR);

INSERT INTO projects VALUES
  (1000, 'COVID-19 Vaccine'),
  (1001, 'Malaria Vaccine'),
  (1002, 'NewProject');

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR,
  project_id INT);

INSERT INTO employees VALUES
  (10000001, 'Terry Smith', 1000),
  (10000002, 'Maria Inverness', 1000),
  (10000003, 'Pat Wang', 1001),
  (10000004, 'NewEmployee', NULL);

Copy

Interroger les tables pour voir les données :

SELECT * FROM projects ORDER BY project_ID;

Copy

+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

SELECT * FROM employees ORDER BY employee_ID;

Copy

+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     |       1000 |
|    10000002 | Maria Inverness |       1000 |
|    10000003 | Pat Wang        |       1001 |
|    10000004 | NewEmployee     |       NULL |
+-------------+-----------------+------------+

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, dans ces exemples de tables, 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 le project_id. Par exemple, ce qui suit joint les tables projects et employees qui ont été créées précédemment :

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;

Copy

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

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 table3)

Copy

Dans ce pseudo-code, table2 et table3 sont jointes en premier lieu. 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 d’informations, 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

Note

Snowflake prend également en charge le ASOF JOIN pour l’analyse de données de séries temporelles. Pour plus d’informations, voir ASOF JOIN et Analyse des données de séries temporelles.

Jointure intérieure¶

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

L’exemple suivant montre 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;

Copy

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

Dans cet exemple, la sortie contient deux colonnes nommées PROJECT_ID. Une colonne PROJECT_ID provient de la table projects, et l’une autre de la table employees. Pour chaque ligne de la sortie, les valeurs des deux colonnes PROJECT_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, ni pour 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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

Le projet nommé NewProject est inclus dans cette sortie même s’il n’y a pas de ligne correspondante dans la table des employees. 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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

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, ce qu’on appelle un 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 :

Note

Cette requête ne contient pas de clause ON et ne possède pas de filtre.

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  ORDER BY p.project_ID, e.employee_ID;

Copy

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

Vous pouvez rendre la sortie d’une jointure croisée 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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

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;

Copy

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Important

Bien que les deux requêtes de cet exemple produisent la même sortie 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 la même sortie.

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 n’ont pas de correspondance, les colonnes qui seraient issues de la table2 contiennent NULL. Un filtre tel que WHERE table2.ID = table1.ID filtre les lignes dans lesquelles soit table2.id ou 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 joint deux tables sur des colonnes qui ont les mêmes noms et des types de données compatibles. Les deux tables créées précédemment, employees et projects, ont une colonne nommée project_ID. Une jointure naturelle construit implicitement la clause ON : ON projects.project_ID = employees.project_ID.

Si deux tables ont plusieurs colonnes en commun, alors une jointure naturelle utilise toutes les colonnes communes dans les clauses ON construites. Par exemple, si deux tables ont chacune des colonnes nommées city et province, alors une jointure naturelle construit la clause ON suivante :

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

Copy

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;

Copy

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

Vous pouvez combiner une jointure naturelle avec une jointure extérieure.

Vous ne pouvez pas combiner une jointure naturelle avec une clause ON`car la condition de jointure est déjà implicite. Cependant, vous pouvez utiliser une clause :code:`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 :

Utilisation de la sous-clause JOIN dans la sous-clause ON de la clause FROM.
Utilisation de la clause WHERE avec la clause FROM.

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

De plus, vous pouvez utiliser le mot-clé:code:DIRECTED pour appliquer l’ordre de jointure des tables. Lorsque vous spécifiez ce mot-clé, la première table, ou la table de gauche, est analysée avant la deuxième table, ou la table de droite. Par exemple, o1 INNER DIRECTED JOIN o2 analyse la table o1 avant la table o2. Si le mot-clé DIRECTED est ajouté, le type de jointure — par exemple, INNER ou OUTER — est nécessaire. Pour plus d’informations, voir JOIN.

Note

La jointure Directed est une fonction de prévisualisation disponible pour tous les comptes.