将严格的业务逻辑谓词添加到 LEFT JOIN 条件
这是理论/最佳实践征求意见。
我已经习惯于将 WHERE 子句和 JOIN 条件视为“托管”任何业务逻辑的好地方，这将使我的查询尽可能精确。
但我注意到添加不相关的业务逻辑作为 JOIN 条件可能违反“最佳实践”。例如：
SELECT a.Id, b.Id
FROM foo AS a
LEFT OUTER JOIN bar AS b ON a.Id = b.Id
AND GETDATE() >= "18/5/2011"
这个例子有点天真：在现实生活中，调用者确实需要附加条件，并且没有它实际上会产生不真实的结果（调用者必须在代码中进行过滤）。
请注意，对于 OUTER 连接，当逻辑要求时，不能将条件放在 WHERE 子句中。
This is theoretical/best practice request for opinions.
I have grown accustomed to viewing the WHERE clause and the JOIN conditions as a good place to "host" any business logic that will make my query as precise as possible.
But it was brought to my attention that adding unrelated business logic as a JOIN condition might be against "best practices". For example:
SELECT a.Id, b.Id
FROM foo AS a
LEFT OUTER JOIN bar AS b ON a.Id = b.Id
AND GETDATE() >= "18/5/2011"
The example is a little naive: in real life the additional condition is really required by the caller and not having it would actually produce untrue result (that the caller will have to filter in the code).
Note that with OUTER joins and when logic dictates it, placing the condition in the WHERE clause is not an option.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
出于这个原因，我一直习惯于将尽可能多的条件放入
JOIN语句中，并且在许多地方（包括 Joe Celko 的著作）中读到，这是最佳实践所以。JOIN条件将会更快，因为它们排除了将行完全添加到组合表中的情况，其中在根据联接条件组合表后使用WHERE子句- 以及可能更大的数据集。在许多情况下，我确信优化器会智能地处理这个问题，并在可能的情况下应用 WHERE 条件，但这只是猜测，并且最多是特定于引擎的。I have always made it a practice to put as many conditionals into the
JOINstatements as possible for that reason, and have read in a number of places, including Joe Celko's writings, that it's a best practice to do so.JOINconditions are going to be faster as they exclude rows from being added to the combined table altogether, where aWHEREclause is used after the tables are combined based on the join conditions - and on, potentially, a much larger data set. In many cases, I'm sure the optimizer handles this intelligently and applies WHERE conditions where possible, but that's speculation, and would be engine specific at best.