If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Better "Join" vs "Where" clause?
2003, 2007
At the EOD, it appears that a WHERE clause accomplishes the same data set as an [INNER] Join. What are the benefits of one over the other? Also, it does not appear that the use of [ or ] preceding / following Table names and/or Field names changes data set results. Is the use of [ or ] a convention for reviewing code or are there substantive issues in some end-use cases? TIA EagleOne |
#2
|
|||
|
|||
Better "Join" vs "Where" clause?
Well, if you don't display any field from the second table when you are
joining two tables then yes, an INNER JOIN might give you the apparence that it is doing the same job as a WHERE clause. Start displaying some fields from the second table and you'll start to feel the difference between a JOIN and a WHERE clauses. For [], it's better not to use them because your code will be more readable; however, if you have some special characters such as blank spaces, dot or $ in the name of your tables or fields then you must use them: Select [My little table].[My first field] from [My little table] ... Not sure but I think that you must also use them if the name of a table or of a field is the same as of a reserved word; for example if you have a field called Date instead of something less confusing like Date1, EndDate, etc. When you'll have many tables and complexe relationships, using aliases for your tables will also be a very good idea: Select LT.[My first field] from [My little table] as LT .... -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Email: sylvain aei ca (fill the blanks, no spam please) Independent consultant and remote programming for Access and SQL-Server (French) wrote in message ... 2003, 2007 At the EOD, it appears that a WHERE clause accomplishes the same data set as an [INNER] Join. What are the benefits of one over the other? Also, it does not appear that the use of [ or ] preceding / following Table names and/or Field names changes data set results. Is the use of [ or ] a convention for reviewing code or are there substantive issues in some end-use cases? TIA EagleOne |
#3
|
|||
|
|||
Better "Join" vs "Where" clause?
|
#4
|
|||
|
|||
Better "Join" vs "Where" clause?
|
#5
|
|||
|
|||
Better "Join" vs "Where" clause?
Thanks Sylvain
"Sylvain Lafontaine" wrote: Well, if you don't display any field from the second table when you are joining two tables then yes, an INNER JOIN might give you the apparence that it is doing the same job as a WHERE clause. Start displaying some fields from the second table and you'll start to feel the difference between a JOIN and a WHERE clauses. For [], it's better not to use them because your code will be more readable; however, if you have some special characters such as blank spaces, dot or $ in the name of your tables or fields then you must use them: Select [My little table].[My first field] from [My little table] ... Not sure but I think that you must also use them if the name of a table or of a field is the same as of a reserved word; for example if you have a field called Date instead of something less confusing like Date1, EndDate, etc. When you'll have many tables and complexe relationships, using aliases for your tables will also be a very good idea: Select LT.[My first field] from [My little table] as LT .... |
#6
|
|||
|
|||
Better "Join" vs "Where" clause?
John W. Vinson wrote in
: A Query using a JOIN is much more likely to be updateable than one using a WHERE, and I've heard that it can also be faster (since the query optimizer makes better use of indexes). I don't know about updateability, but your statement about the query optimizer is false. An explicit join and an implicit join (i.e., using the equivalent WHERE clause) are optimized by Jet EXACTLY THE SAME, so there is going to be no difference in performance at all. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
Better "Join" vs "Where" clause?
In my current situation, I do not need update-ability. But I have learned quite a bit from the
comments to my question. Thanks! "David W. Fenton" wrote: John W. Vinson wrote in : A Query using a JOIN is much more likely to be updateable than one using a WHERE, and I've heard that it can also be faster (since the query optimizer makes better use of indexes). I don't know about updateability, but your statement about the query optimizer is false. An explicit join and an implicit join (i.e., using the equivalent WHERE clause) are optimized by Jet EXACTLY THE SAME, so there is going to be no difference in performance at all. |
#8
|
|||
|
|||
Better "Join" vs "Where" clause?
On Sun, 01 Mar 2009 10:34:27 -0500, EagleOne wrote:
In my current situation, I do not need update-ability. But I have learned quite a bit from the comments to my question. Thanks! For many queries it comes down only to preference. I prefer join syntax because that is more self-documenting about how the input sources "relate" to each other leaving the WHERE clause to document selection criteria. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#9
|
|||
|
|||
Better "Join" vs "Where" clause?
|
#10
|
|||
|
|||
Better "Join" vs "Where" clause?
Documentation is important. I'll add that thought as I try to improve.
Thanks EagleOne Rick Brandt wrote: On Sun, 01 Mar 2009 10:34:27 -0500, EagleOne wrote: In my current situation, I do not need update-ability. But I have learned quite a bit from the comments to my question. Thanks! For many queries it comes down only to preference. I prefer join syntax because that is more self-documenting about how the input sources "relate" to each other leaving the WHERE clause to document selection criteria. |
Thread Tools | |
Display Modes | |
|
|