Skip to main content
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
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.

​
Tipos de JOIN suportados

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:
Ao usar o analyzer, desabilitar a configuração semi_join_include_columns_from_both_sides ou anti_join_include_columns_from_both_sides faz com que a junção correspondente exponha apenas o seu lado preservado às expressões resolvidas após a formação do resultado do JOIN.
  • LEFT SEMI JOIN e LEFT ANTI JOIN expõem apenas as colunas do lado esquerdo.
  • RIGHT SEMI JOIN e RIGHT ANTI JOIN expõem apenas as colunas do lado direito.
  • Isso afeta cláusulas como SELECT, PREWHERE, WHERE, GROUP BY, HAVING, QUALIFY, ORDER BY e LIMIT BY, incluindo curingas qualificados como t1.*.
  • A expressão ON do mesmo JOIN ainda pode referenciar ambos os lados.
Quando essas configurações estão habilitadas (o padrão), o ClickHouse mantém o comportamento legado, em que ambos os lados permanecem acessíveis e SELECT * expande as colunas de ambas as tabelas.
Quando join_algorithm está definido como partial_merge, RIGHT JOIN e FULL JOIN são suportados apenas com o rigor ALL (SEMI, ANTI, ANY e ASOF não são suportados).

​
LATERAL JOIN

JOIN LATERAL permite que a subconsulta no lado direito de uma junção faça referência a colunas das expressões de tabela à sua esquerda; a subconsulta é avaliada para cada combinação distinta dos valores das colunas do lado esquerdo que ela referencia, e seu resultado é unido a todas as linhas da esquerda que tenham essa combinação:
O predicado ON true é obrigatório, assim como em qualquer outro INNER ou LEFT JOIN; omiti-lo gera um erro de sintaxe. Trata-se de um recurso experimental e desabilitado por padrão; habilite-o com a configuração allow_experimental_lateral_join. Por enquanto, apenas o subconjunto a seguir é compatível; qualquer outra forma é rejeitada com um erro:
  • Somente INNER JOIN LATERAL e LEFT JOIN LATERAL; junções RIGHT, FULL, PASTE e NATURAL não são compatíveis, e LATERAL não pode, de forma alguma, ser combinado com uma junção CROSS ou por vírgula.
  • Somente o rigor padrão ALL; ANY, SEMI, ANTI e ASOF não são compatíveis.
  • Nenhum predicado de junção além de ON true (ON 1 também é aceito); USING não é compatível, e o predicado não pode ser omitido. Coloque os filtros que relacionam os dois lados na cláusula WHERE da subconsulta lateral.
  • Os modificadores de junção GLOBAL e LOCAL não são compatíveis.
  • A subconsulta lateral deve referenciar pelo menos uma coluna do lado esquerdo. Para uma subconsulta não correlacionada, use uma junção comum.
  • A subconsulta lateral é avaliada uma vez para cada valor distinto das colunas do lado esquerdo que ela referencia, e não uma vez por linha do lado esquerdo; portanto, ela não deve conter funções não determinísticas dentro de uma consulta, como rand ou generateUUIDv4, nem funções de tabela que geram linhas aleatórias, como generateRandom. Funções que são constantes dentro de uma consulta, como now, são permitidas.
  • Apenas uma subconsulta é compatível como expressão de tabela lateral. As formas de origem de tabela do PostgreSQL LATERAL unnest(...) e CROSS JOIN UNNEST(...) não são compatíveis; em vez delas, use a cláusula ARRAY JOIN.
  • O GROUP BY e o ORDER BY da subconsulta lateral são executados uma única vez sobre todas as avaliações em conjunto; assim, os limites max_rows_to_group_by, max_rows_to_sort e max_bytes_to_sort contabilizam as linhas de todas as avaliações, e não de apenas uma. Eles só são compatíveis com o modo de estouro throw; any e break são rejeitados.
  • As linhas de todas as avaliações são associadas às linhas do lado esquerdo por uma única junção. Como em qualquer hash join, max_rows_in_join e max_bytes_in_join limitam o lado dessa junção que é mantido em memória, e cabe ao planejador escolher esse lado: com as configurações padrão (correlated_subqueries_use_in_memory_buffer = 1), é o lado esquerdo do JOIN LATERAL, pois as linhas do lado esquerdo precisam ser lidas por completo antes que a subconsulta lateral seja avaliada; caso contrário, podem ser os resultados de todas as avaliações em conjunto. Os limites são sempre aplicados como se join_overflow_mode fosse throw: com break, a junção descartaria silenciosamente linhas do lado esquerdo não relacionadas.
Exemplo

​
Configurações

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 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”.

​
Condições da seção ON

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 e podem referenciar a tabela da esquerda, a da direita ou ambas as tabelas 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 join — 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.
Somente o operador de igualdade (=) torna uma condição uma chave de junção. Há suporte a outros operadores entre colunas de tabelas diferentes, mas, sem nenhuma igualdade, a junção precisa examinar cada par de linhas e fica muito mais lenta; consulte JOIN com uma condição ON arbitrária.
Exemplo Considere table_1 e table_2:
Consulta com uma condição na chave de junção e uma condição adicional para table_2:
Query
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.
Response
Consulta com junção do tipo INNER e múltiplas condições:
Query
Response
Consulta com junção do tipo INNER e condição com OR:
Query
Response
Consulta com INNER como tipo de junção e condições com OR e AND:
Uma condição de desigualdade que usa colunas de uma única tabela, como t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c, é aplicada somente a essa tabela: t1.b > 0 usa apenas colunas de t1 e t2.b > t2.c usa apenas colunas de t2. Uma condição de desigualdade que compara colunas de tabelas diferentes, como t1.a = t2.key AND t1.b > t2.key, também é aceita; consulte as seções abaixo para mais detalhes.
Query
Response

​
Junção com condições de desigualdade para colunas de tabelas diferentes

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. Quando a seção ON contém, além da desigualdade, uma igualdade entre as duas tabelas, a igualdade atua como chave de junção e a desigualdade é verificada nas linhas que ela encontrou correspondência. Essa condição mista é executada apenas pelos algoritmos de junção hash, parallel_hash e grace_hash. Quando a seção ON não contém nenhuma igualdade entre as duas tabelas, não há chave de junção para fazer a correspondência. Essa junção é executada por ie_join quando a condição é um par de desigualdades ou, caso contrário, como uma junção por laço aninhado em blocos. Uma condição avaliada durante a junção não pode conter arrayJoin, pois deve preservar o número de linhas; em vez disso, use ARRAY JOIN em uma subconsulta. Exemplo Tabela t1:
Tabela t2

​
JOIN com uma condição ON arbitrária

A seção ON pode ser uma expressão booleana arbitrária sobre as colunas de ambas as tabelas, como uma verificação de intervalo, uma comparação aritmética ou uma chamada a uma função escalar. Quando ela não contém nenhuma igualdade entre as duas tabelas, não há chave de junção para fazer a correspondência das linhas. Um par de condições de desigualdade é executado pelo ie_join quando esse algoritmo está listado em join_algorithm, o que ocorre por padrão. Qualquer outra condição é executada como uma junção por laço aninhado em blocos: a tabela da direita é materializada e a condição é avaliada para cada par de linhas. Um ALL INNER JOIN assume a forma equivalente de um CROSS JOIN com a condição atuando como filtro. A junção por laço aninhado em blocos oferece suporte a todos os tipos de junção e níveis de rigor, exceto ASOF JOIN, PASTE JOIN e ANY FULL JOIN. Ela não é um dos valores de join_algorithm: trata-se do último recurso, usado apenas quando nenhum outro algoritmo consegue executar a condição. Na saída do EXPLAIN, ela aparece como um passo BlockNestedLoopJoin. Quando allow_block_nested_loop_join está desabilitado, uma consulta que dependa dela é rejeitada com INVALID_JOIN_ON_EXPRESSION. Exemplo
Query
Response
A linha que não teve correspondência é preenchida de acordo com join_use_nulls, como em qualquer outra junção: com um valor default no exemplo acima e com NULL quando a configuração está habilitada. Rigor ANY e SEMI normalmente mantêm uma linha por grupo de linhas que compartilham uma chave de junção. Aqui não existe esse grupo, então elas mantêm uma linha por linha da tabela que conduz a junção: LEFT ANY e LEFT SEMI emitem cada linha da esquerda no máximo uma vez, e RIGHT ANY e RIGHT SEMI, cada linha da direita no máximo uma vez. ANY INNER limita os dois lados ao mesmo tempo — cada linha de qualquer uma das tabelas é usada no máximo uma vez, de modo que o resultado tem, no máximo, o mesmo número de linhas da tabela menor. Quais pares compõem esse resultado é algo arbitrário, exatamente como ANY sugere, e pode variar entre duas execuções da mesma consulta. No caso de ANY INNER, isso se estende ao número de linhas: emparelhar de forma gulosa cada linha dos dois lados, em qualquer ordem em que as linhas sejam examinadas, pode deixar um número diferente de linhas sem par. Portanto, o resultado de count() sobre uma junção ANY INNER sem chave de junção não é reproduzível e depende do número de threads e da ordem física da tabela da direita. Desempenho Todos os pares de linhas são examinados, então o trabalho cresce com o produto das contagens de linhas das duas tabelas, e não com a sua soma. Por isso, uma junção por laço aninhado em blocos é ordens de grandeza mais cara do que um hash join sobre os mesmos dados, e a diferença aumenta à medida que as tabelas crescem. Se for possível escrever a consulta com pelo menos uma igualdade na seção ON, faça isso. As junções LEFT ANY, ANY INNER, LEFT SEMI e LEFT ANTI param de varrer a tabela da direita ao encontrar a primeira linha correspondente a uma linha da esquerda, o que geralmente as torna mais baratas do que a junção ALL equivalente. Suas contrapartes conduzidas pela direita (RIGHT ANY, RIGHT SEMI, RIGHT ANTI) examinam todos os pares, porque o resultado depende de quais linhas da direita tiveram correspondência. join_any_take_last_row não tem efeito aqui: sem chave de junção, não há um grupo de linhas correspondentes do qual pegar a última. O uso de memória é limitado pela tabela da direita materializada e não cresce com o tamanho do resultado. A tabela da direita está sujeita às mesmas configurações dos outros algoritmos de junção, listadas em Limitações de memória: max_rows_in_join, max_bytes_in_join e join_overflow_mode a limitam, enquanto max_bytes_before_external_join faz com que ela seja despejada (spill) para o disco.

​
Valores NULL e NaN nas chaves de junção

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:
Tabela B:
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.
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.

​
Uso do ASOF JOIN

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:
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:
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:
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.
ASOF JOIN é compatível apenas com os algoritmos de junção hash e full_sorting_merge. Não é compatível com o mecanismo de tabela Join.

​
Uso do PASTE JOIN

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:
Nota: neste caso, o resultado pode não ser determinístico se a leitura ocorrer em paralelo. Por exemplo:

​
JOIN distribuído

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.

​
Conversão implícita de tipos

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:
e a tabela t_2:
A consulta
retorna o conjunto:

​
Recomendações de uso

​
Processamento de células vazias ou NULL

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.

​
Sintaxe

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.

​
Limitações de sintaxe

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.

​
Desempenho

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.

​
Limitações de memória

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: Quando qualquer um desses limites é atingido, o ClickHouse age conforme instruído pela configuração join_overflow_mode. Esses dois são limites rígidos e nunca fazem uma junção realizar spill para disco, portanto defini-los no limiar de spill ou abaixo dele normalmente interrompe a consulta antes que ela possa fazer spill. Duas configurações mudam esse comportamento: enable_adaptive_memory_spill_scheduler ainda pode fazer spill de uma junção que o limiar abaixo tornou apta a spill, e legacy_join_size_limits_trigger_spilling transforma os dois limites rígidos novamente em gatilhos de spill em disco. Para permitir que uma junção continue executando, fazendo spill do lado direito para o disco em vez de falhar, use: Esses são o gatilho de spill baseado em limiar para todo algoritmo baseado em hash, incluindo o grace_hash; sob pressão de memória, enable_adaptive_memory_spill_scheduler pode fazer spill antes do que eles determinam — mas somente quando um deles for diferente de zero, já que uma junção sem limiar algum nunca faz spill. O join_algorithm escolhido determina como uma junção faz spill — o grace_hash particiona a tabela da direita desde o primeiro bloco, enquanto hash e parallel_hash a acumulam em memória e mudam de estratégia quando o limiar é ultrapassado — e não se essas configurações se aplicam. A única exceção é legacy_join_size_limits_trigger_spilling: com ela ativada, o grace_hash independente ignora ambos os limiares e faz spill com base nos dois limites rígidos.

​
Exemplos

Exemplo:

​
Conteúdo relacionado