JOIN vs. WHERE
-
I quite don't understand why noone is using the WHERE-Syntax for table joining ... I always see something like
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
JOIN Address F
ON O.Forwarder = C.IDFor me, the most irritating thig is the spreading of table names and aliases all over the Statement. I prefer
SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F
WHERE O.Customer = C.ID
AND O.Forwarder = F.ID- It's shorter - All table names and aliases are together Arguments, anyone ?
Klause, theoretically, the first approach is more optimized and can give better performance while working with large data sets. WHY? In first approach, the intermediate tables are expressed clearly and the steps to arrive the result is evident. eg: step1 -> Create Intermediate table I : O join C on ID step2 -> Result R : I join F on ID this is optimum route to reach the result. How about second approach? In a simple case, SQL optimizer might arrive at same steps by analyzing the tables and columns used in WHERE clause but not always. Especially when the WHERE clause is relatively complex.
-
Klause, theoretically, the first approach is more optimized and can give better performance while working with large data sets. WHY? In first approach, the intermediate tables are expressed clearly and the steps to arrive the result is evident. eg: step1 -> Create Intermediate table I : O join C on ID step2 -> Result R : I join F on ID this is optimum route to reach the result. How about second approach? In a simple case, SQL optimizer might arrive at same steps by analyzing the tables and columns used in WHERE clause but not always. Especially when the WHERE clause is relatively complex.
I was under the impression there was no difference by the time the optimizer had done it's job so I have always based my bigotry opinion on the readability.
Never underestimate the power of human stupidity RAH
-
Klaus-Werner Konrad wrote:
Arguments,
Nope, I call them Oracle joins (I loathe Oracle) because that is the way I was taught in the 90s then I moved to SQL SErver and was introduced to the JOIN and have never used it since. I will refactor any procedure I see using them and have a short, sharp discussion with the dev that uses it. Use join or quit.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Nope, I call them Oracle joins
Yeah - a really qualified Response ... I ask for arguments, an your resposne is
Mycroft Holmes wrote:
I will [...] have a short, sharp discussion with the dev that uses it
THIS is my Intention - to have a DISCUSSION about it ! But you have obvoiously no arguments, but prejudices against it - not the ideal base for discussions. What would be your arguments in a discussion with your developer - do you even have any arguments ? Note: I asked politely for arguments for - or against - my preferation, because (as every of us) I want to improve my abilities, and also to give some impressions to SQL newbies to decide what to do, and not to do. So ... HAVE VOU ARGUMENTS, or are you just felt to open your mouth ... ?
-
I was under the impression there was no difference by the time the optimizer had done it's job so I have always based my bigotry opinion on the readability.
Never underestimate the power of human stupidity RAH
Generally there isn't, but in specific cases there are.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
I quite don't understand why noone is using the WHERE-Syntax for table joining ... I always see something like
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
JOIN Address F
ON O.Forwarder = C.IDFor me, the most irritating thig is the spreading of table names and aliases all over the Statement. I prefer
SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F
WHERE O.Customer = C.ID
AND O.Forwarder = F.ID- It's shorter - All table names and aliases are together Arguments, anyone ?
The downvote you got is stupid because the question is valid. The problem is though that readability isn't the same as clarity of intention, and there is more to it than you see at the first glance. Firstly, when using an ANSI Join you specify which TABLES to join. When using an implicit join you specify which fields to join on. This is normally not making any difference, but it might in some specific cases. Consider this query:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F
WHERE O.Customer = C.ID
AND O.Forwarder = F.ID(+)
AND F.OtherID = 1Is it the same as:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
LEFT OUTER JOIN Address F
ON O.Forwarder = F.ID
AND F.OtherID = 1or:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
LEFT OUTER JOIN Address F
ON O.Forwarder = F.ID
WHERE F.OtherID = 1The difference is crucial as the results differ. Secondly, when using an implicit join there is no way to force the optimizer to do the joins in a certain order, which normally is a good thing. The optimizer is usually better than many people on that. But sometime you know better than the optimizer and can force the joining order as such:
SELECT O.Order_No, C.Address, F.Address
FROM (
Orders O JOIN Address C
ON O.Customer = C.ID
) JOIN Address F
ON O.Forwarder = C.IDMy personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says. :)
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Mycroft Holmes wrote:
Nope, I call them Oracle joins
Yeah - a really qualified Response ... I ask for arguments, an your resposne is
Mycroft Holmes wrote:
I will [...] have a short, sharp discussion with the dev that uses it
THIS is my Intention - to have a DISCUSSION about it ! But you have obvoiously no arguments, but prejudices against it - not the ideal base for discussions. What would be your arguments in a discussion with your developer - do you even have any arguments ? Note: I asked politely for arguments for - or against - my preferation, because (as every of us) I want to improve my abilities, and also to give some impressions to SQL newbies to decide what to do, and not to do. So ... HAVE VOU ARGUMENTS, or are you just felt to open your mouth ... ?
Klaus-Werner Konrad wrote:
THIS is my Intention - to have a DISCUSSION about it !
So we are supposed to be telepathic so we can identify your INTENTIONS. You stated that you prefer the JOIN format, then you asked for arguments to that position. Nowhere did you mention discussion. I stated that I had no argument and qualified why. In other words I was supporting your preference! I did not down vote your question or response (was not even aware you had been) as the question was valid and the response just means you are a bit pissy this morning.
Never underestimate the power of human stupidity RAH
-
The downvote you got is stupid because the question is valid. The problem is though that readability isn't the same as clarity of intention, and there is more to it than you see at the first glance. Firstly, when using an ANSI Join you specify which TABLES to join. When using an implicit join you specify which fields to join on. This is normally not making any difference, but it might in some specific cases. Consider this query:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F
WHERE O.Customer = C.ID
AND O.Forwarder = F.ID(+)
AND F.OtherID = 1Is it the same as:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
LEFT OUTER JOIN Address F
ON O.Forwarder = F.ID
AND F.OtherID = 1or:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
LEFT OUTER JOIN Address F
ON O.Forwarder = F.ID
WHERE F.OtherID = 1The difference is crucial as the results differ. Secondly, when using an implicit join there is no way to force the optimizer to do the joins in a certain order, which normally is a good thing. The optimizer is usually better than many people on that. But sometime you know better than the optimizer and can force the joining order as such:
SELECT O.Order_No, C.Address, F.Address
FROM (
Orders O JOIN Address C
ON O.Customer = C.ID
) JOIN Address F
ON O.Forwarder = C.IDMy personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says. :)
Wrong is evil and must be defeated. - Jeff Ello[^]
I'm curious, and lazy, which result does the first query match. I no longer remember the relevance of the (+), left outer join I believe whereas the intention may have been to filter the forwarders.
Never underestimate the power of human stupidity RAH
-
I'm curious, and lazy, which result does the first query match. I no longer remember the relevance of the (+), left outer join I believe whereas the intention may have been to filter the forwarders.
Never underestimate the power of human stupidity RAH
The second one, to match the first one the query would need to look like this:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F
WHERE O.Customer = C.ID
AND O.Forwarder = F.ID(+)
AND F.OtherID(+) = 1Wrong is evil and must be defeated. - Jeff Ello[^]
-
I'm curious, and lazy, which result does the first query match. I no longer remember the relevance of the (+), left outer join I believe whereas the intention may have been to filter the forwarders.
Never underestimate the power of human stupidity RAH
When it comes to the relevance of the (+), think of it as the side that allows nulls (+ nulls)
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Klaus-Werner Konrad wrote:
THIS is my Intention - to have a DISCUSSION about it !
So we are supposed to be telepathic so we can identify your INTENTIONS. You stated that you prefer the JOIN format, then you asked for arguments to that position. Nowhere did you mention discussion. I stated that I had no argument and qualified why. In other words I was supporting your preference! I did not down vote your question or response (was not even aware you had been) as the question was valid and the response just means you are a bit pissy this morning.
Never underestimate the power of human stupidity RAH
Well - the title should be clear enough, and I presented JOIN syntax and WHERE syntax, expressing my worry about noone seems using WHERE. THEN I asked for arguments - obviously (I thought so) WHY noone is using WHERE syntax, and NOT to support my preference. In other words: Are there any points (other than preference) to not using WHERE syntax ??
-
When it comes to the relevance of the (+), think of it as the side that allows nulls (+ nulls)
Wrong is evil and must be defeated. - Jeff Ello[^]
-
The downvote you got is stupid because the question is valid. The problem is though that readability isn't the same as clarity of intention, and there is more to it than you see at the first glance. Firstly, when using an ANSI Join you specify which TABLES to join. When using an implicit join you specify which fields to join on. This is normally not making any difference, but it might in some specific cases. Consider this query:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O, Address C, Address F
WHERE O.Customer = C.ID
AND O.Forwarder = F.ID(+)
AND F.OtherID = 1Is it the same as:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
LEFT OUTER JOIN Address F
ON O.Forwarder = F.ID
AND F.OtherID = 1or:
SELECT O.Order_No, C.Address, F.Address
FROM Orders O
JOIN Address C
ON O.Customer = C.ID
LEFT OUTER JOIN Address F
ON O.Forwarder = F.ID
WHERE F.OtherID = 1The difference is crucial as the results differ. Secondly, when using an implicit join there is no way to force the optimizer to do the joins in a certain order, which normally is a good thing. The optimizer is usually better than many people on that. But sometime you know better than the optimizer and can force the joining order as such:
SELECT O.Order_No, C.Address, F.Address
FROM (
Orders O JOIN Address C
ON O.Customer = C.ID
) JOIN Address F
ON O.Forwarder = C.IDMy personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says. :)
Wrong is evil and must be defeated. - Jeff Ello[^]
Thanks for your thoughts, Jörgen. Of course the results differ, and to achive the correct result, one have to write
AND NVL( F.OtherID, 1 ) = 1
Quote:
My personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says
Don't know where you read this, but it cannot be right ... Even in my original simple example you have two addresses in tho ORDERS table, so there is no chance in having 'the same field always the same name'. Maybe you meant that a field with the same MEANING should have always the same name, like CUSTOMER, FORWARDER etc. ?
-
Have a look here[^] Want to change your mind?.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Thanks for your thoughts, Jörgen. Of course the results differ, and to achive the correct result, one have to write
AND NVL( F.OtherID, 1 ) = 1
Quote:
My personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says
Don't know where you read this, but it cannot be right ... Even in my original simple example you have two addresses in tho ORDERS table, so there is no chance in having 'the same field always the same name'. Maybe you meant that a field with the same MEANING should have always the same name, like CUSTOMER, FORWARDER etc. ?
That would of course depend on which result is the correct one. I would also go very far to avoid a function in the where clause, for performance reasons. The ISO standard I'm referring to is ISO-11179[^]. Considering the amount of text, I can't be bothered to find the actual paragraph that I'm referring to. But the gist is that if you have a Customer Table the Surrogate Key should be named CustomerID in all tables using it no matter if it's the primary key or a foreign key. It shouldn't be CustomerID in one table, CID in another, customer in a third, or just ID in the Customer Table. Same name everywhere to avoid confusion. This also allows you to use the using construct[^] or even a Natural Join[^]. Personally I avoid both as I don't find that they add any clarity. There are as always exceptions. An obvious one is when you have a Person table where the person have a work address and a home address, both columns referring to the same address table, they can't both be AddressID.
Wrong is evil and must be defeated. - Jeff Ello[^]
-