"INNER JOIN"过滤条件在查询中的位置； ON或WHERE子句 [英] Position of `INNER JOIN` filtering conditions in a query; `ON` or `WHERE` clause

问题描述

此问题有一个答案.但我觉得这应该是一个问题.

There is one answer on this question that touches on this.. but I feel it deserves a question of it's own.

This question, which is marked as a duplicate to the first but isn't really, is what I want to ask.. and as it says in the bit:

此问题已被问过，并且已经有答案.如果这些答案不能完全解决您的问题，请提出一个新问题.

所以我要问一个新问题.

So I'm asking a new question.

我可以将查询写为:

SELECT *
  FROM customer_order co
  JOIN customer c 
    ON c.id = co.customer_id
   AND c.type = 'special'
  JOIN order o
    ON o.id = co.order_id
   AND o.status = 'dispatched'

OR:

SELECT *
  FROM customer_order co
  JOIN customer c 
    ON c.id = co.customer_id
  JOIN order o
    ON o.id = co.order_id
 WHERE c.type = 'special'
   AND o.status = 'dispatched'

我绝对更喜欢第一种方法，尤其是在更复杂的查询中，因为它会将条件与操作所在的表进行分组，这使我更易于阅读和识别适当的组合索引.这也意味着，如果我想更改为LEFT JOIN(或者可能是RIGHT JOIN，我实际上并没有使用RIGHT JOIN)，则所有条件都在正确的位置.

I absolutely prefer the first way, especially in more complex queries as it groups the conditions with the tables on which they operate, which makes it easier for me to read and to identify appropriate composite indexes. It also means that if I want to change to a LEFT JOIN (or maybe RIGHT JOIN, I don't really use RIGHT JOIN), all the conditions are in the right place.

但是，社区似乎倾向于第二种方式.

There seems to be some preference, however, in the community towards the second way.

有人知道这种偏爱是否基于，也许是在我尚未偶然发现的某些性能问题或某些可读性问题上?还是我可以继续快乐地成为反叛者?

Does anybody know if this preference is grounded, perhaps in some performance issue or in some readability issue that I have yet to stumble across? Or can I continue to be a rebel happily?

推荐答案

它们完全相同.唯一的决定因素是您在项目中使用什么标准.您需要确定对您而言更具可读性的内容，然后再去做.例如，格式化查询的方式不是我要做的.

They are both exactly the same. The only deciding factor is what standards you use in your project. You need to decide what is more readable for you and go with that. For example the way you format your queries is not what I would do.

我会

SELECT 
  *
FROM 
  customer_order co

  INNER JOIN customer c ON 
    c.id = co.customer_id AND 
    c.type = 'special'

  INNER JOIN order o ON 
    o.id = co.order_id AND 
    o.status = 'dispatched'

除了我觉得我的可读性更好之外，我和你的没有什么区别.根据经验，我通常为与基表相关的语句保留where子句.内部联接中的第一列也将与要联接的表相关(例如o.id或c.id).这些都是我用来保持一致性的所有内容.另一个开发人员可能更喜欢在where子句中包含所有条件.只是偏爱

There is no difference between mine and yours except that I feel mine is more readable. As a rule of thumb I generally reserve the where clause for statements that relate to the base table. Also the first column in the inner join would be related to the table being joined (e.g. o.id or c.id). These are all things I use to just keep consistency. Another developer might prefer to have all conditionals in the where clause. It's simply preference

关于您对社区的看法，我认为大多数人都同意一致性是关键.确保为其他开发人员记录您的方法论并加以遵循.如果性能受到影响，这将是另一种讨论，但事实并非如此.

Regarding your thoughts on the community, I think most people would agree that consistency is key. Make sure you document your methodology for other developers and go with that. If performance was being affected this would be a different discussion, but it's not.

继续进行您的操作，但请确保它是一致的！

Carry on what you are doing, but make sure it's consistent!

此外，对于类似这样的问题，我认为代码复审论坛是一个更好的选择，而且人们不太可能将您的问题否决.

Also, for questions like this I think the code review forum is a better place and guys will be less likely to vote your question down.

这篇关于"INNER JOIN"过滤条件在查询中的位置； ON或WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！