A left join B join C 和 A left join (B join C)有什么区别

数据

表A

id	city
1	a
2	b
3	c
4	d

 

表B

city	name
a	x1
b	x2
c	x3

 

表C

name	amount
x1	100
x2	200

 

WITH tmp1 AS (
SELECT 1 AS id, 'a' AS city
UNION ALL
SELECT 2 AS id, 'b' AS city
UNION ALL
SELECT 3 AS id, 'c' AS city
UNION ALL
SELECT 4 AS id, 'd' AS city
UNION ALL
SELECT 5 AS id, 'e' AS city
),
tmp2 AS (
SELECT 'a' AS city, 'x1' AS name
UNION ALL
SELECT 'b' AS city, 'x2' AS name
UNION ALL
SELECT 'c' AS city, 'x3' AS name    
),
tmp3 AS (
SELECT 'x1' AS name, 100 AS amount
UNION ALL
SELECT 'x2' AS name, 200 AS amount  
)



--1：A left join B join C
SELECT a.id,b.city,c.amount FROM tmp1 a
	LEFT JOIN tmp2 b ON a.city = b.city
	JOIN tmp3 c ON b.name = c.name;

--2：A left join (B join C)
SELECT a.id,d.*
from (
	SELECT *
	FROM tmp1
 )a 
LEFT JOIN (
	select b.city,c.amount from tmp2 b 
	JOIN tmp3 c ON b.name = c.name
)d
on a.city =d.city

结果1:

id	city	amount
1	a	100
2	b	200

 

结果2：

id	city	amount
1	a	100
2	b	200
3
4
5

 

以上。