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

O MaxCompute permite usar operações JOIN para unir tabelas e retornar dados que atendem às condições de junção e consulta. Este tópico descreve as seguintes operações JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, INNER JOIN, NATURAL JOIN, JOIN implícito e múltiplas operações JOIN.

Visão geral

O MaxCompute oferece suporte aos seguintes tipos de operações JOIN:

LEFT OUTER JOIN

Também chamado de LEFT JOIN. O LEFT OUTER JOIN retorna todas as linhas da tabela à esquerda, inclusive aquelas sem correspondência na tabela à direita.

Nota
Em uma operação JOIN, a tabela à esquerda geralmente é grande, enquanto a da direita costuma ser pequena. Se houver valores duplicados em algumas linhas da tabela à direita, evite executar múltiplas operações LEFT JOIN consecutivas. A execução de vários LEFT JOIN em sequência pode causar inchaço de dados e interromper seus jobs.
RIGHT OUTER JOIN

Também conhecido como RIGHT JOIN. O RIGHT OUTER JOIN retorna todas as linhas da tabela à direita, inclusive as sem correspondência na tabela à esquerda.
FULL OUTER JOIN

Também denominado FULL JOIN. O FULL OUTER JOIN retorna todas as linhas das tabelas à esquerda e à direita.
INNER JOIN

A palavra-chave INNER é opcional. O INNER JOIN retorna linhas de dados apenas quando há correspondência entre as tabelas à esquerda e à direita.
NATURAL JOIN

Na operação NATURAL JOIN, os campos usados para unir duas tabelas são determinados com base nos campos comuns entre elas. O MaxCompute oferece suporte a OUTER NATURAL JOIN. Ao usar a cláusula USING, a operação NATURAL JOIN retorna os campos comuns apenas uma vez.
Operação JOIN implícito

Execute uma operação JOIN implícita sem especificar a palavra-chave JOIN.
Múltiplas operações JOIN

O MaxCompute oferece suporte a múltiplas operações JOIN. Use parênteses () para definir a prioridade das operações JOIN. Uma operação JOIN entre parênteses () tem maior prioridade.

Nota

Se uma instrução SQL contiver a cláusula WHERE e você usar a cláusula JOIN antes dela, a operação JOIN será executada primeiro. Em seguida, os resultados obtidos pela operação JOIN serão filtrados conforme as condições especificadas na cláusula WHERE. O resultado final representa a interseção das duas tabelas, e não todas as linhas de uma única tabela.
Use o parâmetro odps.task.sql.outerjoin.ppd para controlar se uma condição não-JOIN na cláusula OUTER JOIN ON deve ser usada como dados de entrada da operação JOIN. Configure esse parâmetro no nível do projeto ou da sessão.
- Ao definir este parâmetro como false, a condição não-JOIN na cláusula ON é tratada como condição da cláusula WHERE para a subconsulta da operação JOIN. Esse comportamento não é padrão. Recomendamos especificar a condição não-JOIN diretamente na cláusula WHERE.
- Com este parâmetro definido como false, as instruções SQL a seguir são equivalentes. Se o parâmetro for definido como true, as duas instruções SQL deixam de ser 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;
```

Notas de uso

Durante uma operação JOIN, a condição de filtro key is not null é adicionada automaticamente ao cálculo. Linhas com valor nulo na chave de junção são filtradas após a operação JOIN.

Limites

Ao executar uma operação JOIN, observe os seguintes limites:

O MaxCompute não oferece suporte a CROSS JOIN. Uma operação CROSS JOIN une duas tabelas sem exigir condições na cláusula ON.
Utilize equi-joins e combine condições usando AND. Non-equi joins ou a combinação de múltiplas condições com OR são permitidos apenas em uma operação MAPJOIN. Para mais informações, consulte MAPJOIN.

Sintaxe

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

table_reference: obrigatório. Instrução de consulta para a tabela à esquerda onde a operação JOIN é realizada. O valor deste parâmetro segue o formato table_name [alias] | table_query [alias] |....
table_factor: obrigatório. Instrução de consulta para a tabela à direita ou para uma tabela onde a operação JOIN é executada. O valor deste parâmetro segue o formato table_name [alias] | table_subquery [alias] |....
join_condition: opcional. Uma condição JOIN consiste na combinação de uma ou mais expressões de igualdade. O valor deste parâmetro segue o formato on equality_expression [and equality_expression].... equality_expression representa uma expressão de igualdade.

Nota

Se condições de pruning de partição forem especificadas na cláusula WHERE, o pruning de partição terá efeito tanto nas tabelas pai quanto nas filhas. Caso as condições estejam na cláusula ON, o pruning afetará apenas a tabela filha. Consequentemente, uma varredura completa (full table scan) será executada na tabela pai. Para mais informações, consulte Evaluate partition pruning effectiveness.

Dados de amostra

Dados de origem de amostra estão disponíveis para facilitar o entendimento dos exemplos neste tópico. As instruções a seguir mostram como criar as tabelas sale_detail e sale_detail_jt e inserir dados nelas.

-- Create two partitioned tables named sale_detail and sale_detail_jt. 
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 to the two tables. 
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 into the tables. 
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);

-- Query data from the sale_detail and sale_detail_jt tables. Sample statements:
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
-- The following result is returned:
+------------+-------------+-------------+------------+------------+
| 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;
-- The following result is returned:
+------------+-------------+-------------+------------+------------+
| 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 table for the JOIN operation. 
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;

Exemplos

Os exemplos a seguir demonstram o uso de JOIN com base nos Dados de amostra.

Exemplo 1: LEFT OUTER JOIN. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. 
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 retornado:

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

Exemplo 2: RIGHT OUTER JOIN. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. 
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 retornado:

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

Exemplo 3: FULL OUTER JOIN. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. 
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 retornado:

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

Exemplo 4: INNER JOIN. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. 
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 retornado:

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

Exemplo 5: NATURAL JOIN. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Perform a NATURAL JOIN operation. 
SELECT * FROM sale_detail_jt NATURAL JOIN sale_detail;
-- The preceding statement is equivalent to the following statement: 
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 retornado:

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

Exemplo 6: JOIN implícito. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Perform an implicit JOIN operation. 
SELECT * FROM sale_detail_jt, sale_detail WHERE sale_detail_jt.shop_name = sale_detail.shop_name;
-- The preceding statement is equivalent to the following statement: 
SELECT * FROM sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name;

Resultado retornado:

+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| 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últiplas operações JOIN sem prioridade especificada. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Both the sale_detail_jt and sale_detail tables have the shop_name column. You must use aliases to distinguish between the columns in the SELECT statement. 
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 retornado:

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

Exemplo 8: Múltiplas operações JOIN usando parênteses () para definir prioridades. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Perform multiple JOIN operations. Use parentheses () to specify the priority. 
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;

Resultado retornado:

+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| 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: Uso de JOIN e WHERE para consultar o número de registros cuja região é china e cujo campo shop_name possui o mesmo valor em ambas as tabelas. Todos os registros da tabela sale_detail são mantidos. Instruções de exemplo:

-- The full table scan feature must be enabled for partitioned tables. Otherwise, the JOIN operation fails. 
SET odps.sql.allow.fullscan=true;
-- Execute the following SQL statement: 
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 retornado:

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

Instrução de exemplo de uso incorreto:

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 retornado:

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

O resultado retornado corresponde à interseção das duas tabelas, e não a todas as linhas da tabela sale_detail.