Categorias:

Sintaxe de consulta

Como trabalhar com junções

Uma junção combina linhas de duas tabelas para criar uma nova linha combinada que pode ser usada na consulta.

Neste tópico:

Introdução

As junções são úteis quando os dados nas tabelas estão relacionados. Por exemplo, uma tabela pode conter informações sobre projetos e outra pode conter informações sobre os funcionários que trabalham nesses projetos.

SELECT * FROM projects ORDER BY project_ID;
+------------+------------------+
| PROJECT_ID | PROJECT_NAME     |
|------------+------------------|
|       1000 | COVID-19 Vaccine |
|       1001 | Malaria Vaccine  |
|       1002 | NewProject       |
+------------+------------------+
Copy
SELECT * FROM employees ORDER BY employee_ID;
+-------------+-----------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|-------------+-----------------+------------|
|    10000001 | Terry Smith     | 1000       |
|    10000002 | Maria Inverness | 1000       |
|    10000003 | Pat Wang        | 1001       |
|    10000004 | NewEmployee     | NULL       |
+-------------+-----------------+------------+
Copy

As duas tabelas unidas geralmente contêm uma ou mais colunas em comum para que as linhas de uma tabela possam ser associadas com as linhas correspondentes na outra tabela. Por exemplo, cada linha na tabela de projetos pode ter um número de ID de projeto único, e cada linha na tabela de funcionários pode incluir o número de ID do projeto ao qual o funcionário está atualmente designado.

A operação de junção especifica (explícita ou implicitamente) como relacionar as linhas em uma tabela com as linhas correspondentes na outra tabela, normalmente referenciando a(s) coluna(s) comum(ns), como ID de projeto. Por exemplo, isto une as tabelas de projeto e funcionários mostradas acima:

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+
Copy

Embora uma única operação de junção possa unir apenas duas tabelas, as junções podem ser encadeadas juntas. O resultado de uma junção é um objeto tipo tabela, e esse objeto tipo tabela pode então ser unido a outro objeto tipo tabela. Conceitualmente, a ideia é semelhante ao seguinte (esta não é a sintaxe real):

table1 join (table2 join table 3)
Copy

Neste pseudocódigo, table2 e table3 são unidas primeiro. A tabela que resulta dessa junção é então unida com table1.

As junções podem ser aplicadas não apenas a tabelas, mas também a outros objetos semelhantes a tabelas. Você pode unir:

  • Uma tabela.

  • Uma exibição (materializada ou não).

  • Um literal de tabela.

  • Uma expressão que é avaliada ao equivalente de uma tabela (contendo uma ou mais colunas e zero ou mais linhas). Por exemplo:

    • O conjunto de resultados retornado por uma função de tabela.

    • O conjunto de resultados definido devolvido por uma subconsulta que retorna uma tabela.

Quando este tópico se referir a unir uma tabela, geralmente significa unir qualquer objeto semelhante a uma tabela.

Nota

O Snowflake pode melhorar o desempenho eliminando junções desnecessárias. Para obter mais detalhes, consulte Compreensão de como o Snowflake consegue eliminar junções redundantes.

Tipos de junções

O Snowflake suporta os seguintes tipos de junções:

  • Junção interna.

  • Junção externa.

  • Junção cruzada.

  • Junção natural.

  • ASOF JOIN.

Junção interna

Uma junção interna emparelha cada linha em uma tabela com a(s) linha(s) correspondente(s) na outra tabela.

O exemplo abaixo usa uma junção interna:

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_id = p.project_id
    ORDER BY p.project_ID, e.employee_ID;
+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     | 1000       |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness | 1000       |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        | 1001       |
+------------+------------------+-------------+-----------------+------------+
Copy

Neste exemplo, a tabela de saída contém duas colunas chamadas “Project_ID“. Uma coluna Project_ID é da tabela de projetos, e outra é da tabela de funcionários. Para cada linha na tabela de saída, os valores nas duas colunas Project_ID correspondem porque a consulta especificou e.project_id = p.project_id.

A saída inclui apenas pares válidos (ou seja, linhas que correspondem à condição de junção). Neste exemplo, não há linha para o projeto denominado “NewProject“ (que ainda não tem funcionários designados) ou o funcionário denominado “NewEmployee“ (que ainda não foi designado para nenhum projeto).

Junção externa

Uma junção externa lista todas as linhas da tabela especificada, mesmo que essas linhas não tenham correspondência na outra tabela. Por exemplo, uma junção externa esquerda entre projetos e funcionários lista todos os projetos, inclusive os projetos que ainda não têm nenhum funcionário designado.

SELECT p.project_name, e.employee_name
    FROM projects AS p LEFT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+
Copy

O projeto denominado “NewProject“ está incluído nesta saída, embora não haja uma linha correspondente na tabela de funcionários. Como não há nomes correspondentes de funcionários para o projeto denominado “NewProject“, o nome do funcionário é definido como NULL.

Uma junção externa direita lista todos os funcionários (independentemente do projeto).

SELECT p.project_name, e.employee_name
    FROM projects AS p RIGHT OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+
Copy

Uma junção externa completa lista todos os projetos e todos os funcionários.

SELECT p.project_name, e.employee_name
    FROM projects AS p FULL OUTER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_name, e.employee_name;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+
Copy

Junção cruzada

Uma junção cruzada combina cada linha da primeira tabela com cada linha da segunda tabela, criando todas as combinações possíveis de linhas (chamadas de “produto cartesiano”). Como a maioria das linhas de resultado contém partes de linhas que não estão realmente relacionadas, uma junção cruzada raramente é útil por si só. Na verdade, as junções cruzadas são geralmente o resultado de se omitir acidentalmente a condição de junção.

O resultado de uma junção cruzada pode ser muito grande (e caro). Se a primeira tabela tiver N linhas e a segunda tabela tiver M linhas, então o resultado são N x M linhas. Por exemplo, se a primeira tabela tiver 100 linhas e a segunda tabela tiver 1000 linhas, então o conjunto de resultados contém 100.000 linhas.

A consulta a seguir mostra uma junção cruzada:

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+
Copy

Note que esta consulta não contém nenhuma cláusula ON e nenhum filtro.

A saída de uma junção cruzada pode se tornar mais útil aplicando um filtro na cláusula WHERE:

SELECT p.project_name, e.employee_name
    FROM projects AS p CROSS JOIN employees AS e
    WHERE e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+
Copy

O resultado desta junção cruzada e filtro é o mesmo que o resultado da seguinte junção interna:

SELECT p.project_name, e.employee_name
    FROM projects AS p INNER JOIN employees AS e
        ON e.project_ID = p.project_ID
    ORDER BY p.project_ID, e.employee_ID;
+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+
Copy

Importante

Embora as duas consultas neste exemplo produzam a mesma saída quando utilizam a mesma condição (e.project_id = p.project_id) em cláusulas diferentes (WHERE vs. FROM ... ON ...), é possível construir pares de consultas que utilizam a mesma condição mas que não produzem a mesma saída.

Os exemplos mais comuns envolvem junções externas. Se você executar table1 LEFT OUTER JOIN table2, então para as linhas de table1 que não têm correspondência, as colunas que teriam vindo de table2 contêm NULL. Um filtro como WHERE table2.ID = table1.ID filtra linhas nas quais ou table2.id ou table1.id contém um NULL, enquanto que uma junção externa explícita na cláusula FROM ... ON ... não filtra linhas com valores NULL. Em outras palavras, uma junção externa com um filtro pode na verdade não agir como uma junção externa.

Junção natural

Uma junção natural é utilizada quando duas tabelas contêm colunas que têm o mesmo nome e nas quais os dados dessas colunas correspondem. Nas tabelas de funcionários e projetos mostradas acima, ambas as tabelas têm colunas denominadas “project_ID“. Uma junção natural constrói implicitamente a cláusula ON: ON projects.project_ID = employees.project_ID.

Se duas tabelas têm várias colunas em comum, então todas as colunas comuns são usadas na cláusula ON. Por exemplo, se você tivesse duas tabelas, cada qual com colunas chamadas “city” e “province”, então uma junção natural construiria a seguinte cláusula ON:

ON table2.city = table1.city AND table2.province = table1.province.

A saída de uma junção natural inclui apenas uma cópia de cada uma das colunas compartilhadas. Por exemplo, a seguinte consulta produz uma junção natural contendo todas as colunas das duas tabelas, exceto que omite todas as colunas project_ID redundantes, com exceção de uma cópia:

SELECT *
    FROM projects NATURAL JOIN employees
    ORDER BY employee_ID;
+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+
Copy

Uma junção natural pode ser combinada com uma junção externa.

Uma junção natural não pode ser combinada com uma cláusula ON porque a condição de junção já está implícita. Entretanto, você pode usar uma cláusula WHERE para filtrar os resultados de uma junção natural.

Implementação de junções

Sintaticamente, há duas formas de unir tabelas:

  • Use o operador JOIN na subcláusula ON da cláusula FROM.

  • Use a cláusula WHERE com a cláusula FROM.

A Snowflake recomenda o uso da subcláusula ON na cláusula FROM. A sintaxe é mais flexível. E especificar o predicado na cláusula ON evita o problema de filtrar acidentalmente as linhas com NULLs ao usar uma cláusula WHERE para especificar a condição de junção para uma junção externa.

Linguagem: Português