JOIN¶

Sintaxe¶

Use uma das seguintes opções:

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

SELECT ...
FROM <object_ref1> [
                     {
                       NATURAL [
                                 {
                                   INNER
                                   | { LEFT | RIGHT | FULL } [ OUTER ]
                                 }
                                 [ DIRECTED ]
                               ]
                       | CROSS  [ DIRECTED ]
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Parâmetros¶

object_ref1 e object_ref2

Cada referência de objeto é uma fonte de dados em tabela ou forma de tabela.

JOIN

Use a palavra-chave JOIN para especificar que as tabelas devem ser unidas. Combine JOIN com outras palavras-chave relacionadas à junção (por exemplo, INNER ou OUTER) para especificar o tipo de junção.

A semântica das junções é a seguinte (para brevidade, este tópico usa o1 e o2 para object_ref1 e object_ref2, respectivamente).

Tipo de junção	Semântica
o1 INNER JOIN o2	Para cada linha de o1, é produzida uma linha para cada linha de o2 correspondente de acordo com a subcláusula ON condition. (Você também pode usar uma vírgula para especificar uma junção interna. Para obter um exemplo, consulte a seção de exemplos abaixo.) Se você usar INNER JOIN sem a cláusula ON, ou se usar uma vírgula sem uma cláusula WHERE, o resultado será o mesmo que usar CROSS JOIN: um produto cartesiano; cada linha de o1 emparelhada com cada linha de o2.
o1 LEFT OUTER JOIN o2	O resultado da junção interna é aumentado com uma linha para cada linha de o1 que não tenha correspondências em o2. As colunas de resultados referentes a o2 contêm nulos.
o1 RIGHT OUTER JOIN o2	O resultado da junção interna é aumentado com uma linha para cada linha de o2 que não tenha correspondências em o1. As colunas de resultados referentes a o1 contêm nulos.
o1 FULL OUTER JOIN o2	Retorna todas as linhas unidas, mais uma linha para cada linha sem correspondência do lado esquerdo (estendida com nulos à direita), mais uma linha para cada linha sem correspondência do lado direito (estendida com nulos à esquerda).
o1 CROSS JOIN o2	Para cada combinação possível de linhas de o1 e o2 (ou seja, produto cartesiano), a tabela unida contém uma linha que consiste em todas as colunas em o1 seguida de todas as colunas em o2. Um CROSS JOIN não pode ser combinado com uma cláusula ON condition. No entanto, você pode usar uma cláusula WHERE para filtrar os resultados.
o1 NATURAL JOIN o2	A NATURAL JOIN é idêntica a uma JOIN explícita nas colunas comuns das duas tabelas, exceto que as colunas comuns são incluídas apenas uma vez na saída. (Uma junção natural considera que colunas com o mesmo nome, mas em tabelas diferentes, contêm dados correspondentes). Para exemplos, consulte a seção de exemplos. A NATURAL JOIN pode ser combinada com um OUTER JOIN. Uma NATURAL JOIN não pode ser combinada com uma cláusula ON condition porque a condição JOIN já está implícita. No entanto, você pode usar uma cláusula WHERE para filtrar os resultados.

A palavra-chave DIRECTED especifica uma junção direcionada, que impõe a ordem de junção das tabelas. A primeira tabela, ou da esquerda, é verificada antes da segunda tabela, ou da direita. Por exemplo, o1 INNER DIRECTED JOIN o2 examina a tabela o1 antes da tabela o2. Junções direcionadas são úteis nas seguintes situações:

• Você está migrando cargas de trabalho para o Snowflake com diretrizes de ordem de junção.

• Você deseja melhorar o desempenho verificando tabelas de junção em uma ordem específica.

Nota

A junção direcionada é um recurso em versão preliminar disponível para todas as contas.

Padrão: INNER JOIN

Se a palavra JOIN for usada sem especificar INNER ou OUTER, então o JOIN é uma junção interna.

Se a palavra-chave DIRECTED for adicionada, o tipo de junção (por exemplo, INNER ou OUTER) é necessário.

Consulte também:

• LATERAL

• ASOF JOIN

ON condition

Uma expressão booleana que define as linhas dos dois lados da JOIN que são consideradas iguais, por exemplo:

ON object_ref2.id_number = object_ref1.id_number

Copy

As condições são discutidas com mais detalhes na documentação da cláusula WHERE.

A cláusula ON é proibida para CROSS JOIN.

A cláusula ON é desnecessária (e proibida) para NATURAL JOIN porque as colunas de junção estão implícitas.

Para outras junções, a cláusula ON é opcional. No entanto, omitir a cláusula ON resulta em um produto cartesiano (cada linha de object_ref1 emparelhada com cada linha de object_ref2). Um produto cartesiano pode criar um volume de produção muito grande, quase todos os quais consistem em pares de linhas que não estão realmente relacionados, o que consome muitos recursos e, muitas vezes, é um erro do usuário.

USING( column_list )

Uma lista de colunas em comum entre as duas tabelas que estão sendo unidas. Essas colunas são usadas como colunas de junção. As colunas devem ter o mesmo nome e significado em cada uma das tabelas que estão sendo unidas.

Por exemplo, suponha que a instrução SQL contenha:

... o1 JOIN o2
    USING (key_column)

Copy

No caso simples, isto seria equivalente a:

... o1 JOIN o2
    ON o2.key_column = o1.key_column

Copy

Na sintaxe padrão de JOIN, a lista de projeção (a lista de colunas e outras expressões após a palavra-chave SELECT) é *. Isto faz com que a consulta retorne o key_column exatamente uma vez. As colunas são retornadas na seguinte ordem:

• As colunas na cláusula USING na ordem especificada.

• As colunas esquerdas da tabela não especificadas na cláusula USING.

• As colunas direitas da tabela não especificadas na cláusula USING.

Para exemplos de uso padrão e não padrão, consulte a seção exemplos.

Notas de uso¶

• As seguintes restrições se aplicam a funções de tabela diferentes de UDTFs SQL:

  • Não é possível especificar a cláusula ON, USING ou NATURAL JOIN em uma função de tabela lateral, diferente de SQL UDTF).

    Por exemplo, a seguinte sintaxe não é permitida:

    SELECT ... FROM my_table
      JOIN TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      INNER JOIN TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      JOIN TABLE(my_js_udtf(col_a))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      INNER JOIN TABLE(my_js_udtf(col_a))
      ON ... ;
    

    Copy

  • Não é possível especificar a cláusula ON, USING ou NATURAL JOIN em uma junção lateral externa a uma função de tabela, diferente de SQL UDTF).

    Por exemplo, a seguinte sintaxe não é permitida:

    SELECT ... FROM my_table
      LEFT JOIN TABLE(FLATTEN(input=>[a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      FULL JOIN TABLE(FLATTEN(input=>[a]))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      LEFT JOIN TABLE(my_js_udtf(a))
      ON ... ;
    

    Copy

    SELECT ... FROM my_table
      FULL JOIN TABLE(my_js_udtf(a))
      ON ... ;
    

    Copy

    Usar essa sintaxe resulta no seguinte erro:

    000002 (0A000): Unsupported feature
      'lateral table function called with OUTER JOIN syntax
       or a join predicate (ON clause)'
    

  • Essas restrições não se aplicam se você usar uma vírgula em vez de uma palavra-chave JOIN:

    SELECT ... FROM my_table,
      TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

    Copy

Exemplos¶

Muitos dos exemplos de JOIN usam duas tabelas: t1 e t2. Crie estas tabelas e insira os dados:

CREATE TABLE t1 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES
  (2),
  (3),
  (4);

CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t2 (col1) VALUES
  (1),
  (2),
  (2),
  (3);

Os exemplos a seguir executam consultas com junções:

• Execução de uma consulta com uma junção interna

• Execução de uma consulta com uma junção externa esquerda

• Execução de uma consulta com uma junção externa direita

• Execução de uma consulta com uma junção externa completa

• Execução de uma consulta com uma junção cruzada

• Execução de uma consulta com uma junção natural

• Executar uma consulta que combine as junções na cláusula FROM

• Executar consultas com junções que utilizam a cláusula USING

SELECT t1.col1, t2.col1
  FROM t1 INNER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 LEFT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 RIGHT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 FULL OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  ORDER BY 1, 2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  WHERE t2.col1 = t1.col1
  ORDER BY 1, 2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

CREATE OR REPLACE TABLE d1 (
  id NUMBER,
  name VARCHAR);

INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');

CREATE OR REPLACE TABLE d2 (
  id NUMBER,
  value VARCHAR);

INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');

SELECT *
  FROM d1 NATURAL INNER JOIN d2
  ORDER BY id;

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY id;

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

CREATE TABLE t3 (col1 INTEGER);

INSERT INTO t3 (col1) VALUES
  (2),
  (6);

SELECT t1.*, t2.*, t3.*
  FROM t1
    LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
    RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

SELECT t1.*, t2.*, t3.*
FROM t1
  LEFT OUTER JOIN
    (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
  ON (t1.col1 = t2.col1)
ORDER BY t1.col1;

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

WITH
  l AS (
       SELECT 'a' AS userid
       ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT *
  FROM l LEFT JOIN r USING(userid);

+--------+
| USERID |
|--------|
| a      |
+--------+

WITH
  l AS (
       SELECT 'a' AS userid
     ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT l.userid as UI_L,
       r.userid as UI_R
  FROM l LEFT JOIN r USING(userid);

+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+