Uso de junções laterais

Em uma cláusula FROM, o constructo LATERAL permite que uma exibição em linha faça referência a colunas de expressões de tabela anteriores.

Por exemplo, se a exibição em linha for uma subconsulta, a subconsulta poderá processar as linhas da tabela à esquerda da subconsulta. Por exemplo:

SELECT ...
  FROM left_hand_table_expression AS lhte,
    LATERAL (SELECT col_1 FROM table_2 AS t2 WHERE t2.col_1 = lhte.col_1);
Copy

Esse comportamento é um pouco semelhante a uma subconsulta correlacionada. A subconsulta após a palavra-chave LATERAL é semelhante à própria subconsulta correlacionada, e a left_hand_table_expression é semelhante à consulta externa. Uma junção lateral, diferentemente de uma subconsulta correlacionada, pode retornar várias linhas, cada uma das quais pode ter várias colunas.

Outros tipos de junções não passam diretamente as linhas da expressão da tabela da esquerda para a expressão da tabela da direita para processamento.

Um uso comum de uma junção lateral é combiná-la com uma chamada à função de tabela FLATTEN para processar uma estrutura de dados complexa, como um tipo de dados ARRAY ou VARIANT, e extrair os valores. Para obter um exemplo, consulte LATERAL.

Diferentemente da saída de outros tipos de junções, a saída de uma junção lateral inclui apenas as linhas geradas a partir da exibição em linha (a subconsulta); depois que as linhas da subconsulta são geradas, elas não são unidas de forma cruzada a todas as linhas da tabela no lado esquerdo.

Neste tópico:

Terminologia

Considere o seguinte fragmento de código:

... FROM te1, LATERAL iv1 ...
Copy

O lado esquerdo da junção lateral é uma expressão de tabela (te1). O lado direito da junção lateral é uma exibição em linha (iv1).

  • Expressão de tabela: neste tópico, a expressão de tabela no lado esquerdo de uma junção lateral, como a expressão de tabela acima denominada te1, pode ser praticamente qualquer expressão válida que seja avaliada como uma tabela. Por exemplo:

    • Uma tabela.

    • Uma exibição.

    • Uma subconsulta.

    • A saída de uma função de tabela.

    • O resultado de uma junção anterior (uma junção lateral ou outro tipo de junção).

  • Exibição em linha: neste tópico, a expressão no lado direito de uma junção lateral (neste caso, iv1) é chamada de “exibição em linha”. Nesse contexto, uma exibição em linha válida pode ser uma das seguintes:

    • Uma exibição definida dentro da instrução e válida somente durante a 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).

    A exibição em linha não pode ser uma tabela.

  • Junção cruzada: neste tópico, o termo “junção cruzada” não se refere apenas a junções cruzadas explícitas, mas também a junções internas e externas, incluindo todas as variações (junções naturais, junções externas à esquerda/direita/completas etc.).

Uma atualização sobre junções

Uma junção é um processo de duas etapas. Primeiro, o servidor emparelha duas linhas, que geralmente estão em tabelas diferentes e que quase sempre estão relacionadas de alguma forma. Em segundo lugar, o servidor une as colunas de cada linha do par em uma única linha.

Muitas das consultas de exemplo usam os dados mostrados abaixo:

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

Aqui está uma junção interna simples (não é uma junção lateral):

SELECT *
  FROM departments AS d, employees AS e
  WHERE e.department_ID = d.department_ID
  ORDER BY employee_ID;
Copy
+---------------+-------------+-------------+-----------+---------------+---------------+
| 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          |
+---------------+-------------+-------------+-----------+---------------+---------------+

Como você pode ver, as linhas são emparelhadas com base nos IDs de departamento correspondente.

A junção pega as colunas de duas linhas de entrada correspondentes (“emparelhadas”) e gera uma linha de saída que contém todas as colunas de ambas as linhas de entrada. (É claro que, ao modificar a lista SELECT, você pode alterar as colunas; no entanto, no caso mais simples, todas as colunas de entrada são incluídas na saída)

Uma junção lateral emparelha as linhas de forma diferente. Entretanto, a segunda metade do processo, a “junção” de linhas emparelhadas, é semelhante: a linha de saída conterá (quase sempre) uma ou mais colunas de cada membro do par de linhas de entrada.

Como uma junção lateral emparelha linhas

Uma junção lateral se comporta de forma diferente de outros tipos de junção. Uma junção lateral se comporta como se o servidor executasse um loop semelhante ao seguinte:

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the current row in the LHT
Copy

Esta seção se concentra na parte de “emparelhamento” do processo, que é diferente para junções laterais.

A construção LATERAL permite que uma exibição em linha no lado direito da junção lateral faça referência a colunas de uma expressão de tabela que esteja fora da exibição. (No exemplo abaixo, a “exibição em linha” é, na verdade, 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;
Copy
+---------------+-------------+-------------+-----------+---------------+---------------+
| 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          |
+---------------+-------------+-------------+-----------+---------------+---------------+

Neste exemplo, a cláusula WHERE na subconsulta à direita refere-se a um valor da tabela à esquerda.

As diferenças entre uma junção lateral e uma junção cruzada são muito maiores do que o simples acesso às colunas. Os próximos parágrafos contrastam esses dois tipos de junções, começando com a junção cruzada tradicional.

Uma junção cruzada combina cada linha da tabela à esquerda com cada linha da tabela à direita. O resultado é um produto cartesiano.

Conceitualmente, uma junção cruzada é semelhante a um loop aninhado, como no pseudocódigo abaixo:

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
Copy

Se a tabela à esquerda tiver n linhas e a tabela à direita tiver m* linhas, o resultado da junção cruzada terá n × m linhas. Por exemplo, se a tabela à esquerda tiver 1.000 linhas e a tabela à direita tiver 100 linhas, o resultado da junção interna será 100.000 linhas. Isso é exatamente o que se espera de loops aninhados; se o loop externo for executado 1.000 vezes e o loop interno for executado 100 vezes por iteração do loop externo, a instrução mais interna será executada 100.000 vezes. (É claro que os programadores de SQL raramente escrevem junções cruzadas puras sem nenhuma condição de junção na cláusula FROM ou na cláusula WHERE)

Uma junção lateral emparelha os registros de forma muito diferente. Aqui está o pseudocódigo para a implementação de uma junção lateral:

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
Copy

A junção lateral tem apenas um loop, não dois loops aninhados, o que altera a saída.

Para a junção cruzada, a saída foi de 100.000 linhas. Para uma junção lateral com a mesma tabela de 1.000 linhas no lado esquerdo e usando uma exibição em linha do lado direito (como uma subconsulta) que emite uma linha de saída por linha de entrada, a saída da junção lateral será de 1.000 linhas, não de 100.000 linhas.

Você pode pensar em uma junção lateral da seguinte forma: para cada linha de entrada da tabela da esquerda, a exibição em linha da direita produz 0 ou mais linhas. Cada uma dessas linhas de saída da subconsulta é então unida à linha de entrada (e não à tabela inteira no lado esquerdo) para produzir uma linha que contenha as colunas selecionadas da subconsulta e as colunas da linha de entrada LHT.

A exibição em linha no lado direito de uma junção lateral não produz necessariamente uma linha de saída exata para cada linha de entrada. Para qualquer linha de entrada, a saída do lado direito pode ser 0 linha, 1 linha ou várias linhas. Cada uma dessas linhas de saída será unida às colunas da linha de entrada original.

Se a subconsulta não produzir exatamente uma linha de saída para cada linha de entrada, a junção lateral não necessariamente produzirá exatamente o mesmo número de linhas que há na tabela da esquerda. Se a tabela à esquerda tiver 1.000 linhas e a exibição em linha produzir 2 linhas de saída para cada linha de entrada, o resultado da junção lateral será 2.000 linhas.

Em cada um dos exemplos de junção lateral até agora, não havia nenhuma cláusula ON ou WHERE na consulta externa para emparelhar os registros. O emparelhamento (se houver) é feito pela exibição em linha com base na linha individual passada para a exibição em linha. Isso fica razoavelmente claro quando a exibição em linha é uma subconsulta com uma cláusula WHERE. Isso não é necessariamente tão óbvio em outros casos, como quando a expressão do lado direito é uma função de tabela em vez de uma subconsulta. (Um exemplo posterior mostra uma expressão à direita que usa a função de tabela FLATTEN em vez de uma subconsulta)

Os leitores que são fluentes em subconsultas correlacionadas ou em junções de funções de tabela podem achar as seguintes comparações úteis para entender como as junções laterais diferem das junções cruzadas. Os leitores que não estiverem familiarizados com subconsultas correlacionadas ou funções de tabela de junção podem ignorar essas seções.

Semelhanças entre subconsultas correlacionadas e junções laterais

Uma junção lateral é semelhante a uma subconsulta correlacionada:

  • Em uma subconsulta correlacionada, a subconsulta é executada uma vez para cada linha da consulta externa.

  • Em uma junção lateral, a subconsulta do lado direito (exibição em linha) é executada uma vez para cada linha na expressão da tabela do lado esquerdo.

No entanto, as subconsultas correlacionadas e as junções laterais não são a mesma coisa. Uma diferença é que, em uma junção lateral, a subconsulta pode gerar mais de uma linha de saída por linha de entrada, e cada linha de saída pode conter várias colunas. As subconsultas correlacionadas retornam apenas uma linha de saída por linha de entrada, e cada linha de saída deve conter apenas uma coluna.

Semelhanças entre as funções de tabela com junção e as junções laterais

Uma junção lateral é semelhante a uma “junção” entre uma tabela e uma função de tabela definida pelo usuário (UDTF). Por exemplo, considere a seguinte instrução SQL:

SELECT *
  FROM t1, TABLE(udtf2(t1.col1))
  ...
  ;
Copy

O pseudocódigo para implementar a junção entre a tabela e a UDTF é o seguinte:

for each row in left_hand_table LHT:
  udtf2(row) -- that is, call udtf2() with the value(s) from the LHT row.
Copy

Isso é essencialmente idêntico ao código para implementar uma junção lateral:

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the LHT row
Copy

Exemplo: uso de uma junção lateral com a função de tabela FLATTEN

As junções laterais são usadas com frequência com a função de tabela FLATTEN integrada. A função FLATTEN é frequentemente usada com tipos de dados que podem armazenar vários valores (como ARRAY, VARIANT e OBJECT). Por exemplo, uma matriz normalmente contém vários valores. Da mesma forma, uma coluna VARIANT pode conter um valor de dados JSON, que pode conter um dicionário (hash) ou uma lista. (E isso, por sua vez, pode conter outros valores)

Você pode criar valores ARRAY da seguinte forma:

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

A função FLATTEN pode extrair valores de dentro desses valores. A função usa uma única expressão do tipo VARIANT, OBJECT ou ARRAY, e extrai os valores dessa expressão em um conjunto de linhas (0 ou mais linhas, cada uma contendo 1 ou mais colunas). Esse conjunto de linhas é equivalente a uma exibição ou a uma tabela. Essa exibição existe apenas durante o período da instrução em que é definida, por isso é comumente chamada de “exibição em linha”.

O exemplo a seguir usa FLATTEN para extrair valores de uma matriz (sem usar uma junção lateral):

SELECT index, value AS project_name
  FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('project1', 'project2')));
Copy
+-------+--------------+
| INDEX | PROJECT_NAME |
|-------+--------------|
|     0 | "project1"   |
|     1 | "project2"   |
+-------+--------------+

A exibição em linha gerada por FLATTEN pode ser (mas não precisa) usada com a palavra-chave LATERAL. Por exemplo:

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

Quando usada com a palavra-chave LATERAL, a exibição em linha pode conter uma referência a colunas em uma tabela que a precede:

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;
Copy
+-------------+-----------+-------+----------------------+
| 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"      |
+-------------+-----------+-------+----------------------+
Linguagem: Português