LATERAL¶

Quando usar a LATERAL¶

LATERAL é uma ferramenta valiosa para os seguintes casos de uso:

• Encadeamento de funções de tabela em dados aninhados: Quando você precisa nivelar matrizes dentro de matrizes ou navegar em vários níveis de JSON aninhados, cada chamada de função de tabela subsequente deve fazer referência à saída da anterior. Com as junções laterais, isso é possível.

• Chamada de funções de tabela com argumentos específicos de linha: Quando uma função de tabela (como uma UDTF) precisa receber valores de entrada diferentes para cada linha da tabela da esquerda.

Para casos simples, como nivelamento de uma matriz de nível único, usar TABLE(FLATTEN(...)) sem uma junção lateral produz o mesmo resultado. As junções laterais são necessárias somente quando a exibição em linha deve fazer referência a colunas que só estão disponíveis de uma expressão anterior na cláusula FROM.

Sintaxe¶

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

Parâmetros¶

left_hand_table_expression

Esta é uma fonte de linhas, como por exemplo:

• Uma tabela.

• Uma exibição.

• Uma subconsulta.

• Uma função de tabela.

• O resultado de uma junção anterior.

inline_view

inline_view pode ser:

• Uma exibição em linha: uma exibição definida dentro da instrução, e válida apenas pela duração da instrução.

• Uma subconsulta.

• Uma função de tabela: ou uma função de tabela interna como FLATTEN ou uma função de tabela definida pelo usuário (UDTF).

inline_view não pode ser uma referência de tabela simples. Ele deve ser uma expressão que possa processar ou filtrar linhas com base nos valores da expressão da tabela à esquerda, como uma subconsulta com uma cláusula WHERE ou uma chamada de função de tabela.

Notas de uso¶

• A exibição em linha após a palavra-chave LATERAL pode referenciar colunas somente da própria exibição em linha e das tabelas à esquerda da exibição em linha na cláusula FROM.

  SELECT *
    FROM table_reference_me, LATERAL (...), table_do_not_reference_me ...
  

  Copy

• Embora a exibição em linha normalmente faça referência a uma ou mais colunas do left_hand_table_expression, não é necessário fazer isso.

• Assim como a sintaxe INNER JOIN pode usar a vírgula ou as palavras-chave INNER JOIN, uma junção lateral também pode usar a vírgula ou as palavras-chave INNER JOIN. Por exemplo:

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

  Copy

• Você não pode especificar a cláusula ON, USINGou NATURAL JOIN em:

  • Uma função de tabela lateral (diferente de SQL UDTF)

  • Uma junção lateral externa com uma função de tabela (diferente de SQL UDTF)

  Para mais detalhes, consulte as notas de uso sobre o tópico JOIN.

Exemplos¶

Consulte também Exemplo: uso de uma junção lateral com a função de tabela FLATTEN e Como usar FLATTEN para filtrar os resultados em uma cláusula WHERE.

O exemplo a seguir usa essas tabelas:

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

A consulta a seguir é uma junção lateral com uma subconsulta.

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

A seguinte instrução SQL é equivalente e produz a mesma saída. Ele usa as palavras-chave INNER JOIN em vez da vírgula na cláusula 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 em comparação a outras abordagens¶

A tabela a seguir resume quando usar LATERAL em comparação com outras abordagens: