Jointure latérale

Dans une clause FROM, le mot clé LATERAL permet à une vue en ligne de référencer les colonnes d’une expression de table qui précède cette vue en ligne.

Une jointure latérale se comporte davantage comme une sous-requête corrélée que comme la plupart des jointures (JOINs). 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

Contrairement à la sortie d’une jointure non latérale, la sortie d’une jointure latérale ne comprend que les lignes générées à partir de la vue en ligne. Les lignes du côté gauche n’ont pas besoin d’être reliées au côté droit, car les lignes du côté gauche ont déjà été prises en compte en étant passées dans la vue en ligne.

Dans ce chapitre :

Syntaxe

SELECT ...
FROM <left_hand_table_expression>, LATERAL ( <inline_view> )
...

expression_table_côté_gauche

C’est une source de lignes, telles que :

  • Une table.

  • Une vue.

  • Une sous-requête.

  • Une fonction de table.

  • Le résultat d’une jointure antérieure.

vue_en_ligne

La vue_en_ligne peut être :

  • Une vue en ligne (vue définie dans l’instruction et valide uniquement pour la durée de l’instruction).

  • Une sous-requête.

  • Une fonction de table (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.

Notes sur l’utilisation

  • La vue en ligne après le mot clé LATERAL ne peut faire référence à des colonnes qu’à partir de la vue en ligne elle-même et des tables situées à gauche de la vue en ligne dans la clause FROM.

    SELECT *
        FROM table_reference_me, LATERAL (...), table_do_not_reference_me ...
    
  • Bien que la vue en ligne fasse généralement référence à une ou plusieurs colonnes de expression_table_côté_gauche, il n’est pas obligatoire de le faire.

  • Tout comme la syntaxe INNER JOIN peut utiliser la virgule ou les mots « INNER JOIN », une jointure latérale peut également utiliser la virgule ou les mots INNER JOIN.

Exemples

Cet exemple utilise 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); 

Exemple de base

Cet exemple montre un LATERAL JOIN avec 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          |
+---------------+-------------+-------------+-----------+---------------+---------------+

Exemple d’utilisation de LATERAL avec FLATTEN()

Cet exemple montre comment une jointure latérale peut utiliser la vue en ligne renvoyée par FLATTEN :

SELECT * FROM table1, LATERAL FLATTEN(...);

Commencez par mettre à jour la table « employee » afin d’inclure les données ARRAY sur lesquelles FLATTEN peut s’exécuter :

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;

Deuxièmement, exécutez une requête qui utilise FLATTEN et contient une référence à une colonne ou plusieurs colonnes dans 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"      |
+-------------+-----------+-------+----------------------+

Exemples montrant les deux syntaxes de jointure

Les instructions SQL suivantes sont équivalentes et produisent la même sortie (la sortie n’est affichée qu’une seule fois ci-dessous). La première instruction SQL ci-dessous utilise une virgule avant le mot clé LATERAL tandis que la deuxième instruction SQL utilise les mots clés INNER JOIN.

SELECT * 
    FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
    ORDER BY employee_ID;
SELECT * 
    FROM departments AS d INNER JOIN 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          |
+---------------+-------------+-------------+-----------+---------------+---------------+