JOIN - MaxCompute: Central de Documentação do Alibaba Cloud

O MaxCompute suporta operações JOIN para combinar linhas de duas tabelas com base em uma coluna relacionada. Os tipos suportados incluem LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, INNER JOIN, NATURAL JOIN, JOIN implícito e múltiplas operações de JOIN.

Tipos de JOIN suportados

Tipo de Join	Comportamento
`LEFT OUTER JOIN` (ou `LEFT JOIN`)	Retorna todas as linhas da tabela à esquerda, incluindo aquelas sem correspondência na tabela à direita. As colunas não correspondidas da tabela à direita assumem o valor NULL.
`RIGHT OUTER JOIN` (ou `RIGHT JOIN`)	Retorna todas as linhas da tabela à direita, incluindo aquelas sem correspondência na tabela à esquerda. As colunas não correspondidas da tabela à esquerda assumem o valor NULL.
`FULL OUTER JOIN` (ou `FULL JOIN`)	Retorna todas as linhas de ambas as tabelas. Colunas sem correspondência em qualquer um dos lados assumem o valor NULL.
`INNER JOIN`	Retorna apenas as linhas com correspondência em ambas as tabelas. A palavra-chave `INNER` é opcional.
`NATURAL JOIN`	Junta automaticamente todas as colunas com o mesmo nome em ambas as tabelas. O MaxCompute suporta `OUTER NATURAL JOIN`. Quando usado com a cláusula `USING`, as colunas comuns aparecem apenas uma vez na saída.
JOIN implícito	Omite a palavra-chave `JOIN`. Liste as tabelas na cláusula `FROM` separadas por vírgulas e defina a condição de junção em `WHERE`. Equivale a um `INNER JOIN`.
Múltiplos JOINs	Encadeie várias operações `JOIN` em uma única instrução. Use parênteses `()` para controlar a ordem de avaliação — as operações dentro dos parênteses são executadas primeiro.

Limitações

Não há suporte para CROSS JOIN. Um CROSS JOIN combina duas tabelas sem condições na cláusula ON, produzindo um produto cartesiano em que cada linha da primeira tabela se associa a cada linha da segunda tabela.
As condições de JOIN devem usar equi-joins combinados com AND. Non-equi joins ou condições combinadas com OR têm suporte apenas no MAPJOIN.

Notas de uso

Chaves de junção NULL são excluídas. O MaxCompute adiciona automaticamente um filtro key IS NOT NULL para as chaves de junção. Linhas em que a chave de junção é NULL são removidas do resultado.
Filtros WHERE após JOIN. Quando uma instrução inclui tanto JOIN quanto WHERE, a junção é executada primeiro e seus resultados são então filtrados pelo WHERE. O resultado final é uma interseção das duas tabelas, e não todas as linhas de qualquer uma delas. Consulte o Exemplo 9 para uma ilustração concreta.
Evite LEFT JOIN consecutivos em tabelas com linhas duplicadas. Na maioria das operações de JOIN, a tabela à esquerda é grande e a tabela à direita é pequena. Se a tabela à direita possuir valores de linha duplicados, múltiplas operações LEFT JOIN consecutivas podem causar inchaço de dados e interrupções no job.

Sintaxe

<table_reference> JOIN <table_factor> [<join_condition>]
| <table_reference> {LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER|NATURAL} JOIN <table_reference> <join_condition>

Parâmetros:

table_reference: A tabela à esquerda. Formato: table_name [alias] | table_query [alias] | ...
table_factor: A tabela à direita ou alvo da junção. Formato: table_name [alias] | table_subquery [alias] | ...
join_condition: Uma ou mais expressões de igualdade combinadas com AND. Formato: ON equality_expression [AND equality_expression] ...

O comportamento de partition pruning varia dependendo do local onde as condições de filtro são colocadas:

Condições em WHERE : o partition pruning aplica-se tanto às tabelas pai quanto às filhas.

Condições em ON : o partition pruning aplica-se apenas à tabela filha; uma varredura completa da tabela (full table scan) é executada na tabela pai. Para obter detalhes, consulte Verificar se o partition pruning é eficaz .

Controle do comportamento de predicado em `OUTER JOIN`

O parâmetro odps.task.sql.outerjoin.ppd controla se as condições que não são de JOIN em uma cláusula OUTER JOIN ON são aplicadas antes ou depois da junção.

Valor	Comportamento
`false`	A condição que não é de JOIN em `ON` é tratada como uma condição de subconsulta `WHERE` (comportamento não padrão). Coloque as condições que não são de JOIN em `WHERE`.
`true`	Comportamento padrão. Condições que não são de JOIN em `ON` não são movidas para `WHERE`.

Configure este parâmetro no nível do projeto ou da sessão.

Quando definido como false, as duas instruções a seguir são equivalentes. Quando definido como true, elas não são equivalentes:

SELECT A.*, B.* FROM A LEFT JOIN B ON A.c1 = B.c1 AND A.c2 = 'xxx';

SELECT A.*, B.* FROM (SELECT * FROM A WHERE c2 = 'xxx') A LEFT JOIN B ON A.c1 = B.c1;

Dados de exemplo

Os exemplos a seguir usam duas tabelas particionadas: sale_detail e sale_detail_jt.

-- Create the tables
CREATE TABLE IF NOT EXISTS sale_detail
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

CREATE TABLE IF NOT EXISTS sale_detail_jt
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Add partitions
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
ALTER TABLE sale_detail_jt ADD PARTITION (sale_date='2013', region='china');

-- Insert data
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
INSERT INTO sale_detail_jt PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

-- Preview the data (requires full table scan to be enabled for partitioned tables)
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;

A tabela sale_detail contém:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt;

A tabela sale_detail_jt contém:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

-- Create a helper table used in the multiple-JOIN example
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;

Exemplos

Todos os exemplos usam as tabelas de amostra definidas em Dados de exemplo. Como ambas as tabelas possuem uma coluna shop_name, os exemplos usam aliases de tabela (a, b) para distinguir as colunas no SELECT.

Exemplo 1: LEFT OUTER JOIN

SET odps.sql.allow.fullscan=true;
SELECT a.shop_name AS ashop, b.shop_name AS bshop
FROM sale_detail_jt a
LEFT OUTER JOIN sale_detail b ON a.shop_name = b.shop_name;

Resultado:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

Todas as três linhas de sale_detail_jt (a tabela à esquerda) aparecem. Como s5 não possui um shop_name correspondente em sale_detail, a coluna bshop é NULL.

Exemplo 2: RIGHT OUTER JOIN

SET odps.sql.allow.fullscan=true;
SELECT a.shop_name AS ashop, b.shop_name AS bshop
FROM sale_detail_jt a
RIGHT OUTER JOIN sale_detail b ON a.shop_name = b.shop_name;

Resultado:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s1         | s1         |
| s2         | s2         |
| NULL       | s3         |
| NULL       | null       |
| NULL       | s6         |
| NULL       | s7         |
+------------+------------+

Todas as seis linhas de sale_detail (a tabela à direita) aparecem. As quatro lojas sem entrada correspondente em sale_detail_jt produzem NULL na coluna ashop.

Exemplo 3: FULL OUTER JOIN

SET odps.sql.allow.fullscan=true;
SELECT a.shop_name AS ashop, b.shop_name AS bshop
FROM sale_detail_jt a
FULL OUTER JOIN sale_detail b ON a.shop_name = b.shop_name;

Resultado:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| NULL       | s3         |
| NULL       | s6         |
| s2         | s2         |
| NULL       | null       |
| NULL       | s7         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

Todas as linhas de ambas as tabelas aparecem. s5 existe apenas em sale_detail_jt (NULL à direita), enquanto s3, null, s6 e s7 existem apenas em sale_detail (NULL à esquerda).

Exemplo 4: INNER JOIN

SET odps.sql.allow.fullscan=true;
SELECT a.shop_name AS ashop, b.shop_name AS bshop
FROM sale_detail_jt a
INNER JOIN sale_detail b ON a.shop_name = b.shop_name;

Resultado:

+------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
+------------+------------+

Apenas s1 e s2 existem em ambas as tabelas, portanto, apenas essas duas linhas são retornadas.

Exemplo 5: NATURAL JOIN

O NATURAL JOIN junta todas as colunas com o mesmo nome em ambas as tabelas. Neste caso, isso abrange todas as cinco colunas: shop_name, customer_id, total_price, sale_date e region.

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt NATURAL JOIN sale_detail;

Isso equivale a:

SELECT sale_detail_jt.shop_name AS shop_name,
       sale_detail_jt.customer_id AS customer_id,
       sale_detail_jt.total_price AS total_price,
       sale_detail_jt.sale_date AS sale_date,
       sale_detail_jt.region AS region
FROM sale_detail_jt
INNER JOIN sale_detail
ON sale_detail_jt.shop_name = sale_detail.shop_name
   AND sale_detail_jt.customer_id = sale_detail.customer_id
   AND sale_detail_jt.total_price = sale_detail.total_price
   AND sale_detail_jt.sale_date = sale_detail.sale_date
   AND sale_detail_jt.region = sale_detail.region;

Resultado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Apenas s1 e s2 correspondem em todas as cinco colunas. Embora s5 compartilhe customer_id e total_price com s2, o shop_name é diferente, então ele é excluído.

Exemplo 6: JOIN implícito

Omitir a palavra-chave JOIN e listar tabelas separadas por vírgulas em FROM produz um inner join implícito.

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt, sale_detail
WHERE sale_detail_jt.shop_name = sale_detail.shop_name;

Isso equivale a:

SELECT * FROM sale_detail_jt
JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name;

Resultado:

+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+

Exemplo 7: Múltiplos JOINs sem prioridade explícita

Quando múltiplos JOINs são encadeados sem parênteses, eles são avaliados da esquerda para a direita.

SET odps.sql.allow.fullscan=true;
SELECT a.* FROM sale_detail_jt a
FULL OUTER JOIN sale_detail b ON a.shop_name = b.shop_name
FULL OUTER JOIN sale_detail c ON a.shop_name = c.shop_name;

Resultado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s5         | c2          | 100.2       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| s1         | c1          | 100.1       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| s2         | c2          | 100.2       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
+------------+-------------+-------------+------------+------------+

Cada FULL OUTER JOIN com sale_detail (que possui 6 linhas) gera linhas NULL adicionais para combinações não correspondentes de ambos os lados de cada junção. O resultado seleciona apenas colunas de a (sale_detail_jt), portanto, as linhas correspondentes de sale_detail aparecem como NULL.

Exemplo 8: Múltiplos JOINs com prioridade explícita

Use parênteses para controlar qual JOIN é avaliado primeiro.

SET odps.sql.allow.fullscan=true;
SELECT * FROM shop
JOIN (sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name)
ON shop.shop_name = sale_detail_jt.shop_name;

O inner join interno (sale_detail_jt JOIN sale_detail ...) é executado primeiro, produzindo apenas linhas correspondentes. O join externo com shop filtra ainda mais com base na correspondência de shop_name.

Resultado:

+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    | shop_name3 | customer_id3 | total_price3 | sale_date3 | region3    |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+

Exemplo 9: Filtragem com WHERE versus subconsulta em LEFT JOIN

Este exemplo demonstra uma diferença crítica de comportamento ao filtrar linhas em um OUTER JOIN.

Abordagem correta — filtrar em uma subconsulta:

SET odps.sql.allow.fullscan=true;
SELECT a.shop_name, a.customer_id, a.total_price, b.total_price
FROM (SELECT * FROM sale_detail WHERE region = 'china') a
LEFT JOIN (SELECT * FROM sale_detail_jt WHERE region = 'china') b
ON a.shop_name = b.shop_name;

Resultado (todas as 3 linhas da tabela à esquerda filtrada são preservadas):

+------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
| s3         | c3          | 100.3       | NULL         |
+------------+-------------+-------------+--------------+

Como s3 não tem correspondência em sale_detail_jt, total_price2 é NULL — mas a linha ainda é retornada porque pertence à tabela à esquerda.

Abordagem incorreta — filtrar no WHERE após o LEFT JOIN:

SELECT a.shop_name, a.customer_id, a.total_price, b.total_price
FROM sale_detail a
LEFT JOIN sale_detail_jt b ON a.shop_name = b.shop_name
WHERE a.region = 'china' AND b.region = 'china';

Resultado (apenas 2 linhas — a interseção):

+------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
+------------+-------------+-------------+--------------+

A linha s3 é excluída porque b.region é NULL para essa linha, e a condição WHERE b.region = 'china' a filtra. A cláusula WHERE é executada após o LEFT JOIN e efetivamente transforma o outer join em um inner join — uma source comum de bugs.

Para preservar a semântica do outer join, filtre as linhas antes da junção usando subconsultas. Colocar condições de filtro em WHERE após um OUTER JOIN descarta silenciosamente as linhas que o outer join deveria preservar.

Tipos de JOIN suportados

Limitações

Notas de uso

Sintaxe

Controle do comportamento de predicado em OUTER JOIN

Dados de exemplo

Exemplos

Exemplo 1: LEFT OUTER JOIN

Exemplo 2: RIGHT OUTER JOIN

Exemplo 3: FULL OUTER JOIN

Exemplo 4: INNER JOIN

Exemplo 5: NATURAL JOIN

Exemplo 6: JOIN implícito

Exemplo 7: Múltiplos JOINs sem prioridade explícita

Exemplo 8: Múltiplos JOINs com prioridade explícita

Exemplo 9: Filtragem com WHERE versus subconsulta em LEFT JOIN

Controle do comportamento de predicado em `OUTER JOIN`