ODPS JOIN

阿里云中Max Compute（原ODPS）计算引擎学习备忘录。

子查询

子查询等价：https://help.aliyun.com/document_detail/73781.html?spm=a2c4g.11186623.6.695.bfdd28dc5TaSoL#title-kho-mt6-6rm
以下in语句只在ODPS2中生效。但是可以清楚地看到semi join与anti join的含义。

Semi join： 只留下右表中id出现的行

类似交集 union

LEFT SEMI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果匹配成功，则输出左表。这里需要注意的是由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常用来实现exists的语义。

select * from mytable1 where id in (select id from mytable2);
--等效于以下语句。
select * from mytable1 a left semi join mytable2 b on a.id = b.id;

Anti join：过滤去掉右表中id出现的行

类似补集 except / minus

LEFT ANTI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果右表所有的记录都没有匹配成功，则输出左表。同样由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常常用来实现not exists的语义。

select * from mytable1 where id not in (select id from mytable2);
--如果mytable2中的所有id都不为NULL，则等效于以下语句。
select * from mytable1 a left anti join mytable2 b on a.id = b.id;

JOIN

详细语法说明：https://help.aliyun.com/document_detail/73783.html?spm=a2c4g.11186623.2.11.66bd7eb5iuC82y#title-6wq-wbn-brl

MaxCompute的JOIN操作分为左连接、右连接、全连接、内连接。以下四种命令中的outer / inner都可以省略。

• Left Outer Join：左连接。返回左表中的所有记录，即使右表中没有与之匹配的记录。如果右表中没有匹配，则结果为 NULL。
  Left Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于左表中不满足ON表达式的行，输出左表，右表补NULL。
  https://www.runoob.com/sql/sql-join-left.html

说明 如果右边值不唯一，建议不要连续使用过多left join，以免在join的过程中产生数据膨胀，导致作业停滞。因为若右表中有N行对应，则结果表中会生成N行。若无对应，则会填null。

• Right Outer Join：右连接。返回右表中的所有记录，即使左表中没有与之匹配的记录。

• Full Outer Join：全连接。返回左右表中的所有记录。
  FULL Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于两侧表中不满足ON表达式的行，输出有数据的表，另一侧补NULL。

• Inner Join：内连接，关键字inner可以省略。表中存在至少一个匹配时，inner join返回行。
  Inner Join的处理逻辑是将左右表进行笛卡尔乘积，然后选择满足ON表达式的行进行输出。
  https://www.runoob.com/sql/sql-join-inner.html

JOIN ON条件与where条件与子查询中的where条件辨析

https://help.aliyun.com/document_detail/89993.html