A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Better "Join" vs "Where" clause?



 
 
Thread Tools Display Modes
  #1  
Old February 28th, 2009, 07:00 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 308
Default 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  
Old February 28th, 2009, 07:22 PM posted to microsoft.public.access.queries
Sylvain Lafontaine[_2_]
external usenet poster
 
Posts: 247
Default 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



  #5  
Old February 28th, 2009, 09:31 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 308
Default 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  
Old February 28th, 2009, 10:43 PM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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  
Old March 1st, 2009, 04:34 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 308
Default 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  
Old March 1st, 2009, 05:08 PM posted to microsoft.public.access.queries
Rick Brandt
external usenet poster
 
Posts: 4,354
Default 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
  #10  
Old March 1st, 2009, 07:21 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 308
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.