not in 如何转化left join on提交效率

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/CNZYYH/article/details/79643454

以下是一个转换事例:

第一个样例:

select * from BUS_GG br WHERE 1 = 1 AND br.id NOT IN (SELECT RECEIVER_ID FROM SYS_GG);
       
select * from BUS_GG br left join SYS_GG k  on br.id = k.RECEIVER_ID where  k.RECEIVER_ID is null

 

第二个样例:

select * from BUS_CHANNEL br WHERE 1 = 1 AND br.CHANNEL_ID NOT IN (SELECT CHANNEL_ID FROM BUS_CHANNEL_PARAMETER where CHANNEL_ID=24);
       
select * from BUS_CHANNEL br left join BUS_CHANNEL_PARAMETER k  on br.CHANNEL_ID = k.CHANNEL_ID and k.CHANNEL_ID=24 where  k.CHANNEL_ID is null;

 

LEFT JOIN ON WHERE:在临时表生成后,再对临时表的数据进行过滤,再返回左表。

LEFT JOIN ON AND:在临时表生成的过程时,ON中的条件不管是否为真,都将返回左表。

展开阅读全文

没有更多推荐了,返回首页