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

Joins are useful when the data in the tables is related. For example, one table might hold information about projects, and one table might hold information about employees working on those projects.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+
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       |
+-------------+-----------------+------------+

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

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

An inner join pairs each row in one table with the matching row(s) in the other table.

The example below uses an inner join:

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

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.

The output includes only valid pairs (i.e. rows that match the join condition). In this example there is no row for the project named « NewProject » (which has no employees assigned yet) or the employee named « NewEmployee » (who hasn’t been assigned to any projects yet).

Jointure extérieure

An outer join lists all rows in the specified table, even if those rows have no match in the other table. For example, a left outer join between projects and employees lists all projects, including projects that do not yet have any employee assigned.

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

The project named « NewProject » is included in this output even though there is no matching row in the employees table. Because there are no matching employee names for the project named « NewProject », the employee name is set to NULL.

A right outer join lists all employees (regardless of project).

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

A full outer join lists all projects and all employees.

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

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

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

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

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

A natural join is used when two tables contain columns that have the same name and in which the data in those columns corresponds. In the employees and projects tables shown above, both tables have columns named « project_ID ». A natural join implicitly constructs the ON clause: ON projects.project_ID = employees.project_ID.

If two tables have multiple columns in common, then all the common columns are used in the ON clause. For example, if you had two tables that each had columns named « city » and « province », then a natural join would construct the following ON clause:

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

The output of a natural join includes only one copy of each of the shared columns. For example, the following query produces a natural join containing all columns in the two tables, except that it omits all but one copy of the redundant project_ID column:

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

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 recommends using the ON sub-clause in the FROM clause. The syntax is more flexible. And specifying the predicate in the ON clause avoids the problem of accidentally filtering rows with NULLs when using a WHERE clause to specify the join condition for an outer join.