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

最新推荐文章于 2023-10-18 11:33:23 发布

原创最新推荐文章于 2023-10-18 11:33:23 发布 · 7.5k 阅读

9 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

7 篇文章

订阅专栏

该博客讨论了SQL中的左连接（LEFT JOIN）和内连接（JOIN）操作，通过示例展示了如何从多个表中获取和合并数据。内容包括创建临时表tmp1, tmp2和tmp3，然后使用LEFT JOIN和JOIN组合查询这些表，以获取id, city和amount信息。最终展示的查询结果分别展示了不同联接方式下的输出差异。

数据

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