2

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?

Community
  • 1
  • 1
Arth
  • 12,789
  • 5
  • 37
  • 69
  • I am not sure about why the community seems to prefer one way or the other but it is also completely correct to do `SELECT * FROM customer_order co, customer c, order o WHERE c.type = 'special' AND o.status = 'dispatched' AND c.id = co.customer_id AND o.id = co.order_id` – Spaceman Spiff Aug 12 '14 at 19:42
  • 2
    Thanks Ian, yep more ways! That method is pretty much covered in [this question](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause), however. – Arth Aug 12 '14 at 19:46
  • 1
    Nice link, I actually wasn't sure about that. It looks like both the links in your question are the same though. I am very interested in the answer to this question too. – Spaceman Spiff Aug 12 '14 at 19:51
  • 2
    @IanSellar Ah, have corrected the second link! Looks like we've had a mystery downvoter, I am rather critical of unexplained criticism.. I'd love a decent answer to this. – Arth Aug 12 '14 at 20:00
  • 1
    What about filling your sample table with a lot of data and have a look at the execution plan of both queries. I concede that I'm a conservative guy and use the join condition for the condition that joins both tables and the where clause to restrict the result. I see that as grouping of conditions by function. I've got no use for implicit joins because they're very error prone and don't really mix with outer joins. – VMai Aug 12 '14 at 20:49
  • 2
    Closed as primarily opinion-based? The OP specifically asked: "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?" In other words, NOT opinion based, but asking for a reason to use one or the other. "No difference, just opinion" MAY be a valid answer, but that's not a reason to close. – Menachem Aug 26 '14 at 20:03
  • @Menachem: Agreed. Although, now that someone has answered "No difference, just opinion," the close vote is probably reasonable to avoid further churn. – Josiah Yoder Jul 14 '17 at 15:30

2 Answers2

4

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 (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.

Ryan-Neal Mes
  • 6,003
  • 7
  • 52
  • 77
2

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.

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70