我是否应该使用 notists 或 join 语句来过滤掉 NULL？
SELECT *
FROM employees e
WHERE NOT EXISTS
(
SELECT name
FROM eotm_dyn d
WHERE d.employeeID = e.id
)
哪个
SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL
更有效，一些分析？
SELECT *
FROM employees e
WHERE NOT EXISTS
(
SELECT name
FROM eotm_dyn d
WHERE d.employeeID = e.id
)
And
SELECT * FROM employees a LEFT JOIN eotm_dyn b on (a.joinfield=b.joinfield) WHERE b.name IS NULL
Which is more efficient,some analysis?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
假设涉及的列值不能为NULL -
MySQL：
LEFT JOIN/IS NULL比NOT EXISTS更高效 > - 阅读 本文了解详细信息。Oracle：
它们是等价的。
SQL Server：
NOT EXISTS比LEFT JOIN/IS NULL更高效 - 阅读本文了解详细信息。Postgres：
与 Oracle 一样，它们是等效的。
如果您对列值不能为空这一细节感到困难，请使用 LEFT JOIN / IS NULL - 记住 LEFT JOIN 的含义。 此链接可能会有所帮助。
Assuming the column values involved can not be NULL -
MySQL:
LEFT JOIN/IS NULLis more efficient thanNOT EXISTS- read this article for details.Oracle:
They are equivalent.
SQL Server:
NOT EXISTSis more efficient thanLEFT JOIN/IS NULL- read this article for details.Postgres:
Like Oracle, they are equivalent.
If you have trouble with the detail that the column values can not be null, yet use the LEFT JOIN / IS NULL - remember what a LEFT JOIN signifies. This link might help.
我真的认为你应该分析这样的问题。它不仅取决于确切的数据库产品，而且理论上它还可能取决于数据的偏差。
然而！默认情况下，我会说编写最清楚地表达您的意图的代码。您正在查找
employee记录，但没有匹配的eotm_dyn，因此IMO最清晰代码是WHERE NOT EXISTS。这可能并不重要，但我会使用SELECT 1（而不是SELECT name），因为name在“没有匹配的 eotm_dyn 逻辑一旦您有了表达您的意图和工作原理的代码，就可以考虑基于分析进行优化。
I really think you should profile for such a question. Not only does it depend on the exact database product, but in theory it could also depend on the skew of your data.
However! By default I would say write the code that most clearly expresses your intent. You are after
employeerecords without a matchingeotm_dyn, so IMO the clearest code isWHERE NOT EXISTS. It probably won't matter, but I would useSELECT 1(notSELECT name), since thenameis not important in the "without a matchingeotm_dynlogic.Once you have code that expresses what you intend and works, then look at optimising based on profiling.