left join 和 left semi join:
Table A:
id name
1 a
2 b
3 c
Table B:
id age
1 10
2 20
首先使用 left join :
select * from a
left join b
on a.id = b.id;
结果:
1 a 1 10
2 b 2 20
3 c null null
可以看到 left join 返回:
- 左表(A)所有记录
- 右表(B)有匹配记录则返回,无匹配记录使用null填充
再使用left semi join :
select * from a
left semi join b
on a.id = b.id;
结果:
1 a
2 b
3 c
left semi join 只返回:
- 左表(A)所有的记录
- 不返回右表(B)的任何记录
所以通过验证表明:
- left join 返回左右表所有记录
- left semi join 只返回左表记录
left semi join 只返回左表记录,那何必使用它呢?
left semi join 有两个具体的用途:
- 过滤右表记录:
通过 ON 条件只匹配右表中的部分记录,从而完成对右表的过滤。
这比 directly 选 left table 快,因为不需要全表扫描。
- 子查询优化:
可以替代使用 right table 在 where 中国 in/not in 的子查询。
这可以有效提高性能。
举个例子:
-- 使用子查询
SELECT * FROM left_table
WHERE id IN (SELECT id FROM right_table WHERE condition)
-- 等价于使用left semi join
SELECT *
FROM left_table
LEFT SEMI JOIN right_table
ON left_table.id = right_table.id
WHERE right_table.condition
二者效果相同,但 left semi join 的性能更高。
所以总的来说,left semi join 有两个主要用途:
- 过滤右表记录
- 替代性能较差的子查询
它看起来多余,但实际上可以提高查询效率。
left anti join
假设我们有以下两个表:
table1:
id name
1 a
2 b
3 c
4 d
table2:
id age
1 20
2 30
现在我们要查询table1中不在table2中的记录,可以使用left anti join:
select * from table1
left anti join table2
on table1.id = table2.id
执行结果为:
3 c
4 d
只返回了table1中的id=3 和 id=4的记录。
原因是:
- left anti join会返回左表(table1)所有记录
- 但是排除与 ON条件匹配的右表(table2)的记录
也就是说只返回左表中不在右表中的记录。
而使用普通的left join会得到:
1 a 1 20
2 b 2 30
3 c
4 d
返回左表所有记录,与右表匹配的用右表值,否则用null填充。
所以通过对比,我们可以看出:
- left anti join只返回左表不在右表中的记录
- left join返回左表所有记录,带上右表匹配与否的内容
总的来说:
- left semi join 会返回左表所有记录,并包含匹配的右表记录
- left anti join 会返回左表所有记录,但排除匹配的右表记录
版权声明:本文为weixin_45017098原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。