MaxCompute SQL最佳實踐

MaxCompute SQL最佳實踐是在開發文檔的内容基礎上，提供的一些實踐性的使用指南，具體的内容見：MaxCompute SQL最佳實踐文檔，這裡選出了一些重要且常用的内容進行說明。内容包括：

快速掌握SQL寫法
分組取出每組資料的前N條
SQL實作多行資料轉一條
MaxCompute SQL中的JOIN ON條件

快速掌握SQL寫法

資料集準備

這裡以emp/dept表為示例資料集。單擊emp表資料檔案、dept表資料檔案下載下傳資料檔案。這裡将下載下傳的檔案存儲為：csv格式檔案，檔案中的資料内容不變。然後在MaxCompute項目上使用如下指令建立相應的表：

建立emp表：

-- 建立emp表
CREATE TABLE IF NOT EXISTS emp (
  EMPNO string ,
  ENAME string ,
  JOB string ,
  MGR bigint ,
  HIREDATE datetime ,
  SAL double ,
  COMM double ,
  DEPTNO bigint
);

建立dept表：

-- 建立dept表
CREATE TABLE IF NOT EXISTS dept (
  DEPTNO bigint ,
  DNAME string ,
  LOC string
);

建立表完成後，上傳資料使用odpscmd用戶端的tunnel指令，将下載下傳的資料檔案導入到資料表中，odpscmd用戶端以及MaxCompute Studio中使用odpscmd的開發環境搭建見：MaxCompute Studio資料開發工具的使用。上傳指令如下：

[email protected] YITIAN_BJ_MC>tunnel upload /Users/yitian/Documents/MaxCompute/maxcompute-data/emp.csv emp;

Upload session: 202004212318540c47df0b1829ea42
Start upload:/Users/yitian/Documents/MaxCompute/maxcompute-data/emp.csv
Using \n to split records
Upload in strict schema mode: true
Total bytes:744  Split input to 1 blocks
2020-04-21 23:18:55     scan block: '1'
2020-04-21 23:18:55     scan block complete, block id: 1
2020-04-21 23:18:55     upload block: '1'
2020-04-21 23:18:55     upload block complete, block id: 1
upload complete, average speed is 744 bytes/s
OK

[email protected] YITIAN_BJ_MC>tunnel upload /Users/yitian/Documents/MaxCompute/maxcompute-data/dept.csv dept;

上傳完成後，可使用如下指令進行來檢視導入資料的正确性：

[email protected] YITIAN_BJ_MC>select count(*) from emp;
+------------+
| _c0        |
+------------+
| 14         |
+------------+

常見問題

剛開始學習MaxCompute SQL時，可能會遇到的常見問題如下：

使用Group by時，Select的部分要麼是分組項，要麼是聚合函數。
Order by後面必須加Limit n。
Select表達式中不能用子查詢，可以改寫為Join。
Join不支援笛卡爾積，以及MapJoin的用法和使用場景。
Union all需要改成子查詢的格式。
In/Not in語句對應的子查詢隻能有一列，而且傳回的行數不能超過1000，否則也需要改成Join。

SQL示例

示例一：列出員勞工數大于零的所有部門。有如下兩種查詢方法：

-- 列出員勞工數大于0的所有部門，方法1
select d.deptno, d.dname, d.loc
from dept as d
join (
  select deptno, count(*) as num from emp
  group by deptno
) as e on d.deptno=e.deptno
where e.num>0;

-- 方法2
select d.*
from dept d
join (
  select distinct deptno as no
  from emp
) e on d.deptno=e.no;

傳回的結果均為：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

分析一下上面的兩個方法：

就本查詢來說，方法2的方式更為簡單，查詢效率應該更高，而且滿足正确的查詢需要。方法1使用了分組的思想來統計emp表中各部門的人數num，在進行join連接配接，并使用num作為過濾條件，這種方式的查詢效率低于方法2。但如果查詢需要統計：列出員勞工數大于零的所有部門，并列出各部門的總人數，這種情況下方法2就不能滿足需要了，但方法1中可以在第一行的輸出列中加入e.num，即可以完成該查詢需求。

示例二：列出薪金比SMITH多的所有員工（MapJoin使用場景）。這裡依然存在兩種查詢方案：

-- 列出薪資比smith多的所有員工，方法1
select empno, ename, sal from emp
join (
  select max(sal) as smith_sal from emp
  where ename='SMITH'
) s on emp.sal>s.smith_sal;

-- 方法2
select /*+ mapjoin(a)*/ e.empno, e.ename, e.sal
from emp e
join (
  select max(sal) as smith_sal from emp
  where ename='SMITH'
) a on e.sal>a.smith_sal;

傳回結果均為：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

方法1為普通的join連接配接查詢，但其實此時是MapJoin的典型場景：當一個大表和一個或多個小表JOIN時，您可以在SELECT語句中顯式指定MAPJOIN以提升查詢性能。這裡emp相對來說是大表，而子查詢為小表，是以可以使用mapjoin進行查詢，以提升查詢性能。

示例三：列出所有員工的姓名及其直接上級的姓名。自連接配接查詢場景：

-- 列出所有員工的姓名及其直接上級的姓名
select t1.ename as emp_name, t2.ename as leader_name
from emp t1
left outer join emp t2 on t1.mgr=t2.empno;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

示例四：列出基本薪金大于1500的所有工作（having語句使用場景）。下面有兩種查詢方法

-- 列出基本薪金大于1500的所有工作，方法1（錯誤）
select job, min(sal) as sal
from emp
where sal>1500
group by job;

-- 方法2（正确）
select job, min(sal) as sal
from emp
group by job
having min(sal)>1500;

以上的兩個方法，方法1的執行順序是：from-where-select-group by。是以它會先将sal>1500的所有job找到，然後在進行grouop by。此時由于job=SALESMAN的多條資料中，存在sal>1600的資料，是以最終查詢結果會包含SALESMAN。結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

但該查詢的需求中“基本工資”這裡文檔中了解為最低工資，而SALESMAN的最低工資為1250，是以它并不應該包含在最後的結果集中，此時就需要使用方法2進行查詢，方法2的查詢結果為正确的結果。傳回的内容如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

示例五：列出在每個部門工作的員工數量、平均工資和平均服務期限。時間處理上有很多好用的内建函數，如下所示。

-- 列出在每個部門工作的員工數量、平均工資和平均服務期限
select deptno,
       count(empno) as cnt_emp,
       round(avg(sal)) as avg_sal,
       round(avg(datediff(getdate(), hiredate, 'dd')), 2) as avg_hire
from emp
group by deptno;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

示例六：列出每個部門的薪水前3名的人員的姓名以及其排序（Top n的需求非常常見）。

-- 列出每個部門的薪水前3名的人員的姓名以及其排序
select *
from (
  select deptno,
         ename,
         sal,
         ROW_NUMBER() over (partition by deptno order by sal desc) as nums
  from emp
) t1
where t1.nums < 4;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

其中使用了MaxCompute中視窗函數ROW_NUMBER，該函數由于對視窗内分組資料進行排序，并傳回排序後的行号，指令格式如下，具體見：MaxCompute視窗函數文檔内容。

row_number() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

示例七：用一個SQL寫出每個部門的人數、CLERK（辦事員）的人數占該部門總人數占比。

-- 用一個SQL寫出每個部門的人數、CLERK（辦事員）的人數占該部門總人數占比
SELECT deptno, 
       COUNT(empno) AS cnt, 
       ROUND(SUM(CASE 
        WHEN job = 'CLERK' THEN 1 
        ELSE 0 
        END
       ) / COUNT(empno), 2) AS rate
FROM `EMP`
GROUP BY deptno;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

這裡對clerk人數統計時使用了MaxCompute中一些特有的函數，這部分函數包括CAST、DECODE、LEAST、ARRAY、SPLIT、MAP等，具體見：MaxCompute 其他函數文檔。

分組取出每組資料的前N條

這部分的内容其實上面已經使用過一次了（上面示例6），就是使用ROW_NUMBER視窗函數進行分組排序後，在将每組的前N條資料進行取出即可。指令如下：

SELECT * FROM (
  SELECT empno
  , ename
  , sal
  , job
  , ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) AS rn
  FROM emp
) tmp
WHERE rn < 10;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

此外，可以将所有職工根據部門分組，每個組内根據

sal

做降序排序，獲得職工自己組内的序号。

select deptno, 
       ename, 
       sal, 
       ROW_NUMBER() over (partition by deptno order by sal desc) as nums
from emp;
--執行結果如下。
+------------+-------+------------+------------+
| deptno     | ename | sal        | nums       |
+------------+-------+------------+------------+
| 10         | JACCKA | 5000.0     | 1          |
| 10         | KING  | 5000.0     | 2          |
| 10         | CLARK | 2450.0     | 3          |
| 10         | WELAN | 2450.0     | 4          |
| 10         | TEBAGE | 1300.0     | 5          |
| 10         | MILLER | 1300.0     | 6          |
| 20         | SCOTT | 3000.0     | 1          |
| 20         | FORD  | 3000.0     | 2          |
| 20         | JONES | 2975.0     | 3          |
| 20         | ADAMS | 1100.0     | 4          |
| 20         | SMITH | 800.0      | 5          |
| 30         | BLAKE | 2850.0     | 1          |
| 30         | ALLEN | 1600.0     | 2          |
| 30         | TURNER | 1500.0     | 3          |
| 30         | MARTIN | 1250.0     | 4          |
| 30         | WARD  | 1250.0     | 5          |
| 30         | JAMES | 950.0      | 6          |
+------------+-------+------------+------------+

SQL實作多行資料轉一條

這部分說明如何使用 SQL 實作多條資料壓縮為一條。

需求1：将emp表，根據job，擷取該job中的所有ename作為一列，格式如下：

job	ename
job1	ename1,ename2,ename3
job2	ename4,ename5

查詢語句如下：

-- 将emp表，根據job，擷取該job中的所有ename作為一列
select job, wm_concat(',', ename) as ename_list
from emp
group by job;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

需求2：在emp表中，統計每個部門的總人數，CLERK職位的人數及占比，MANAGER的總人數及占比，指令如下：

-- 在emp表中，統計每個部門的總人數，CLERK職位的人數及占比，MANAGER的總人數及占比
select deptno,
       count(empno),
       sum(case when job='CLERK' then 1 else 0 end) as clerk_cnt,
       round(sum(case when job='CLERK' then 1 else 0 end)/count(empno), 2) as clerk_rate,
       sum(case when job='MANAGER' then 1 else 0 end) as manager_cnt,
       round(sum(case when job='MANAGER' then 1 else 0 end)/count(empno), 2) as manager_rate
from emp
group by deptno;

傳回結果如下：

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

MaxCompute SQL中的JOIN ON條件

目前MaxCompute提供了以下幾種Join類型：

類型	含義
Inner Join	輸出符合關聯條件的資料。
Left Join	輸出左表的所有記錄，對于右表符合關聯的資料，輸出右表，沒有符合的，右表補null。
Right Join	輸出右表的所有記錄，對于左表符合關聯的資料，輸出左表，沒有符合的，左表補null。
Full Join	輸出左表和右表的所有記錄，對于沒有關聯上的資料，未關聯的另一側補null。
Left Semi Join	對于左表中的一條資料，如果右表存在符合關聯條件的行，則輸出左表。
Left Anti Join	對于左表中的一條資料，如果對于右表所有的行，不存在符合關聯條件的資料，則輸出左表。

根據不同的場景，可以使用不同的Join類型來實作對應的關聯操作。但是在實際使用過程中，存在如下錯誤示例。

A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

正确的join語句如下，應該是在A和B中擷取某一個分區的資料，然後在進行JOIN操作：

(SELECT * FROM A WHERE ds='20180101') A
(LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI)  JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key

然而針對不同的Join類型，兩者可能并不等價。

連接配接條件和執行順序

對于如下包含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}

中，有時結果是一緻的，有時候結果又是不一緻的。下面分情況進行讨論。

太長不看版

以下實驗的結論總結：

過濾條件放在不同的位置語義可能大不相同，如果隻是進行過濾資料後再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} 中。

如果還是覺得規則比較複雜，最好的方法就是把過濾條件寫到子查詢中。

詳細版：各類join操作實驗示例

資料準備

執行如下的建表語句：

-- JOIN操作示例資料
CREATE TABLE JOIN_A AS
  SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);

CREATE TABLE JOIN_B AS
  SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);

表A、表B的示例資料如下：

-- 表A示例資料
key	ds
+----+---+
1	20180101
2	20180101
2	20180102

-- 表B示例資料
key	ds
+----+---+
1	20180101
3	20180101
2	20180102

表A和表B的笛卡爾乘積如下：

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
1	20180101	3	20180101
1	20180101	2	20180102
2	20180101	1	20180101
2	20180101	3	20180101
2	20180101	2	20180102
2	20180102	1	20180101
2	20180102	3	20180101
2	20180102	2	20180102

inner join測試

Inner Join的處理邏輯是将左右表進行笛卡爾乘積，然後選擇滿足ON表達式的行進行輸出。

1. 第一種情況，在子查詢中過濾：

-- inner join 情況1
select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

結果如下：

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

2. 第二種情況，在JOIN 條件中過濾:

-- inner join 情況2
select a.*, b.*
from JOIN_A a
join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

笛卡爾積的結果有9條，滿足ON條件的結果同樣隻有1條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

3. 第三種情況，在JOIN後的WHERE條件過濾:

-- inner join 情況3
select a.*, b.*
from JOIN_A a
join JOIN_B b on a.key=b.key
where a.ds='20180101' and b.ds='20180101';

笛卡爾積的結果有9條，滿足ON條件

a.key = b.key

的結果有3條，如下。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180102	2	20180102
2	20180101	2	20180102

此時對于這個結果再進行過濾

A.ds='20180101' and B.ds='20180101'

，結果隻有1條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

可以看到，将過濾條件放在三個不同的地方，得到了相同的結果。僅此該部分的結論如下：

結論：inner join中，過濾條件在 {subquery_where_condition} 、 {on_condition} 和 {where_condition} 中都是等價的。

Left Join

Left Join的處理邏輯是将左右表進行笛卡爾乘積，然後對于滿足ON表達式的行進行輸出，對于左表中不滿足ON表達式的行，輸出左表，右表補NULL。

1. 第一種情況，在子查詢中過濾：

-- left join 情況1
select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a left join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

過濾後，左右側有兩條，右側有一條，結果有兩條：

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL

并且，left join等價于left outer join，上面指令等價于如下，查詢結果相同：

select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a left outer join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

2. 第二種情況，在JOIN 條件中過濾：

select a.*, b.*
from JOIN_A a
left join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

笛卡爾積的結果有9條，滿足ON條件的結果同樣隻有1條，則對于左表剩餘的兩條輸出左表，右表補NULL。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
2	20180102	NULL	NULL

3. 第三種情況，JOIN後的WHERE條件過濾:

-- left join 情況3
select a.*, b.*
from JOIN_A a
left join JOIN_B b on a.key=b.key
where a.ds='20180101' and b.ds='20180101';

笛卡爾積的結果有9條，滿足ON條件

a.key = b.key

的結果有3條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102

此時對于這個結果再進行過濾

A.ds='20180101' and B.ds='20180101'

，結果隻有1條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

可以看到，将過濾條件放在三個不同的地方，得到了三種不同的結果。

結論：過濾條件在 {subquery_where_condition} 、 {on_condition} 和 {where_condition} 不一定等價。

對于左表的過濾條件，放在 {subquery_where_condition} 和 {where_condition} 是等價的。

對于右表的過濾條件，放在 {subquery_where_condition} 和 {on_condition} 中是等價的。

Right Join

Right Join和Left Join是類似的，隻是左右表的差別，結論如下：

過濾條件在 {subquery_where_condition} 、 {on_condition} 和 {where_condition} 不一定等價。
對于右表的過濾條件，放在 {subquery_where_condition} 和 {where_condition} 是等價的。
對于左表的過濾條件，放在 {subquery_where_condition} 和 {on_condition} 中是等價的。

Full Join

FULL Join的處理邏輯是将左右表進行笛卡爾乘積，然後對于滿足ON表達式的行進行輸出，對于兩側表中不滿足ON表達式的行，輸出有資料的表，另一側補NULL。

1. 第一種情況，子查詢中過濾：

-- full join 情況1
select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a full join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

過濾後，左右側有兩條，右側有兩條，結果有三條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
NULL	NULL	3	20180101

2. 第二種情況，JOIN 條件中過濾:

-- full join 情況2
select a.*, b.*
from JOIN_A a
full join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

笛卡爾積的結果有9條，滿足ON條件的結果同樣隻有1條，則對于左表剩餘的兩條輸出左表，右表補NULL。右表剩餘的兩條輸出右表，左表補NULL。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
2	20180102	NULL	NULL
NULL	NULL	3	20180101
NULL	NULL	2	20180102

3. 第三種情況，JOIN後的WHERE條件過濾:

-- full join 情況3
select a.*, b.*
from JOIN_A a
full join JOIN_B b on a.key=b.key
where a.ds='20180101' and b.ds='20180101';

笛卡爾積的結果有9條，滿足ON條件

a.key = b.key

的結果有3條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102

再對沒有JOIN上的資料進行輸出，另一側補NULL，得到結果如下。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102
NULL	NULL	3	20180101

此時對于這個結果再進行過濾

A.ds='20180101' and B.ds='20180101'

，結果隻有1條。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

可以看到，和LEFT JOIN類似，得到了三種不同的結果。

結論：過濾條件寫在 {subquery_where_condition} 、 {on_condition} 和 {where_condition} 均不等價。

Left Semi Join

LEFT SEMI Join的處理邏輯是對于左表的每一條記錄，都去和右表進行比對，如果比對成功，則輸出左表。這裡需要注意的是由于隻輸出左表，是以JOIN後的Where條件中不能寫右側的過濾條件。LEFT SEMI JOIN常用來實作exists的語義。

1. 第一種情況，在子查詢中過濾：

-- left semi join 情況1
select a.*
from (
  select * from JOIN_A where ds='20180101'
) a left semi join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

過濾後，左右側有兩條，最終符合

a.key = b.key

的隻有一條。

a.key	a.ds
1	20180101

2. 第二種情況，JOIN 條件中過濾：

-- left semi join 情況2
select a.*
from JOIN_A a
left semi join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

對于左側的三條記錄，滿足ON條件的結果同樣隻有1條。

a.key	a.ds
1	20180101

3. 第三種情況，JOIN後的WHERE條件過濾：

-- left semi join 情況3
select a.*
from JOIN_A a
left semi join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key
where a.ds='20180101';

左側能符合ON條件的有一條。

a.key	a.ds
1	20180101

此時對于這個結果再進行過濾

A.ds='20180101'

，結果仍然保持1條。

a.key	a.ds
1	20180101

可以看到，LEFT SEMI JOIN和INNER JOIN類似，無論過濾條件放在哪裡，結果都是一緻的。

結論：過濾條件寫在 {subquery_where_condition} 、 {on_condition} 和 {where_condition} 是等價的。

Left Anti Join

LEFT ANTI Join的處理邏輯是對于左表的每一條記錄，都去和右表進行比對，如果右表所有的記錄都沒有比對成功，則輸出左表。同樣由于隻輸出左表，是以JOIN後的Where條件中不能寫右側的過濾條件。LEFT SEMI JOIN常常用來實作not exists的語義。

1. 第一種情況，子查詢中過：

-- left anti join 情況1
select a.*
from (
  select * from JOIN_A where ds='20180101'
) a left anti join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

過濾後，左側有兩條，右側有兩條，結果有1條。

a.key	a.ds
2	20180101

2. 第二種情況，JOIN 條件中過濾：

-- left anti join 情況2
select a.*
from JOIN_A a
  left anti join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

對于左側的三條記錄，隻有第一條有滿足ON條件的結果，是以輸出剩餘的兩條記錄。

a.key	a.ds
2	20180101
2	20180102

3. 第三種情況，JOIN後的WHERE條件過濾：

-- left anti join 情況3
select a.*
from JOIN_A a
  left anti join (
  select * from JOIN_B where ds='20180101'
  ) b on a.key=b.key
where a.ds='20180101';

左側能通過ON條件的有兩條。

a.key	a.ds
2	20180101
2	20180102

此時對于這個結果再進行過濾

A.ds='20180101'

，結果為1條。

a.key	a.ds
2	20180101

可以看到，LEFT ANTI JOIN中，過濾條件WHERE語句分别放在JOIN ON條件中、條件前和條件後，得到的結果是不相同的。

結論：過濾條件寫在 {subquery_where_condition} 、 {on_condition} 和 {where_condition} 不一定等價。

對于左表的過濾條件，放在 {subquery_where_condition} 和 {where_condition} 是等價的。

對于右表的過濾條件，放在 {subquery_where_condition} 和 {on_condition} 中是等價的，右表表達式不能放在 {where_condition} 中。

以上内容隻是針一個常用場景測試的幾種不同的寫法，沒有具體的推導過程，對于涉及到不等值表達式的場景會更加複雜，如果您有興趣可以嘗試推導一下。

MaxCompute SQL資料開發最佳實踐MaxCompute SQL最佳實踐快速掌握SQL寫法分組取出每組資料的前N條SQL實作多行資料轉一條MaxCompute SQL中的JOIN ON條件

MaxCompute SQL最佳實踐

快速掌握SQL寫法

資料集準備

常見問題

SQL示例

分組取出每組資料的前N條

SQL實作多行資料轉一條

MaxCompute SQL中的JOIN ON條件

連接配接條件和執行順序

太長不看版

詳細版：各類join操作實驗示例

資料準備

inner join測試

Left Join

Right Join

Full Join

Left Semi Join

Left Anti Join

繼續閱讀

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

【python】【資料處理】畫多元資料分布圖

sqlServer根據經緯查距離