JOIN

A cláusula JOIN produz uma nova tabela ao combinar colunas de uma ou mais tabelas com base em valores em comum entre elas. É uma operação comum em bancos de dados com suporte a SQL, correspondente à operação de junção da álgebra relacional. O caso especial de uma junção de uma tabela com ela mesma costuma ser chamado de "self-join".

Sintaxe

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

Expressões da cláusula ON e colunas da cláusula USING são chamadas de "chaves de junção". Salvo indicação em contrário, um JOIN produz um produto cartesiano entre linhas com "chaves de junção" correspondentes, o que pode gerar resultados com muito mais linhas do que as tabelas de origem.

Todos os tipos padrão de SQL JOIN são suportados:

• JOIN sem tipo especificado implica INNER.
• A palavra-chave OUTER pode ser omitida com segurança.
• Uma sintaxe alternativa para CROSS JOIN é especificar várias tabelas na cláusula FROM, separadas por vírgulas.
• Se não houver colunas correspondentes para um NATURAL JOIN, ele funciona como um CROSS JOIN.

Os tipos adicionais de junção disponíveis no ClickHouse são:

O tipo de junção padrão pode ser substituído usando a configuração join_default_strictness.

O comportamento do servidor do ClickHouse para operações ANY JOIN depende da configuração any_join_distinct_right_table_keys.

Veja também

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

Use a configuração cross_to_inner_join_rewrite para definir o comportamento quando o ClickHouse não consegue reescrever um CROSS JOIN como um INNER JOIN. O valor padrão é 1, o que permite que a junção continue, mas ela será mais lenta. Defina cross_to_inner_join_rewrite como 0 se quiser que um erro seja gerado e como 2 para não executar os CROSS JOIN, mas sim forçar a reescrita de todas as junções com vírgula ou CROSS JOIN. Se a reescrita falhar quando o valor for 2, você receberá uma mensagem de erro informando "Please, try to simplify WHERE section".

Uma seção ON pode conter várias condições combinadas com os operadores AND e OR. As condições que especificam chaves de junção devem:

• referenciar as tabelas da esquerda e da direita
• usar o operador de igualdade

Outras condições podem usar outros operadores lógicos, mas devem referenciar ou a tabela da esquerda ou a da direita de uma consulta.

As linhas são combinadas se toda a condição composta for atendida. Se as condições não forem atendidas, as linhas ainda poderão ser incluídas no resultado, dependendo do tipo de JOIN. Observe que, se as mesmas condições forem colocadas em uma seção WHERE e não forem atendidas, as linhas sempre serão filtradas do resultado.

O operador OR dentro da cláusula ON funciona com o algoritmo de hash junção — para cada argumento OR com chaves de junção para JOIN, é criada uma tabela hash separada; assim, o consumo de memória e o tempo de execução da consulta aumentam linearmente à medida que cresce o número de expressões OR na cláusula ON.

Exemplo

Considere table_1 e table_2:

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

Consulta com uma condição na chave de junção e uma condição adicional para table_2:

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

Observe que o resultado contém a linha com o nome C e a coluna de texto vazia. Ela foi incluída no resultado porque foi usada uma junção do tipo OUTER.

┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘

Consulta com junção do tipo INNER e múltiplas condições:

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

┌

Consulta com junção do tipo INNER e condição com OR:

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

Consulta com INNER como tipo de junção e condições com OR e AND:

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

Atualmente, o ClickHouse oferece suporte a ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN com condições de desigualdade, além das condições de igualdade. As condições de desigualdade são compatíveis apenas com os algoritmos de junção hash, parallel_hash e grace_hash. Uma condição de não igualdade avaliada durante a junção não pode conter arrayJoin, pois deve preservar o número de linhas; uma condição aplicada a apenas um lado e uma chave de igualdade baseada em arrayJoin são extraídas antes da junção e não são afetadas; uma condição não disjuntiva de ALL INNER JOIN também não é afetada, pois, nesse caso, a condição é aplicada após a junção. Quando a expansão depende de apenas um lado, mova-a para um ARRAY JOIN em uma subconsulta antes da junção; uma condição cujo argumento de arrayJoin lê colunas de ambos os lados precisa ser reestruturada.

Exemplo

Tabela t1:

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ a    │ 1 │ 1 │ 2 │
│ key1 │ b    │ 2 │ 3 │ 2 │
│ key1 │ c    │ 3 │ 2 │ 1 │
│ key1 │ d    │ 4 │ 7 │ 2 │
│ key1 │ e    │ 5 │ 5 │ 5 │
│ key2 │ a2   │ 1 │ 1 │ 1 │
│ key4 │ f    │ 2 │ 3 │ 4 │
└──────┴──────┴───┴───┴───┘

Tabela t2

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ A    │ 1 │ 2 │ 1 │
│ key1 │ B    │ 2 │ 1 │ 2 │
│ key1 │ C    │ 3 │ 4 │ 5 │
│ key1 │ D    │ 4 │ 1 │ 6 │
│ key3 │ a3   │ 1 │ 1 │ 1 │
│ key4 │ F    │ 1 │ 1 │ 1 │
└──────┴──────┴───┴───┴───┘

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);

key1    a    1    1    2    key1    B    2    1    2
key1    a    1    1    2    key1    C    3    4    5
key1    a    1    1    2    key1    D    4    1    6
key1    b    2    3    2    key1    C    3    4    5
key1    b    2    3    2    key1    D    4    1    6
key1    c    3    2    1    key1    D    4    1    6
key1    d    4    7    2            0    0    \N
key1    e    5    5    5            0    0    \N
key2    a2    1    1    1            0    0    \N
key4    f    2    3    4            0    0    \N

NULL não é igual a nenhum valor, nem a si mesmo. Isso significa que, se uma chave de junção tiver um valor NULL em uma tabela, ela não corresponderá a um valor NULL na outra tabela.

Exemplo

Tabela A:

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

Tabela B:

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

Observe que a linha com Charlie da tabela A e a linha com pontuação 88 da tabela B não aparecem no resultado por causa do valor NULL na chave de junção.

Caso você queira fazer a correspondência de valores NULL, use a função isNotDistinctFrom para comparar as chaves de junção.

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

Os valores NaN em chaves de junção de ponto flutuante não seguem a regra de NULL acima. A comparação escalar de dois valores NaN (NaN = NaN) resulta em 0; no entanto, as chaves de junção não são comparadas usando semântica escalar — chaves NaN podem corresponder. Se isso de fato ocorre é um detalhe de implementação e depende do algoritmo de junção, do tipo de chave e das configurações da sessão. Não dependa de um comportamento específico. Se você precisar que linhas com NaN não correspondam, mapeie-as para valores NULL: ON if(isNaN(A.id), NULL, A.id) = B.id. Em um ASOF JOIN, a coluna de correspondência mais próxima é comparada por ordenação, que NaN não suporta — filtre essas linhas em ambos os lados.

ASOF JOIN é útil quando você precisa combinar registros que não têm correspondência exata.

Este algoritmo de junção exige uma coluna especial nas tabelas. Essa coluna:

• Deve conter uma sequência ordenada.
• Pode ser de um dos seguintes tipos: Int, UInt, Float, Date, DateTime, Decimal.
• No algoritmo de junção hash, ela não pode ser a única coluna na cláusula JOIN.

Sintaxe ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

Você pode usar qualquer número de condições de igualdade e exatamente uma condição de correspondência mais próxima. Por exemplo, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.

Condições aceitas para a correspondência mais próxima: >, >=, <, <=.

Sintaxe de ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN usa equi_columnX para fazer a junção por igualdade e asof_column para fazer a junção pela correspondência mais próxima com a condição table_1.asof_column >= table_2.asof_column. A coluna asof_column é sempre a última na cláusula USING.

Por exemplo, considere as tabelas a seguir:

         table_1                           table_2
      event   | ev_time | user_id       event   | ev_time | user_id
    ----------|---------|----------   ----------|---------|----------
                  ...                               ...
    event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
                  ...                 event_2_2 |  12:30  |   42
    event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
                  ...                               ...

ASOF JOIN pode usar o timestamp de um evento de usuário de table_1 e encontrar um evento em table_2 cujo timestamp seja o mais próximo do timestamp do evento de table_1, de acordo com a condição de correspondência mais próxima. Valores de timestamp iguais são considerados os mais próximos, quando disponíveis. Aqui, a coluna user_id pode ser usada na junção por igualdade, e a coluna ev_time pode ser usada na junção pela correspondência mais próxima. No nosso exemplo, event_1_1 pode ser unido a event_2_1 e event_1_2 pode ser unido a event_2_3, mas event_2_2 não pode ser unido.

O resultado de PASTE JOIN é uma tabela que contém todas as colunas da subconsulta à esquerda, seguidas de todas as colunas da subconsulta à direita. As linhas são associadas com base em suas posições nas tabelas originais (a ordem das linhas deve estar definida). Se as subconsultas retornarem números diferentes de linhas, as linhas excedentes serão descartadas.

Exemplo:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers(2)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(2)
    ORDER BY a DESC
) AS t2

┌

Nota: neste caso, o resultado pode não ser determinístico se a leitura ocorrer em paralelo. Por exemplo:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers_mt(5)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(10)
    ORDER BY a DESC
) AS t2
SETTINGS max_block_size = 2;

┌

Há duas maneiras de executar um JOIN que envolve tabelas distribuídas:

• Ao usar um JOIN normal, a consulta é enviada aos servidores remotos. As subconsultas são executadas em cada um deles para montar a tabela da direita, e a junção é realizada com essa tabela. Em outras palavras, a tabela da direita é formada separadamente em cada servidor.
• Ao usar GLOBAL ... JOIN, primeiro o servidor solicitante executa uma subconsulta para calcular um dos lados da junção e armazena o resultado em uma tabela temporária. Essa tabela temporária é então enviada a cada servidor remoto, e as consultas são executadas neles usando os dados temporários transmitidos. Para junções LEFT e INNER, a tabela da direita é calculada pela subconsulta. Para junções RIGHT, a tabela da esquerda é calculada, já que a tabela da direita é a preservada e deve ser lida dos shards.

Tenha cuidado ao usar GLOBAL. Para mais informações, consulte a seção Subconsultas distribuídas.

Consultas INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN oferecem suporte à conversão implícita de tipos para as "chaves de junção". No entanto, a consulta não pode ser executada se as chaves de junção das tabelas à esquerda e à direita não puderem ser convertidas para um único tipo (por exemplo, não há nenhum tipo de dado que possa comportar todos os valores de UInt64 e Int64, ou de String e Int32).

Exemplo

Considere a tabela t_1:

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

e a tabela t_2:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

A consulta

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

retorna o conjunto:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

Ao fazer JOIN entre tabelas, podem surgir células vazias. A configuração join_use_nulls define como o ClickHouse preenche essas células.

Se as chaves de JOIN forem campos Nullable, as linhas em que pelo menos uma das chaves tem o valor NULL não são unidas.

As colunas especificadas em USING devem ter os mesmos nomes em ambas as subconsultas, e as demais colunas devem ter nomes diferentes. Você pode usar aliases para alterar os nomes das colunas nas subconsultas.

A cláusula USING especifica uma ou mais colunas para unir, estabelecendo a igualdade entre essas colunas. A lista de colunas é definida sem parênteses. Condições de junção mais complexas não são suportadas.

Para várias cláusulas JOIN em uma única consulta SELECT:

• Selecionar todas as colunas com * só é possível quando há junção entre tabelas, e não subconsultas.
• A cláusula PREWHERE não está disponível.
• A cláusula USING não está disponível.

Para as cláusulas ON, WHERE e GROUP BY:

• Não é possível usar expressões arbitrárias nas cláusulas ON, WHERE e GROUP BY, mas você pode definir uma expressão em uma cláusula SELECT e depois usá-la nessas cláusulas por meio de um alias.

Ao executar um JOIN, não há otimização da ordem de execução em relação às outras etapas da consulta. A junção (uma busca na tabela da direita) é executada antes da filtragem em WHERE e antes da agregação.

Sempre que uma consulta é executada com o mesmo JOIN, a subconsulta é executada novamente, porque o resultado não fica em cache. Para evitar isso, use o mecanismo de tabela especial Join, que é um array pré-preparado para junções e permanece sempre na RAM.

Em alguns casos, é mais eficiente usar IN em vez de JOIN.

Se você precisar de um JOIN para fazer junção com tabelas de dimensão (tabelas relativamente pequenas que contêm propriedades de dimensão, como nomes de campanhas publicitárias), um JOIN pode não ser muito prático, já que a tabela da direita é acessada novamente a cada consulta. Nesses casos, existe o recurso "dicionários", que você deve usar em vez de JOIN. Para mais informações, consulte a seção Dicionários.

Por padrão, o ClickHouse usa o algoritmo hash join. O ClickHouse usa a right_table e cria uma tabela hash para ela na RAM. Se join_algorithm = 'auto' estiver habilitado, após determinado limite de consumo de memória, o ClickHouse passa a usar o algoritmo merge de junção. Para obter uma descrição dos algoritmos de junção, consulte a configuração join_algorithm.

Se você precisar restringir o consumo de memória da operação de junção JOIN, use as seguintes configurações:

• max_rows_in_join — Limita o número de linhas na tabela hash.
• max_bytes_in_join — Limita o tamanho da tabela hash.

Quando qualquer um desses limites é atingido, o ClickHouse age conforme instruído pela configuração join_overflow_mode.

Exemplo:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

• Blog: ClickHouse: um SGBD extremamente rápido com suporte completo a junções SQL - Parte 1
• Blog: ClickHouse: um SGBD extremamente rápido com suporte completo a junções SQL - Nos bastidores - Parte 2
• Blog: ClickHouse: um SGBD extremamente rápido com suporte completo a junções SQL - Nos bastidores - Parte 3
• Blog: ClickHouse: um SGBD extremamente rápido com suporte completo a junções SQL - Nos bastidores - Parte 4