Clickhouse: Cláusula JOIN

Cláusula JOIN
Join produz uma nova tabela combinando colunas de uma ou várias tabelas usando valores comuns a cada uma. É uma operação comum em bancos de dados com suporte a SQL, que corresponde à junção de álgebra relacional . O caso especial de junção de uma tabela é frequentemente chamado de “self-join”.

Sintaxe:

Expressões from ONclause e colunas from USINGclause são chamadas de “join keys”. A menos que indicado de outra forma, join produz um produto cartesiano de linhas com “join keys” correspondentes, o que pode produzir resultados com muito mais linhas do que as tabelas de origem.

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

INNER JOIN, somente linhas correspondentes são retornadas.
LEFT OUTER JOIN, linhas não correspondentes da tabela da esquerda são retornadas além das linhas correspondentes.
RIGHT OUTER JOIN, linhas não correspondentes da tabela da direita são retornadas além das linhas correspondentes.
FULL OUTER JOIN, linhas não correspondentes de ambas as tabelas são retornadas, além das linhas correspondentes.
CROSS JOIN, produz produto cartesiano de tabelas inteiras, “chaves de junção” não são especificadas.
JOIN sem tipo especificado implica INNER. A palavra-chave OUTER pode ser omitida com segurança. A sintaxe alternativa para CROSS JOIN é especificar várias tabelas na cláusula FROM separadas por vírgulas.

Tipos de junção adicionais disponíveis no ClickHouse:

LEFT SEMI JOIN e RIGHT SEMI JOIN, uma lista de permissões em “chaves de junção”, sem produzir um produto cartesiano.
LEFT ANTI JOIN e RIGHT ANTI JOIN, uma lista negra de “chaves de junção”, sem produzir um produto cartesiano.
LEFT ANY JOIN, RIGHT ANY JOIN e INNER ANY JOIN, parcialmente (para o lado oposto de LEFT e RIGHT) ou completamente (para INNERe FULL) desabilita o produto cartesiano para JOINtipos padrão.
ASOF JOIN e LEFT ASOF JOIN, juntando sequências com uma correspondência não exata. ASOF JOIN O uso é descrito abaixo.
PASTE JOIN, executa uma concatenação horizontal de duas tabelas.

Observação
Quando join_algorithm é definido como partial_merge, RIGHT JOIN e FULL JOIN são suportados apenas com ALL rigor ( SEMI, ANTI, ANY, e ASOFnão são suportados).

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

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

Use a configuração cross_to_inner_join_rewrite para definir o comportamento quando o ClickHouse falha ao reescrever um CROSS JOIN como um INNER JOIN. O valor padrão é 1, que permite que a junção continue, mas será mais lento. Defina cross_to_inner_join_rewrite como 0 se quiser que um erro seja lançado e defina como para 2 não executar as junções cruzadas, mas sim forçar uma reescrita de todas as junções de vírgula/cruzadas. Se a reescrita falhar quando o valor for 2, você receberá uma mensagem de erro informando "Por favor, tente simplificar a seção WHERE".

Seção ON

Uma seção ON pode conter várias condições combinadas usando os operadores AND e OR. Condições que especificam chaves de junção devem se referir às tabelas esquerda e direita e devem usar o operador de igualdade. Outras condições podem usar outros operadores lógicos, mas devem se referir à tabela esquerda ou direita de uma consulta.

As linhas são unidas se toda a condição complexa for atendida. Se as condições não forem atendidas, ainda assim as linhas podem ser incluídas no resultado, dependendo do JOINtipo. Observe que se as mesmas condições forem colocadas em uma WHEREseção e não forem atendidas, as linhas serão sempre filtradas do resultado.

O operador OR dentro da ON cláusula funciona usando o algoritmo de junção hash — para cada argumento OR com chaves de junção para JOIN, uma tabela hash separada é criada, de modo que o consumo de memória e o tempo de execução da consulta crescem linearmente com o aumento do número de expressões OR da cláusula ON.

Observação
Se uma condição se refere a colunas de tabelas diferentes, então somente o operador de igualdade (=) é suportado até agora.

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 de 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');

Note que o resultado contém a linha com o nome C e a coluna de texto vazia. Ele é incluído no resultado porque um tipo de junção OUTER é usado.

Consulta com tipo de junção 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');

Resultado:

┌─name─┬─text───┬─scores─┐
│ B │ Text B │ 15 │
└──-───┴─-─────┴──────┘

Consulta com tipo de junção INNER e com condição 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;

Resultado:

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

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

Observação
Por padrão, condições não iguais são suportadas, desde que usem colunas da mesma tabela. Por exemplo, t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c, porque t1.b > 0usa colunas somente de t1e t2.b > t2.cusa colunas somente de t2. No entanto, você pode tentar suporte experimental para condições como t1.a = t2.key AND t1.b > t2.key, confira a seção abaixo para mais detalhes.

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

Resultado:

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

[experimental] Junção com condições de desigualdade para colunas de tabelas
Observação

Observação
Este recurso é experimental. Para usá-lo, defina allow_experimental_join_conditioncomo 1 em seus arquivos de configuração ou usando o comando SET:

SET allow_experimental_join_condition=1

Caso contrário, você obterá INVALID_JOIN_ON_EXPRESSION.

Atualmente, o Clickhouse oferece suporte ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN a condições de desigualdade, além de condições de igualdade. As condições de desigualdade são suportadas apenas para algoritmos de junção hashe grace_hash. As condições de desigualdade não são suportadas com join_use_nulls.

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

Resultado
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

Valores NULL

O NULL não é igual a nenhum valor, incluindo ele mesmo. Isso significa que se uma chave JOIN 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

Resultado:
┌─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 estão no resultado por causa do valor NULL na chave JOIN.

Caso você queira corresponder valores NULL, use a isNotDistinctFromfunção para comparar as chaves JOIN.

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

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

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

O algoritmo requer a coluna especial em tabelas. Esta coluna:

Deve conter uma sequência ordenada.
Pode ser um dos seguintes tipos: Int, UInt , Float , Date , DateTime , Decimal .
Para hasho algoritmo de junção, 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 suportadas para a correspondência mais próxima: >, >=, <, <=.

Sintaxe 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 unir em igualdade e asof_column para unir na 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 seguintes tabelas:
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 pegar o timestamp de um evento de usuário de table_1 e encontrar um evento em table_2 onde o timestamp é mais próximo do timestamp do evento de table_1 correspondente à condição de correspondência mais próxima. Valores de timestamp iguais são os mais próximos, se disponíveis. Aqui, a coluna user_id pode ser usada para unir em igualdade e a coluna ev_time pode ser usada para unir na correspondência mais próxima. Em nosso exemplo, event_1_1 pode ser unido com event_2_1 e event_1_2 pode ser unido com event_2_3, mas event_2_2 não pode ser unido.

Observação
ASOF JOIN é suportado apenas por algoritmos de junção hash e Não full_sorting_merge é suportado no mecanismo de junção de tabela.

USO DE PASTE JOIN
O resultado de PASTE JOIN é uma tabela que contém todas as colunas da subconsulta esquerda seguidas por todas as colunas da subconsulta direita. As linhas são correspondidas com base em suas posições nas tabelas originais (a ordem das linhas deve ser definida). Se as subconsultas retornarem um número diferente de linhas, linhas extras serão cortadas.

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

┌─a─┬─t2.a─┐
│ 0 │ 1 │
│ 1 │ 0 │
└───┴────┘
Nota: Neste caso, o resultado pode ser não determinístico se a leitura for paralela.
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;

┌─a─┬─t2.a─┐
│ 2 │ 9 │
│ 3 │ 8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │ 7 │
│ 1 │ 6 │
└───┴────┘
┌─a─┬─t2.a─┐
│ 4 │ 5 │
└───┴────┘

JOIN distribuído
Há duas maneiras de executar junções envolvendo tabelas distribuídas:

Ao usar um JOIN, a consulta é enviada para servidores remotos. Subconsultas são executadas em cada um deles para fazer a tabela certa, e a junção é realizada com essa tabela. Em outras palavras, a tabela certa é formada em cada servidor separadamente.
Ao usar GLOBAL ... JOIN, primeiro o servidor solicitante executa uma subconsulta para calcular a tabela correta. Essa tabela temporária é passada para cada servidor remoto, e consultas são executadas neles usando os dados temporários que foram transmitidos.
Tenha cuidado ao usar GLOBAL. Para obter mais informações, consulte a seção Subconsultas distribuídas .

Conversão de tipo implícito
Consultas INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL JOIN suportam a conversão de tipo implícita para "join keys", no entanto, a consulta não pode ser executada se as join keys das tabelas esquerda e direita não puderem ser convertidas em um único tipo (por exemplo, não há nenhum tipo de dado que possa conter todos os valores de UInt64, Int64 ou 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) │
└────┴────-─┴───────-────┴──────────────┘

Recomendações de uso

Processamento de Celulas
Ao unir tabelas, as células vazias podem aparecer. A configuração join_use_nulls define como o ClickHouse preenche essas células.

Se as chaves JOIN forem campos anuláveis, as linhas em que pelo menos uma das chaves tiver o valor NULL não serão unidas.

As colunas especificadas em USING devem ter os mesmos nomes em ambas as subconsultas, e as outras colunas devem ser nomeadas de forma diferente. Você pode usar aliases para alterar os nomes das colunas em subconsultas.

A cláusula USING especifica uma ou mais colunas para unir, o que estabelece a igualdade dessas colunas. A lista de colunas é definida sem colchetes. Condições de junção mais complexas não são suportadas.

Limitações de sintaxe

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

A obtenção de todas as colunas via * está disponível somente se as tabelas forem unidas, não se forem 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:

Expressões arbitrárias não podem ser usadas nas cláusulas ON, WHERE e GROUP BY, mas você pode definir uma expressão em uma cláusula SELECT e 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 a outros estágios da consulta. A junção (uma busca na tabela certa) é executada antes da filtragem WHERE e antes da agregação.

Cada vez que uma consulta é executada com o mesmo JOIN, a subconsulta é executada novamente porque o resultado não é armazenado em cache. Para evitar isso, use o mecanismo especial Join table, que é um array preparado para junção que está sempre na RAM.

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

Se você precisar de um JOIN para unir com tabelas de dimensão (estas são tabelas relativamente pequenas que contêm propriedades de dimensão, como nomes para campanhas publicitárias), um JOIN pode não ser muito conveniente devido ao fato de que a tabela certa é acessada novamente para cada consulta. Para tais casos, há um recurso de “dicionários” que você deve usar em vez de JOIN. Para mais informações, veja a seção Dicionários .

Limitaçõoes de memória
Por padrão, o ClickHouse usa o algoritmo hash join . O ClickHouse pega a right_table e cria uma tabela hash para ela na RAM. Se join_algorithm = 'auto' estiver habilitado, depois de algum limite de consumo de memória, o ClickHouse retorna ao algoritmo merge JOIN. Para descrição de algoritmos, veja a configuração join_algorithm.

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

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

Quando qualquer um desses limites é atingido, o ClickHouse age conforme as instruções da 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 │
└─────-───┴──────┴──-───┘