SQL替换NOT IN的几种方法总结

OT IN很好用,但效率很低,共有如下几种方法可以替换,我用几十万数据的测试,最后一种效率是最好的

select email from email where email not in(select distinct email from log);

SELECT email FROM email WHERE not exists (select email from log where log.email=email.email);

SELECT email.email, email.val FROM (SELECT email.*, log.email FROM email LEFT JOIN log ON email.email=log.email) AS res WHERE res.log.email is null;

测试表结构和数据如下

email
email val
1 a
2 b
2 b
3 c
3 c
3 c
4 d
4 d
4 d
4 d
log
email
1
3
3
5
5
5
5
5

运行结果

email val
2 b
2 b
4 d
4 d
4 d
4 d
阅读更多
想对作者说点什么?

博主推荐

换一批

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