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

编程开发 / / at

NOT 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

2条回应:“SQL替换NOT IN的几种方法总结”

  1. 3D电影说道:

    这个方法不错。。。

发表评论

电子邮件地址不会被公开。 必填项已用*标注