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

Lorsque deux tables sont jointes, 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.

+------------+------------------+
| Project_ID | Project_Name     |
+------------+------------------+
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+

+-------------+------------------+------------+
| Employee_ID | Employee_Name    | Project_ID |
+-------------+------------------+------------+
|    10000001 | Terry Smith      |       1000 |
|    10000002 | Maria Inverness  |       1000 |
|    10000003 | Pat Wang         |       1001 |
|    99999999 | 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, 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 *
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID;

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)

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.

Types de jointures

Snowflake prend en charge les types de jointures suivants :

  • Jointure intérieure.

  • Jointure extérieure.

  • Jointure croisée.

  • Jointure naturelle.

Jointure intérieure

Chaque ligne d’une table est associée à la ou aux lignes correspondantes dans l’autre table.

Supposons que vous utilisiez les tables ci-dessus et la requête ci-dessous :

SELECT *
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id;

Le résultat serait similaire à ceci :

+-------------+------------------+------------+------------+------------------+
| Employee_ID | Employee_Name    | Project_ID | Project_ID | Project_name     |
+-------------+------------------+------------+------------+------------------+
|    10000001 | Terry Smith      |       1000 |       1000 | COVID-19 Vaccine |
|    10000002 | Maria Inverness  |       1001 |       1001 | Malaria Vaccine  |
|    10000003 | Pat Wang         |       1001 |       1001 | Malaria Vaccine  |
+-------------+------------------+------------+------------+------------------+

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 nouveau projet (qui n’a pas encore d’employés affectés) ou le nouvel employé (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érerait 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;

Le résultat serait similaire à ceci :

+------------------+------------------+
| Project_name     | Employee_Name    |
+------------------+------------------+
| COVID-19 Vaccine | Terry Smith      |
| Malaria Vaccine  | Maria Inverness  |
| Malaria Vaccine  | Pat Wang         |
| New Project      | NULL             |
+------------------+------------------+

Le projet nommé « Nouveau projet » 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é « Nouveau projet », le nom de l’employé est réglé sur NULL.

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

Une jointure extérieure complète énumère tous les projets et tous les employés. La sortie serait similaire à ce qui suit :

+------------------+------------------+
| Project_name     | Employee_Name    |
+------------------+------------------+
| COVID-19 Vaccine | Terry Smith      |
| Malaria Vaccine  | Maria Inverness  |
| Malaria Vaccine  | Pat Wang         |
| New Project      | 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 (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;

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 INNER JOIN employees AS e
    WHERE e.project_ID = p.project_ID;

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;

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. Par exemple, supposons que vous ayez deux tables, chacune contenant une colonne nommée « Ville » et une colonne nommée « Province ». Une jointure naturelle construit implicitement la clause ON ON table2.city = table1.city AND table2.province = table1.province.

De plus, une jointure naturelle n’inclut automatiquement qu’une copie de chacune de ces colonnes dans la sortie.

Par exemple, la requête suivante produirait une jointure naturelle contenant toutes les colonnes des deux tables, sauf qu’elle omettrait les informations redondantes sur la ville et la province de l’une des tables :

SELECT *
    FROM geographic_data_by_city_and_province NATURAL JOIN demographic_data_by_city_and_province;

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