子查询不在性能问题中
我有一个缓慢的查询，
select * from table1 where id NOT IN ( select id from table2 )
通过执行以下操作会更快（不确定这是否可能）：
select * from table1 where id not in ( select id from table2 where id = table1.id )
或者：
select * from table1 where table1.id NOT EXIST( select id from table2 where table2.id = table1.id )
或者：
select * from table1
left join table2 on table2.id = table1.id
WHERE table2.id is null
或者做其他事情？就像把它分成两个查询一样......
I have this slow query
select * from table1 where id NOT IN ( select id from table2 )
Would this be faster by doing something like (not sure if this is possible):
select * from table1 where id not in ( select id from table2 where id = table1.id )
Or:
select * from table1 where table1.id NOT EXIST( select id from table2 where table2.id = table1.id )
Or:
select * from table1
left join table2 on table2.id = table1.id
WHERE table2.id is null
Or do something else? Like break it up into two queries ...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
问题是 - 比较中的字段是否可以为空（即列值可以为 NULL）吗？
如果它们可以为空...
...在 MySQL 中
NOT IN或NOT EXISTS性能更好 - 请参阅 此链接。如果它们NOT可为空...
...
LEFT JOIN / IS NULL表现更好 - 请参阅此链接。The question is - are the field(s) in the comparison nullable (meaning, can the column value be NULL)?
If they're nullable...
...in MySQL the
NOT INorNOT EXISTSperform better - see this link.If they are NOT nullable...
...
LEFT JOIN / IS NULLperforms better - see this link.目的是摆脱 NOT IN
The object being to get rid of NOT IN