left join和left semi join的区别
程序员文章站
2022-07-01 08:27:11
...
--建表
create TABLE left_semi_join1(
id int,
name STRING
);
create TABLE left_semi_join2(
id int,
age STRING
);
--插入数据
INSERT INTO TABLE left_semi_join1 VALUES (1,'james'),(2,'hsl'),(3,'james'),(4,'hsl');
INSERT INTO TABLE left_semi_join2 VALUES (1,'22'),(2,'33'),(3,'44');
SELECT * from left_semi_join1;
SELECT * from left_semi_join2;
SELECT *
FROM left_semi_join1 a
LEFT SEMI JOIN left_semi_join2 b
ON a.id = b.id
;
SELECT *
FROM left_semi_join1 a
where a.id in (select id from left_semi_join2)
;
SELECT *
FROM left_semi_join1 a
LEFT JOIN left_semi_join2 b
ON a.id = b.id
;
什么时候使用left semi join?
当左表与右表的关联列都存在重复数据时,由于产生笛卡尔积,使用left join是低效的。此时采用left semi join
当左表left semi join右表时,结果表只能有左表的列,且右表只能在on中设置过滤条件,并且当右表有重复数据时,左表只会关联一次。
推荐阅读
-
SQL中的left join right join
-
oracle中left join和right join的区别浅谈
-
详解Node.js中path模块的resolve()和join()方法的区别
-
深入Oracle的left join中on和where的区别详解
-
sql的left join 、right join 、inner join之间的区别
-
数据库Left join , Right Join, Inner Join 的相关内容,非常实用
-
SQL联合查询inner join、outer join和cross join的区别详解
-
解析mysql left( right ) join使用on与where筛选的差异
-
详解Node.js中path模块的resolve()和join()方法的区别
-
SQL中的left join right join