Click here to Skip to main content
15,868,164 members
Home / Discussions / Database
   

Database

 
AnswerRe: JOIN vs. WHERE Pin
Mycroft Holmes11-Jul-14 14:24
professionalMycroft Holmes11-Jul-14 14:24 
GeneralRe: JOIN vs. WHERE Pin
Klaus-Werner Konrad12-Jul-14 9:08
Klaus-Werner Konrad12-Jul-14 9:08 
GeneralRe: JOIN vs. WHERE Pin
Mycroft Holmes12-Jul-14 14:13
professionalMycroft Holmes12-Jul-14 14:13 
GeneralRe: JOIN vs. WHERE Pin
Klaus-Werner Konrad13-Jul-14 0:08
Klaus-Werner Konrad13-Jul-14 0:08 
AnswerRe: JOIN vs. WHERE Pin
data modeling guy11-Jul-14 19:53
data modeling guy11-Jul-14 19:53 
GeneralRe: JOIN vs. WHERE Pin
Mycroft Holmes11-Jul-14 23:29
professionalMycroft Holmes11-Jul-14 23:29 
GeneralRe: JOIN vs. WHERE Pin
Jörgen Andersson12-Jul-14 9:42
professionalJörgen Andersson12-Jul-14 9:42 
AnswerRe: JOIN vs. WHERE Pin
Jörgen Andersson12-Jul-14 10:25
professionalJörgen Andersson12-Jul-14 10:25 
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:
SQL
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 = 1
Is it the same as:
SQL
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 = 1
or:
SQL
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 = 1

The 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:
SQL
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.ID



My personal pet peeve is that a field should always have the same name everywhere in a database just like the ISO standard says. Smile | :)
Wrong is evil and must be defeated. - Jeff Ello[^]

GeneralRe: JOIN vs. WHERE Pin
Mycroft Holmes12-Jul-14 14:22
professionalMycroft Holmes12-Jul-14 14:22 
GeneralRe: JOIN vs. WHERE Pin
Jörgen Andersson12-Jul-14 20:44
professionalJörgen Andersson12-Jul-14 20:44 
GeneralRe: JOIN vs. WHERE Pin
Jörgen Andersson12-Jul-14 20:55
professionalJörgen Andersson12-Jul-14 20:55 
GeneralRe: JOIN vs. WHERE Pin
Klaus-Werner Konrad13-Jul-14 0:35
Klaus-Werner Konrad13-Jul-14 0:35 
GeneralRe: JOIN vs. WHERE Pin
Jörgen Andersson13-Jul-14 1:09
professionalJörgen Andersson13-Jul-14 1:09 
GeneralRe: JOIN vs. WHERE Pin
data modeling guy13-Jul-14 5:57
data modeling guy13-Jul-14 5:57 
GeneralRe: JOIN vs. WHERE Pin
Klaus-Werner Konrad13-Jul-14 1:08
Klaus-Werner Konrad13-Jul-14 1:08 
GeneralRe: JOIN vs. WHERE Pin
Jörgen Andersson13-Jul-14 1:46
professionalJörgen Andersson13-Jul-14 1:46 
QuestionHow to create an audit table? Pin
Bastien Vandamme10-Jul-14 0:30
Bastien Vandamme10-Jul-14 0:30 
AnswerRe: How to create an audit table? Pin
Eddy Vluggen10-Jul-14 8:14
professionalEddy Vluggen10-Jul-14 8:14 
AnswerRe: How to create an audit table? Pin
Mycroft Holmes10-Jul-14 14:15
professionalMycroft Holmes10-Jul-14 14:15 
GeneralRe: How to create an audit table? Pin
GuyThiebaut11-Jul-14 2:09
professionalGuyThiebaut11-Jul-14 2:09 
GeneralRe: How to create an audit table? Pin
Eddy Vluggen11-Jul-14 7:28
professionalEddy Vluggen11-Jul-14 7:28 
GeneralRe: How to create an audit table? Pin
jschell11-Jul-14 9:07
jschell11-Jul-14 9:07 
GeneralRe: How to create an audit table? Pin
Eddy Vluggen11-Jul-14 9:44
professionalEddy Vluggen11-Jul-14 9:44 
GeneralRe: How to create an audit table? Pin
Mycroft Holmes11-Jul-14 14:12
professionalMycroft Holmes11-Jul-14 14:12 
GeneralRe: How to create an audit table? Pin
jschell12-Jul-14 10:33
jschell12-Jul-14 10:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.