背景及目的
本文结果都是在SQL标准语义模式下的推导结果，希望大家都能够按照标准的SQL语义来写SQL，这样才能保证后续SQL的可移植性。
SQL概述
MaxCompute SQL适用于海量数据（GB、TB、EB级别），离线批量计算的场合。MaxCompute作业提交后会有几十秒到数分钟不等的排队调度，所以适合处理跑批作业，一次作业批量处理海量数据，不适合直接对接需要每秒处理几千至数万笔事务的前台业务系统。
MaxCompute SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不能因此简单地把MaxCompute等价成一个数据库，它在很多方面并不具备数据库的特征，如事务、主键约束、索引等，更多差异请参见与其他SQL语法的差异。目前在MaxCompute中允许的最大SQL长度是3MB。
类型转换说明
MaxCompute SQL允许数据类型之间的转换，类型转换方式包括显式类型转换和隐式类型转换。更多详情请参见类型转换。
- 显式类型转换：是指用cast将一种数据类型的值转换为另一种类型的值的行为。
- 隐式类型转换：是指在运行时，由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。隐式转换作用域包括各种运算符、内建函数等作用域。
分区表
MaxCompute SQL支持分区表。指定分区表会对您带来诸多便利，例如提高SQL运行效率、减少计费等。关于分区的详情请参见基本概念>分区。
UNION ALL
参与UNION ALL运算的所有列的数据类型、列个数、列名称必须完全一致，否则会报异常。
使用限制
SQL限制项请参见SQL限制项汇总，不支持的DDL及DML语法请参见与其他SQL语法的差异。
其他限制：
- SCALAR SUBQUERY限制
- Insert values限制：values必须是常量
- MaxCompute最多允许256个表的union all/union
- Mapjoin的小表不能超过512MB
- 由于国际标准化组织发布的中国时区信息调整，执行相关SQL时，日期显示某些时间段会存在时间差异：1900-1928年的日期时间差异5分52秒，1900年之前的日期时间差异9秒。
快速体验
通过MaxCompute客户端，快速查看基础语句：
- 进入项目空间：use ;
- 查看Tables：
show tables; --列出当前项目空间下所有的表。
show tables like 'daniel'; --列出当前项目空间下表名与'daniel'匹配上的表，支持正则表达式。
show partitions; --table_name：指定查询的表名称（表不存在或非分区表报错）
- 创建Table：
CREATE TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[LIFECYCLE days]
[AS select_statement]
CREATE TABLE [IF NOT EXISTS] table_name
LIKE existing_table_name- Describe Table
DESC <table_name>; -- table_name：表名或视图名称
DESC extended <table_name>;--查看外部表信息查看分区：
desc table_name partition(pt_spec)- 删除Table
DROP TABLE [IF EXISTS] table_name; -- table_name：要删除的表名。DDL语句（表操作、生命周期操作、分区和列操作、视图操作）参考更多。
数据类型
MaxCompute类型
基本数据类型：
复杂数据类型
RDBMS与MaxCompute
Hive与MaxCompute
MaxCompute与Hive的数据类型映射如下：
| Hive 数据类型 | MaxCompute 数据类型 |
|---|---|
| BOOLEAN | BOOLEAN |
| TINYINT | TINYINT |
| SMALLINT | SMALLINT |
| INT | INT |
| BIGINT | BIGINT |
| FLOAT | FLOAT |
| DOUBLE | DOUBLE |
| DEICIMAL | DEICIMAL |
| STRING | STRING |
| VARCHAR | VARCHAR |
| CHAR | STRING |
| BINARY | BINARY |
| TIMESTAMP | TIMESTAMP |
| DATE | Datetime |
| ARRAY | ARRAY |
| MAP | MAP |
| STRUCT | STRUCT |
| UNION | 不支持 |
数据操作
更新表数据
如何使用Insert into和Insert overwrite两种命令更新表数据。
insert操作
介绍如何使用Insert into和Insert overwrite两种命令更新表数据。
INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)]
select_statement
FROM from_statement;说明：
- MaxCompute的Insert语法与通常使用的MySQL或Oracle的Insert语法有差别，在insert overwrite|into后需要加入table关键字，而非直接使用tablename。
- 当Insert的目标表是分区表时，指定分区值[PARTITION (partcol1=val1, partcol2=val2 …)]语法中不允许使用函数等表达式。
- 目前INSERT OVERWRITE还不支持指定插入列的功能，暂时只能用INSERT INTO。
- 不支持insert into到hash clustering表
Insert into与Insert overwrite的区别是：Insert into会向表或表的分区中追加数据，而Insert overwrite会在向表或分区中插入数据前清空表中的原有数据。
在使用MaxCompute处理数据的过程中， Insert overwrite/into是最常用到的语句，它们会将计算的结果保存到一个表中，以供下一步计算使用。比如计算sale_detail表中不同地区的销售额，操作如下：
create table sale_detail_insert like sale_detail;
alter table sale_detail_insert add partition(sale_date='2013', region='china');
insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
select shop_name, customer_id,total_price from sale_detail;向某个分区插入数据时，分区列不允许出现在select列表中。
insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
select shop_name, customer_id, total_price, sale_date, region from sale_detail;
-- 报错返回，sale_date，region为分区列，不允许出现在静态分区的insert语句中。同时，partition的值只能是常量，不可以出现表达式。以下用法是非法的：
insert overwrite table sale_detail_insert partition (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
select shop_name, customer_id, total_price from sale_detail;动态分区操作
动态分区使用注意事项：
- 在您insert into partition时，如果分区不存在，会自动创建分区。
- 如果多个insert into partition作业并发，同时发现分区不存在，都会主动创建分区，但是同时只有一个会创建成功，其它的都会失败。
- insert into partition作业如果不能控制并发，只能通过预创建分区来避免问题。
MULTI INSERT
MaxCompute SQL支持在一个语句中插入不同的结果表或者分区实现多路输出。
FROM from_statement
INSERT OVERWRITE | INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 [FROM from_statement]
[INSERT OVERWRITE | INTO TABLE tablename2 [PARTITION (partcol1=val3, partcol2=val4 ...)]
select_statement2 [FROM from_statement]]说明：
- 一般情况下，单个SQL中最多可以写256路输出，超过256路，则报语法错误。
- 在一个multi insert中：对于分区表，同一个目标分区不允许出现多次。对于未分区表，该表不能出现多次。
- 对于同一张分区表的不同分区，不能同时有Insert overwrite和Insert into操作，否则报错返回。
示例如下：
create table sale_detail_multi like sale_detail;
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price where .....
insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price where .....;
-- 成功返回，将sale_detail的数据插入到sales里的 2010 年及2011年中国大区的销售记录中。
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price;
-- 出错返回，同一分区出现多次。
from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert into table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price;
-- 出错返回，同一张表的不同分区，不能同时有insert overwrite和insert into操作。VALUES
通常在业务测试阶段，需要给一个小数据表准备些基本数据，您可以通过 INSERT … VALUES的方法快速对测试表写入一些测试数据。
命令格式如下：
INSERT INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)][co1name1,colname2...]
[VALUES (col1_value,col2_value,...),(col1_value,col2_value,...),...]示例一：
drop table if exists srcp;
create table if not exists srcp (key string ,value bigint) partitioned by (p string);
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);INSERT … VALUES语句执行成功后，查询表srcp分区p=abc，结果如下：
+-----+------------+---+
| key | value | p |
+-----+------------+---+
| a | 1 | abc |
| b | 2 | abc |
| c | 3 | abc |
+-----+------------+---+当表有很多列，而准备数据的时候希望只插入部分列的数据，此时可以使用插入列表功能。
示例二：
drop table if exists srcp;
create table if not exists srcp (key string ,value bigint) partitioned by (p string);
insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');INSERT … VALUES语句执行成功后，查询表srcp分区p=20170101，结果如下：
+-----+------------+---+
| key | value | p |
+-----+------------+---+
| d | NULL | 20170101 |
| e | NULL | 20170101 |
| f | NULL | 20170101 |
+-----+------------+---+对于在values中没有制定的列，可以看到取缺省值为NULL。插入列表功能不一定和values一起用，对于insert into…select…，同样可以使用。
实际上，values表并不限于在Insert语句中使用，任何DML语句都可以使用。
INSERT … VALUES有一个限制：values必须是常量，但是有时候希望在插入的数据中进行一些简单的运算，此时可以使用MaxCompute的values table功能，详情见示例三。
示例三：
drop table if exists srcp;
create table if not exists srcp (key string ,value bigint) partitioned by (p string);
insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'20170102' from values ('d',4),('e',5),('f',6) t(a,b);其中的values (…), (…) t (a, b)相当于定义了一个名为t，列为a，b的表，类型为（a string，b bigint），其中的类型从values列表中推导。这样在不准备任何物理表的时候，可以模拟一个有任意数据的，多行的表，并进行任意运算。
INSERT … VALUES语句执行成功后，查询表srcp分区p=‘20170102’，结果如下：
+-----+------------+---+
| key | value | p |
+-----+------------+---+
| d4 | 2 | 20170102 |
| e5 | 2 | 20170102 |
| f6 | 2 | 20170102 |
+-----+------------+---+VALUES TABLE这个用法还可以取代 select * from dual与 union all组合的方式，来拼出常量表。如下：
select 1 c from dual
union all
select 2 c from dual;
--等同于
select * from values (1), (2) as t (c);还有一种values表的特殊形式，如下所示：
select abs(-1), length('abc'), getdate();如上述语句所示，可以不写from语句，直接执行select，只要select的表达式列表不用任何上游表数据就可以。其底层实现为从一个1行，0列的匿名values表选取。这样，在希望测试一些函数，比如自己的UDF等时，便不用再手工创建DUAL表。
SQL查询
MaxCompute SQL中，很常用的一个操作就是关联(Join)。目前MaxCompute提供了一下几种Join类型：
| 类型 | 含义 |
|---|---|
| Inner Join | 输出符合关联条件的数据 |
| Left Join | 输出左表的所有记录，对于右表符合关联的数据，输出右表，没有符合的，右表补NULL |
| Right Join | 输出右表的所有记录，对于左表符合关联的数据，输出左表，没有符合的，左表补NULL |
| Full Join | 输出左表和右表的所有记录，对于没有关联上的数据，未关联的另一侧补null |
| Left Semi Join | 对于左表中的一条数据，如果右表存在符合关联条件的行，则输出左表 |
| Left Anti Join | 对于左表中的一条数据，如果对于右表所有的行，不存在符合关联条件的数据，则输出左表 |
| User Defined Join | 指定两个输入流，用户自己实现Join的逻辑，这里不展开讨论 |
根据不同的场景，用户可以使用不同的Join类型来实现对应的关联操作。但是在实际使用过程当中，经常有用户分不清楚过滤条件在JOIN ON语句中还是在WHERE中有什么区别，或者认为他们的效果都是一样的，例如在生产的环境中经常可以看到用户写了
A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';这里用户的本意是希望在A和B中获取某一个分区的数据进行JOIN操作，也就是
(SELECT * FROM A WHERE ds='20190121') A
(LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key然而针对不同的Join类型，两者可能并不等价，不仅无法将分区条件下推，导致全表扫描，而且会导致正确性问题。这里简要辨析一下过滤条件分别在以下的的异同
- 子查询的WHERE条件
- JOIN ON条件
- JOIN ON后的WHERE条件
1 原理
这里先说明一个JOIN和WHERE条件的计算顺序，对于
(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}来说，计算顺序为
- 子查询中的{subquery_where_condition}
- JOIN的{on_condition}的条件
- JOIN结果集合{where_condition}的计算
对于不同的JOIN类型，滤语句放在{subquery_where_condition}、{on_condition}和{where_condition}中，有时结果是一致的，有时候结果又是不一致的。下面分情况进行讨论：
2 实验
2.1 准备
首先构造表A
CREATE TABLE A AS SELECT * FROM VALUES (1, 20190121),(2, 20190121),(2, 20190122) t (key, ds);| key | ds |
|---|---|
| 1 | 20190121 |
| 2 | 20190121 |
| 2 | 20190122 |
则他们的笛卡尔乘积为
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 1 | 20190121 | 3 | 20190121 |
| 1 | 20190121 | 2 | 20190122 |
| 2 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | 3 | 20190121 |
| 2 | 20190121 | 2 | 20190122 |
| 2 | 20190122 | 1 | 20190121 |
| 2 | 20190122 | 3 | 20190121 |
| 2 | 20190122 | 2 | 20190122 |
2.2 Inner Join
结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中都是等价的。
Inner Join的处理逻辑是将左右表进行笛卡尔乘积，然后选择满足ON表达式的行进行输出。
第一种情况，子查询中过滤：
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;非常简单，结果只有一条
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
第二种情况，JOIN 条件中过滤
SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';笛卡尔积的结果有9条，满足ON条件的结果同样只有1条。
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
第三种情况，JOIN后的WHERE条件过滤
SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20190121' and B.ds='20190121';来说，笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，分别是
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | 2 | 20190122 |
| 2 | 20190122 | 2 | 20190122 |
此时对于这个结果再进行过滤A.ds='20190121' and B.ds='20190121'，结果只有1条，和刚才的结果一致
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
2.3 Left Join
结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
对于左表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
对于右表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的。
Left Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于左表中不满足ON表达式的行，输出左表，右表补NULL。
第一种情况，子查询中过滤：
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;
过滤后，左右侧有两条，右侧有一条，结果有两条| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | NULL | NULL |
第二种情况，JOIN 条件中过滤
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';笛卡尔积的结果有9条，满足ON条件的结果同样只有1条，则对于左表剩余的两条输出左表，右表补NULL。
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | NULL | NULL |
| 2 | 20190122 | NULL | NULL |
第三种情况，JOIN后的WHERE条件过滤：
SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20190121' and B.ds='20190121';来说，笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，分别是
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | 2 | 20190122 |
| 2 | 20190122 | 2 | 20190122 |
此时对于这个结果再进行过滤A.ds='20190121' and B.ds='20190121'，结果只有1条
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
可以看到，将过滤条件放在三个不同的地方，得到了三种不同的结果。
2.4 Right Join
Right Join和Left Join是类似的，只是左右表的区别。
结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
对于右表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
对于左表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的。
2.5 Full Join
结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}均不等价。
FULL Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于两侧表中不满足ON表达式的行，输出有数据的表，另一侧补NULL。
第一种情况，子查询中过滤：
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
FULL JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;过滤后，左右侧有两条，右侧有两条，结果有三条
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | NULL | NULL |
| NULL | NULL | 3 | 20190121 |
第二种情况，JOIN 条件中过滤：
SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';笛卡尔积的结果有9条，满足ON条件的结果同样只有1条，则对于左表剩余的两条输出左表，右表补NULL。右表剩余的两条输出右表，左表补NULL
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | NULL | NULL |
| 2 | 20190122 | NULL | NULL |
| NULL | NULL | 3 | 20190121 |
| NULL | NULL | 2 | 20190122 |
第三种情况，JOIN后的WHERE条件过滤
SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key
WHERE A.ds='20190121' and B.ds='20190121';笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，分别是
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | 2 | 20190122 |
| 2 | 20190122 | 2 | 20190122 |
再对没有JOIN上的数据进行输出，另一侧补NULL，得到结果
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
| 2 | 20190121 | 2 | 20190122 |
| 2 | 20190122 | 2 | 20190122 |
| NULL | NULL | 3 | 20190121 |
此时对于这个结果再进行过滤A.ds='20190121' and B.ds='20190121'，结果只有1条
| a.key | a.ds | b.key | b.ds |
|---|---|---|---|
| 1 | 20190121 | 1 | 20190121 |
可以看到，和LEFT JOIN类似，得到了三种不同的结果。
2.6 Left Semi Join
结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}是等价的。
LEFT SEMI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果匹配成功，则输出左表。这里需要注意的是由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常常用来实现exists的语义
第一种情况，子查询中过滤：
SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;过滤后，左右侧有两条，最终符合a.key = b.key的只有一条
| a.key | a.ds |
| -------- | -------- |
| 1 | 20190121 |
第二种情况，JOIN 条件中过滤：
SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';对于左侧的三条记录，满足ON条件的结果同样只有1条
| a.key | a.ds|
| -------- | -------- |
|1 | 20190121 |
第三种情况，JOIN后的WHERE条件过滤
SELECT A.*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key
WHERE A.ds='20190121';左侧能符合ON条件的有一条
| a.key | a.ds|
| -------- | -------- |
|1 | 20190121 |
此时对于这个结果再进行过滤A.ds='20190121'，结果仍然保持1条
| a.key | a.ds|
| -------- | -------- |
| 1 | 20190121 |
可以看到，LEFT SEMI JOIN和INNER JOIN类似，无论过滤条件放在哪里，结果都是一致的。
2.7 Left Anti Join
结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
对于左表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
对于右表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的，右表表达式不能放在{where_condition}中。
LEFT ANTI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果右表所有的记录都没有匹配成功，则输出左表。同样由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常常用来实现not exists的语义。
第一种情况，子查询中过滤：
SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20190121') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key;过滤后，左侧有两条，右侧有两条，结果有1条
| a.key | a.ds |
|---|---|
| 2 | 20190121 |
第二种情况，JOIN 条件中过滤：
SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20190121' and B.ds='20190121';对于左侧的三条记录，只有第一条有满足ON条件的结果，所以输出剩余的两条记录
| a.key | a.ds |
|---|---|
| 2 | 20190121 |
| 2 | 20190122 |
第三种情况，JOIN后的WHERE条件过滤
SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20190121') B
ON a.key = b.key
WHERE A.ds='20190121';左侧能通过ON条件的有两条
| a.key | a.ds |
|---|---|
| 2 | 20190121 |
| 2 | 20190122 |
此时对于这个结果再进行过滤A.ds='20190121'，结果为1条
| a.key | a.ds |
|---|---|
| 2 | 20190121 |
可以看到，LEFT ANTI JOIN中，过滤条件放在JOIN ON条件中和前后的WHERE条件中，结果是不相同的。
以上只是针对一个常用场景的几种不同的写法做的简单的测试，没有具体的推导过程，对于涉及到不等值表达式的场景会更加复杂，有兴趣的同学可以自己尝试推导一下。
3 总结
过滤条件放在不同的位置语义可能大不相同，对于用户而言，如果只是进行过滤数据后再JOIN的操作，可以简要记住以下几点，当然如果还是觉得规则比较复杂的话，那最好的方法就是每次都把过滤条件写到子查询中，虽然这样写起来会啰嗦一些。
- INNER JOIN/LEFT SEMI JOIN 对于两侧的表达式可以随便写。
- LEFT JOIN/LEFT ANTI JOIN 左表的过滤条件要放到{subquery_where_condition}或者{where_condition}，右表的过滤条件要放到{subquery_where_condition}或者{on_condition}中。
- RIGHT JOIN和LEFT JOIN相反，右表的过滤条件要放到{subquery_where_condition}或者{where_condition}，左表的过滤条件要放到{subquery_where_condition}或者{on_condition}。
- FULL OUTER JOIN 只能放到{subquery_where_condition}中。