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