LATERAL¶

Quand utiliser LATERAL ?¶

LATERAL est un outil utile pour les cas d’utilisation suivants :

• Chaînage de fonctions de table sur les données imbriquées : Lorsque vous devez aplatir des tableaux dans des tableaux ou parcourir plusieurs niveaux de JSON imbriqués, chaque appel de fonction de table suivant doit faire référence à la sortie du précédent. Les jointures latérales rendent cela possible.

• Appel des fonctions de table avec des arguments spécifiques aux lignes : Lorsqu’une fonction de table (comme une UDTF) doit recevoir différentes valeurs d’entrée pour chaque ligne de la table de gauche.

Pour les cas simples tels que l’aplatissement d’un tableau à un seul niveau, utiliser TABLE(FLATTEN(...)) sans jointure latérale produit le même résultat. Les jointures latérales ne sont nécessaires que lorsque la vue en ligne doit faire référence à des colonnes qui ne sont disponibles qu’à partir d’une expression précédente dans la clause FROM.

Syntaxe¶

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

Paramètres¶

left_hand_table_expression

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.

inline_view

La inline_view peut être :

• Une vue en ligne : une vue définie dans l’instruction, et valide 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 inline_view ne peut pas être une référence de table simple. Il doit s’agir d’une expression pouvant traiter ou filtrer des lignes sur la base des valeurs de l’expression de table de gauche, telle qu’une sous-requête avec une clause WHERE ou appel d’une fonction de 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 ...
  

  Copy

• Bien que la vue en ligne fasse généralement référence à une ou plusieurs colonnes de left_hand_table_expression, il n’est pas obligatoire de le faire.

• Tout comme la syntaxe INNER JOIN peut utiliser la virgule ou les mots-clés INNER JOIN, une jointure latérale peut également utiliser la virgule ou les mots-clés INNER JOIN. Par exemple :

  FROM departments AS d INNER JOIN LATERAL (...)
  

  Copy

• Vous ne pouvez pas spécifier la clause ON, USING ou NATURAL JOIN dans :

  • Une fonction de table latérale (autre qu’une UDTF SQL)

  • Une jointure latérale externe à une fonction de table (autre qu’une UDTF SQL)

  Pour plus de détails, voir les notes sur l’utilisation dans le sujet JOIN.

Exemples¶

Voir aussi Exemple : utilisation d’une jointure latérale avec la fonction de table FLATTEN et Utilisation de FLATTEN pour filtrer les résultats dans une clause WHERE.

L’exemple suivant utilise ces tables :

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

La requête suivante est une jointure latérale 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          |
+---------------+-------------+-------------+-----------+---------------+---------------+

L’instruction SQL suivante est équivalente et produit la même sortie. Il utilise les mots-clés INNER JOIN au lieu de la virgule dans la clause FROM.

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;

CREATE OR REPLACE TABLE persons AS
  SELECT column1 AS id, PARSE_JSON(column2) AS c
    FROM VALUES
      (12712555,
       '{ "name": { "first": "John", "last": "Smith" },
          "contact": [{ "business": [
            { "type": "phone", "content": "555-1234" },
            { "type": "email", "content": "j.smith@example.com" }
          ]}]}'),
      (98127771,
       '{ "name": { "first": "Jane", "last": "Doe" },
          "contact": [{ "business": [
            { "type": "phone", "content": "555-1236" },
            { "type": "email", "content": "j.doe@example.com" }
          ]}]}');

SELECT id,
    f1.value:type::STRING AS contact_type,
    f1.value:content::STRING AS contact_details
  FROM persons p,
    LATERAL FLATTEN(INPUT => p.c, PATH => 'contact') f,
    LATERAL FLATTEN(INPUT => f.value:business) f1;

+----------+--------------+---------------------+
|       ID | CONTACT_TYPE | CONTACT_DETAILS     |
|----------+--------------+---------------------|
| 12712555 | phone        | 555-1234            |
| 12712555 | email        | j.smith@example.com |
| 98127771 | phone        | 555-1236            |
| 98127771 | email        | j.doe@example.com   |
+----------+--------------+---------------------+

LATERAL par rapport à d’autres approches¶

Le tableau suivant indique quand utiliser LATERAL par rapport à d’autres approches :