Utilisation de jointures latérales¶
Dans une clause FROM, la construction LATERAL permet à une vue en ligne de faire référence aux colonnes des expressions de table précédentes.
Par exemple, si la vue en ligne est une sous-requête, la sous-requête peut traiter les lignes de la table située à gauche de la sous-requête. Par exemple :
SELECT ...
FROM left_hand_table_expression AS lhte,
LATERAL (SELECT col_1 FROM table_2 AS t2 WHERE t2.col_1 = lhte.col_1);
Ce comportement est quelque peu similaire à celui d’une sous-requête corrélée. La sous-requête après le mot-clé LATERAL est similaire à la sous-requête corrélée elle-même, et la left_hand_table_expression est similaire à la requête extérieure. Une jointure latérale, contrairement à une sous-requête corrélée, peut renvoyer plusieurs lignes, chacune pouvant comporter plusieurs colonnes.
Les autres types de jointures ne transmettent pas directement les lignes de l’expression de la table de gauche à l’expression de la table de droite pour traitement.
Une utilisation courante d’une jointure latérale consiste à la combiner avec un appel à la fonction de table FLATTEN pour traiter une structure de données complexe, telle qu’un type de données ARRAY ou VARIANT, et en extraire les valeurs. Pour un exemple, voir LATERAL.
Contrairement à la sortie d’autres types de jointures, la sortie d’une jointure latérale n’inclut que les lignes générées à partir de la vue en ligne (la sous-requête) ; une fois que les lignes de la sous-requête sont générées, elles ne sont pas croisées avec toutes les lignes de la table du côté gauche.
Dans ce chapitre :
Terminologie¶
Considérez le fragment de code suivant :
... FROM te1, LATERAL iv1 ...
Le côté gauche de la jointure latérale est une expression de table (te1). La partie droite de la jonction latérale est une vue en ligne (iv1).
Expression de table : dans ce sujet, l’expression de table du côté gauche d’une jointure latérale, telle que l’expression de table ci-dessus nommée
te1, peut être presque n’importe quelle expression valide qui évalue une table. Par exemple :Une table.
Une vue.
Une sous-requête.
La sortie d’une fonction de table.
Le résultat d’une jointure antérieure (une jointure latérale ou un autre type de jointure).
Vue en ligne : dans cette référence, l’expression située à droite d’une jointure latérale (dans ce casse,
iv1) est appelée « vue en ligne » Dans ce contexte, une vue en ligne valide peut être l’une des suivantes :Une vue définie dans l’instruction et valable uniquement pour la durée de l’instruction.
Une sous-requête.
Une fonction de table : soit une fonction de table intégrée telle que FLATTEN ou une fonction de table définie par l’utilisateur (UDTF).
La vue en ligne ne peut pas être une table.
Jointure croisée : dans cette référence, le terme « jointure croisée » se réfère non seulement aux jointures croisées explicites, mais aussi aux jointures internes et aux jointures externes, y compris toutes les variantes (jointures naturelles, jointures externes gauche/droite/complètes, et ainsi de suite).
Un rappel sur les jointures¶
Une jointure est un processus en deux étapes. Tout d’abord, le serveur associe deux lignes, qui se trouvent généralement dans des tables différentes et qui sont presque toujours liées d’une manière ou d’une autre. Ensuite, le serveur réunit les colonnes de chaque ligne de la paire en une seule ligne.
La plupart des exemples de requêtes utilisent les données présentées ci-dessous :
CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
department_ID INTEGER, project_names ARRAY);
INSERT INTO departments (department_ID, name) VALUES
(1, 'Engineering'),
(2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
(101, 'Richards', 1),
(102, 'Paulson', 1),
(103, 'Johnson', 2);
Voici une simple jointure interne (il ne s’agit pas d’une jointure latérale) :
SELECT *
FROM departments AS d, employees AS e
WHERE e.department_ID = d.department_ID
ORDER BY employee_ID;
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
| 1 | Engineering | 101 | Richards | 1 | NULL |
| 1 | Engineering | 102 | Paulson | 1 | NULL |
| 2 | Support | 103 | Johnson | 2 | NULL |
+---------------+-------------+-------------+-----------+---------------+---------------+
Comme vous pouvez le constater, les lignes sont appariées sur la base des IDs de département correspondants.
La jointure prend les colonnes de deux lignes d’entrée correspondantes (« appariées ») et génère une ligne de sortie qui contient toutes les colonnes des deux lignes d’entrée. (Bien sûr, en modifiant la liste SELECT, vous pouvez changer les colonnes ; cependant, dans le cas le plus simple, toutes les colonnes d’entrée sont incluses dans la sortie)
Une jointure latérale associe les lignes différemment. Toutefois, la seconde moitié du processus, la « jointure » des lignes appariées, est similaire : la ligne de sortie contiendra (presque toujours) une ou plusieurs colonnes de chaque membre de la paire de lignes d’entrée.
Comment une jointure latérale associe des lignes¶
Une jointure latérale se comporte différemment des autres types de jointures. Une jointure latérale se comporte comme si le serveur exécutait une boucle similaire à celle-ci :
for each row in left_hand_table LHT:
execute right_hand_subquery RHS using the values from the current row in the LHT
Cette section se concentre sur la partie « appariement » du processus, qui est différente pour les jonctions latérales.
La construction LATERAL permet à une vue en ligne du côté droit de la jointure latérale de faire référence aux colonnes d’une expression de table se trouvant en dehors de la vue. (Dans l’exemple ci-dessous, la « vue en ligne » est en fait une sous-requête)
SELECT *
FROM departments AS d,
LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
ORDER BY employee_ID;
+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
| 1 | Engineering | 101 | Richards | 1 | NULL |
| 1 | Engineering | 102 | Paulson | 1 | NULL |
| 2 | Support | 103 | Johnson | 2 | NULL |
+---------------+-------------+-------------+-----------+---------------+---------------+
Dans cet exemple, la clause WHERE de la sous-requête de droite fait référence à une valeur de la table de gauche.
Les différences entre une jointure latérale et une jointure croisée vont bien au-delà du simple accès aux colonnes. Les paragraphes suivants opposent ces deux types de jointure, en commençant par la jointure croisée traditionnelle.
Une jointure croisée combine chaque ligne de la table de gauche avec chaque ligne de la table de droite. Le résultat est un produit cartésien.
D’un point de vue conceptuel, une jointure croisée est similaire à une boucle imbriquée, comme dans le pseudo-code ci-dessous :
for each row in left_hand_table LHT:
for each row in right_hand_table RHT:
concatenate the columns of the RHT to the columns of the LHT
Si la table de gauche comporte n lignes et la table de droite m* lignes, le résultat de la jointure croisée comporte n x m lignes. Par exemple, si la table de gauche comporte 1000 lignes et la table de droite 100 lignes, le résultat de la jointure interne est de 100 000 lignes. C’est exactement ce que vous attendez des boucles imbriquées ; si la boucle externe s’exécute 1000 fois et que la boucle interne s’exécute 100 fois par itération de la boucle externe, l’instruction la plus proche s’exécute 100 000 fois. (Bien entendu, les programmeurs utilisant le langage SQL écrivent rarement des jointures croisées pures sans aucune condition de jointure dans la clause FROM ou WHERE)
Une jointure latérale associe les enregistrements de manière très différente. Voici le pseudo-code pour l’implémentation d’une jointure latérale :
for each row in left_hand_table LHT:
execute right_hand_subquery RHS using the values from the LHT row,
and concatenate LHT columns to RHS columns
La jointure latérale ne comporte qu’une seule boucle, et non deux boucles imbriquées, ce qui modifie la sortie.
Pour la jointure croisée, la sortie était de 100 000 lignes. Pour une jointure latérale avec la même table de 1000 lignes du côté gauche, et en utilisant une vue en ligne du côté droit (telle qu’une sous-requête) qui émet une ligne de sortie par ligne d’entrée, la sortie de la jointure latérale sera de 1000 lignes, et non de 100 000 lignes.
Vous pouvez concevoir une jointure latérale de la manière suivante : pour chaque ligne d’entrée de la table de gauche, la vue en ligne de droite produit 0 ligne ou plus. Chacune de ces lignes de sortie de la sous-requête est ensuite jointe à la ligne d’entrée (et non à l’ensemble de la table du côté gauche) pour produire une ligne qui contient les colonnes sélectionnées dans la sous-requête et les colonnes de la ligne d’entrée LHT.
La vue en ligne du côté droit d’une jointure latérale ne produit pas nécessairement une ligne de sortie pour chaque ligne d’entrée. Pour une ligne d’entrée, la sortie du côté droit peut être 0 ligne, 1 ligne ou plusieurs lignes. Chacune de ces lignes de sortie sera jointe aux colonnes de la ligne d’entrée d’origine.
Si la sous-requête ne produit pas exactement une ligne en sortie pour chaque ligne en entrée, la jointure latérale ne produit pas nécessairement autant de lignes qu’il y en a dans la table de gauche. Si la table de gauche compte 1000 lignes et que la vue en ligne produit 2 lignes de sortie pour chaque ligne d’entrée, le résultat de la jointure latérale est de 2000 lignes.
Dans chacun des exemples de jointure latérale présentés jusqu’à présent, il n’y avait pas de clause ON ou WHERE dans la requête extérieure pour apparier les enregistrements. L’appariement (le cas échéant) est effectué par la vue en ligne sur la base de la ligne individuelle transmise à la vue en ligne. Ceci est assez clair lorsque la vue en ligne est une sous-requête avec une clause WHERE. Ce n’est pas nécessairement aussi évident dans d’autres casse, par exemple lorsque l’expression de droite est une fonction de table plutôt qu’une sous-requête. (Un exemple ultérieur montre une expression de droite qui utilise la fonction de table FLATTEN au lieu d’une sous-requête.)
Les lecteurs qui maîtrisent les sous-requêtes corrélées ou les jointures de fonctions de table trouveront peut-être les comparaisons suivantes utiles pour comprendre en quoi les jointures latérales diffèrent des jointures croisées. Les lecteurs qui ne sont pas familiarisés avec les sous-requêtes corrélées ou les fonctions de table de jointure peuvent ignorer ces sections.
Similitudes entre les fonctions de table de jointure et les jointures latérales¶
Une jointure latérale est similaire à une « jointure » entre une table et une fonction définie par l’utilisateur (UDTF). Par exemple, considérons l’instruction SQL suivante :
SELECT *
FROM t1, TABLE(udtf2(t1.col1))
...
;
Le pseudo-code pour l’implémentation de la jointure entre la table et le site UDTF est le suivant :
for each row in left_hand_table LHT:
udtf2(row) -- that is, call udtf2() with the value(s) from the LHT row.
Ce code est essentiellement identique à celui de la mise en œuvre d’une jointure latérale :
for each row in left_hand_table LHT:
execute right_hand_subquery RHS using the values from the LHT row
Exemple : utilisation d’une jointure latérale avec la fonction de table FLATTEN¶
Les jointures latérales sont fréquemment utilisées avec la fonction de table intégrée FLATTEN. La fonction FLATTEN est souvent utilisée avec des types de données pouvant stocker plusieurs valeurs (tels que ARRAY, VARIANT et OBJECT). Par exemple, un tableau contient généralement plusieurs valeurs. De même, une colonne VARIANT peut contenir une valeur de données JSON, qui peut contenir un dictionnaire (hachage) ou une liste. (Et qui, à son tour, peut contenir d’autres valeurs)
Vous pouvez créer les valeurs ARRAY comme suit :
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs')
WHERE employee_ID = 101;
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
WHERE employee_ID = 102;
La fonction FLATTEN peut extraire des valeurs à l’intérieur de ces valeurs. La fonction prend une seule expression de type VARIANT, OBJECT ou ARRAY, et extrait les valeurs de cette expression dans un ensemble de lignes (0 ou plusieurs lignes, chacune contenant 1 ou plusieurs colonnes). Cet ensemble de lignes est équivalent à une vue ou à une table. Cette vue n’existe que pour la durée de l’instruction dans laquelle elle est définie, c’est pourquoi elle est communément appelée « vue en ligne ».
L’exemple suivant utilise FLATTEN pour extraire des valeurs d’un tableau (sans utiliser de jointure latérale) :
SELECT index, value AS project_name
FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('project1', 'project2')));
+-------+--------------+
| INDEX | PROJECT_NAME |
|-------+--------------|
| 0 | "project1" |
| 1 | "project2" |
+-------+--------------+
La vue en ligne générée par FLATTEN peut être utilisée (mais ce n’est pas une exigence) avec le mot-clé LATERAL. Par exemple :
SELECT * FROM table1, LATERAL FLATTEN(...);
Lorsqu’elle est utilisée avec le mot-clé LATERAL, la vue en ligne peut contenir une référence aux colonnes d’une table qui la précède :
SELECT emp.employee_ID, emp.last_name, index, value AS project_name
FROM employees AS emp,
LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
ORDER BY employee_ID;
+-------------+-----------+-------+----------------------+
| EMPLOYEE_ID | LAST_NAME | INDEX | PROJECT_NAME |
|-------------+-----------+-------+----------------------|
| 101 | Richards | 0 | "Materialized Views" |
| 101 | Richards | 1 | "UDFs" |
| 102 | Paulson | 0 | "Materialized Views" |
| 102 | Paulson | 1 | "Lateral Joins" |
+-------------+-----------+-------+----------------------+