欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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;

left join和left semi join的区别

SELECT * from left_semi_join2;

left join和left semi join的区别

SELECT  *
FROM    left_semi_join1 a
LEFT SEMI JOIN left_semi_join2 b
ON      a.id = b.id
;

left join和left semi join的区别

SELECT  *
FROM    left_semi_join1 a
where a.id in (select id from left_semi_join2)
;

left join和left semi join的区别 

SELECT  *
FROM    left_semi_join1 a
LEFT JOIN left_semi_join2 b
ON      a.id = b.id
;

left join和left semi join的区别

什么时候使用left semi join?

当左表与右表的关联列都存在重复数据时,由于产生笛卡尔积,使用left join是低效的。此时采用left semi join

当左表left semi join右表时,结果表只能有左表的列,且右表只能在on中设置过滤条件,并且当右表有重复数据时,左表只会关联一次。