Categorias:: Sintaxe de consulta

JOIN¶

Uma operação JOIN combina linhas de duas tabelas (ou outras fontes semelhantes a tabelas, como visualizações ou funções de tabela) para criar uma nova linha combinada que pode ser usada na consulta. Para uma explicação conceitual das junções, consulte Como trabalhar com junções.

Este tópico descreve como usar o constructo JOIN na cláusula FROM. A subcláusula JOIN especifica (explícita ou implicitamente) como relacionar as linhas em uma tabela com as linhas correspondentes na outra tabela. Consulte também ASOF JOIN, que é usado para unir dados de séries temporais em colunas de carimbo de data/hora quando seus valores seguem um ao outro, precedem um ao outro ou correspondem exatamente.

Embora a forma recomendada de juntar tabelas seja usar JOIN com a subcláusula ON da cláusula FROM, uma forma alternativa de fazer a junção das tabelas é usando a cláusula WHERE. Para obter mais detalhes, consulte a documentação da cláusula WHERE.

Sintaxe¶

Use uma das seguintes opções:

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

Copy

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

Copy

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

Copy

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`. (Note que você também pode usar uma vírgula para especificar uma junção interna. Para um exemplo, consulte a seção de exemplos abaixo). Se você usar `INNER JOIN` sem a cláusula `ON` (ou se você usar vírgula sem uma cláusula `WHERE`), o resultado é 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 composta de todas as colunas em `o1` seguida de todas as colunas em `o2`. A `CROSS JOIN` não pode ser combinada 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). Consulte a seção Exemplos abaixo para alguns exemplos. A `NATURAL JOIN` pode ser combinada com um `OUTER JOIN`. A `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.

Consulte também:

Padrão: INNER JOIN

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

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; 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; isso 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; estas 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 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 os exemplos abaixo.

Notas de uso¶

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

Você não pode especificar a cláusula ON, USING ou NATURAL JOIN em uma função de tabela lateral (diferente de UDTF SQL).

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

Você não pode especificar a cláusula ON, USING ou NATURAL JOIN em uma junção lateral externa a uma função de tabela (diferente de UDTF SQL).

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 esta sintaxe acima 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. As tabelas e seus dados são criados como mostrado abaixo:

CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);

Copy

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

Copy

Junção interna:

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

Copy

Isto mostra uma junção externa esquerda. Observe o valor NULL para a linha na tabela t1 que não tem uma linha correspondente na tabela t2.

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

Copy

Isto mostra uma junção externa correta. Observe o valor NULL para a linha na tabela t1 que não tem uma linha correspondente na tabela t2.

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

Copy

Isto mostra uma junção externa completa. Observe que como cada tabela tem uma linha que não tem uma linha correspondente na outra tabela, a saída contém duas linhas com valores NULL:

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

Copy

Aqui está um exemplo de junção cruzada, que produz um produto cartesiano. Note que a junção cruzada não tem uma cláusula ON.

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

Copy

Uma junção cruzada pode ser filtrada por uma cláusula WHERE, como mostrado no exemplo abaixo:

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

Copy

Este é um exemplo de uma junção natural. Isso produz a mesma saída da junção interna correspondente, exceto que a saída não inclui uma segunda cópia da coluna de junção:

CREATE OR REPLACE TABLE d1 (
  id number,
  name string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D1 successfully created. |
+--------------------------------+
INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
CREATE OR REPLACE TABLE d2 (
  id number,
  value string
  );
+--------------------------------+
| status                         |
|--------------------------------|
| Table D2 successfully created. |
+--------------------------------+
INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       3 |
+-------------------------+
SELECT *
    FROM d1 NATURAL INNER JOIN d2
    ORDER BY id;
+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

Copy

Junções naturais podem ser combinadas com junções externas, por exemplo:

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

Copy

As junções podem ser combinadas na cláusula FROM. O código a seguir cria uma terceira tabela, depois une duas JOINs na cláusula FROM:

CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t3 (col1) VALUES 
   (2),
   (6);

Copy

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

Copy

Em tal consulta, os resultados são determinados com base nas junções que ocorrem da esquerda para a direita (embora o otimizador possa reordenar as junções se uma ordem de junção diferente produzir o mesmo resultado). Se a junção externa direita tiver que ocorrer antes da junção externa esquerda, então a consulta pode ser gravada da seguinte forma:

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

Copy

Os dois exemplos abaixo mostram o uso padrão (ISO 9075) e não padrão da cláusula USING. Ambos são suportados pelo Snowflake.

Este primeiro exemplo mostra o uso padrão. Especificamente, a lista de projeção contém exatamente «*». Mesmo que a consulta de exemplo una duas tabelas, e cada tabela tenha uma coluna, e a consulta peça todas as colunas, a saída terá uma coluna, não duas.

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

Copy

O exemplo a seguir mostra o uso não padrão. A lista de projeção contém algo diferente de «*». A saída contém duas colunas, e a segunda coluna contém um valor da segunda tabela ou NULL.

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

Copy