关于in/not in ,exists/not exists 以及left join and null 的执行效率对比

关于in/not in ,exists/not exists 以及left join and null 的执行效率对比

---

先抛出执行效率结论：left join > exists/not exists > in/not in

一、建表以及插入测试数据

create table test (
`id` string comment '用户id',
`taskno` string comment 'task',
`part` string comment '访问时间')
 comment '用户访问记录表'
row format delimited fields terminated by '\t'
lines terminated by '\n' stored as orc;

insert overwrite table test
select '1001' as id,	'10011' as taskno,	'轮胎' 				as part union all
select '1001' as id,	'10011' as taskno,	'前保险杠皮' 		as part union all
select '1002' as id,	'10021' as taskno,	'钢圈' 				as part union all
select '1001' as id,	'10011' as taskno,	'前下摆臂(右)'