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:
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question .
So I'm asking a new question.
I can write a query as:
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'
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.
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'
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 (egoid 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.
In the case of inner join both really are equivalent in its execution, even though there is a different semantics. Query optimizers will review and evaluate criteria in your WHERE clause and your FROM clause and consider all of these factors when building query plans in order to reach the most efficient execution plan. So you can go with whatever way you like to.
Also as you told it is worth notable that when inner join is replaced with left/ right joins equations change and you need the filters on 'ON' clause.
链接地址: http://www.djcxy.com/p/86262.html