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
  #31  
Old March 4th, 2009, 02:57 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Better "Join" vs "Where" clause?

Yes, MS SQL Server does it accordingly to the ANSI specs, while JET does
not. While MS SQL Server and Jet indeed differ on this statement, today
(that may change in another Office patch), with Jet, we must conclude that
this syntax has to be avoided... Although it is possible that someone cut
and paste the statement from MS SQL Server into Jet ... well, that someone
is better to be aware of the *possible* difference in result, even if no
'parsing' error occurs!


Vanderghast, Access MVP



"Bob Barrows" wrote in message
...
Michel Walsh wrote:
since City is a field from authors, not from books,

(select authorid from books where City = 'Iqaluit')


is not making sense. It would if the condition was implying the
unpreserved table, but it is not the case, here.


My mistake, I should have written

SELECT authors.*
FROM (select * from authors where City = 'Iqaluit') as authors
left JOIN
books ON authors.authorID = books.authorID

I was thinking your example was equivalent to the one shown in BOL (which
illustrated the differences in results when applying the filter to the
unpreserved table). I now see that it wasn't.

This is an interesting topic, albeit nothing to do with the reason I
stepped into this thread in the first place. it appears that Jet is
evaluating the statement like this:

SELECT authors.*
FROM (select * from authors where City = 'Iqaluit') as authors
left JOIN
books ON authors.authorID = books.authorID

while SQL Server is evaluating it like this:

SELECT authors.*
FROM authors Left Join
(select a.authorid from authors a inner join books b
on a.authorid = b.authorid where a.City='lqaluit') as q

I'm no expert on ANSI, so I'm not sure which is the behavior specified by
that standard. When you said "... MS SQL Server is right, since a left
join should preserve all records from the left table ... " did you mean
"correct" in terms of the ANSI specification?

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



  #32  
Old March 4th, 2009, 04:00 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Better "Join" vs "Where" clause?

David W. Fenton wrote:

Uh, you're quoting examples of T-SQL. You haven't shown that the
results returned are different from Jet than within SQL Server.


Quick recap:
We were discussing why it would be better to put JOIN criteria in the
JOIN clause rather than the WHERE clause. I had suggested that it would
be better to get in the habit of doing it in the JOIN clause so one
would be be better prepared to make the move to SQL Server. I supported
that contention by explaining that putting outer join criteria in the
WHERE clause in SQL Server (not possible in Jet) could produce different
results than putting the criteria in the JOIN clause. That's the only
point I was attempting to make.


--
HTH,
Bob Barrows


  #33  
Old March 5th, 2009, 12:59 AM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Better "Join" vs "Where" clause?

"Bob Barrows" wrote in
:

you have yet to provide a
single example of a join statement that works in Jet but not in
SQL Server. If you're talking about the different syntax for
update/delete statements rather than the join structures
themselves, then say so and I will drop out of the discussion.


I explained this many posts ago -- I encountered the problem more
than once (it was a couple years ago during a SQL Server upsizing
project, attempting to convert some queries to SQL views, and the
joins in the Jet SQL had to be rewritten to work on SQL Server), but
I can't recall the exact syntax that caused the problem.

It *did* happen and I do recall that it was a problem with the
parens.

But as I said several posts ago, I can't provide the specifics as
they are lost in the mists of time.

If you would like to believe that this never happened to me, then so
be it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #34  
Old March 5th, 2009, 01:00 AM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Better "Join" vs "Where" clause?

"Bob Barrows" wrote in
:

David W. Fenton wrote:

Uh, you're quoting examples of T-SQL. You haven't shown that the
results returned are different from Jet than within SQL Server.


Quick recap:
We were discussing why it would be better to put JOIN criteria in
the JOIN clause rather than the WHERE clause. I had suggested that
it would be better to get in the habit of doing it in the JOIN
clause so one would be be better prepared to make the move to SQL
Server. I supported that contention by explaining that putting
outer join criteria in the WHERE clause in SQL Server (not
possible in Jet) could produce different results than putting the
criteria in the JOIN clause. That's the only point I was
attempting to make.


And so far, you haven't done that, so far as I can see.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #35  
Old March 5th, 2009, 01:01 AM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Better "Join" vs "Where" clause?

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

While MS SQL Server and Jet indeed differ on this statement, today
(that may change in another Office patch), with Jet, we must
conclude that this syntax has to be avoided... Although it is
possible that someone cut and paste the statement from MS SQL
Server into Jet ... well, that someone is better to be aware of
the *possible* difference in result, even if no 'parsing' error
occurs!


I'm having some difficulty figuring out *why* someone would every
code the problematic syntax.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #36  
Old March 5th, 2009, 02:44 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Better "Join" vs "Where" clause?

A possible reason is that it is shorter than the other alternatives. Also,
in Jet, you may have problem with [] names to use the subquery alternative.
Compa

FROM a LEFT JOIN [b b] (ON a.ab=[b b].ab AND [b b].bb=cc)

it is shorter than

FROM a LEFT JOIN (SELECT * from [b b] WHERE bb=cc) AS x ON a.ab=x.ab

which is itself closer to the point, shorter and less prone to error than to
bring the condition to the where clause.

With the condition brought to the where clause, I doubt the optimizer will
use the same execution plan. Indeed, with all in the ON clause, including
the sub-query, you clearly make the join by 'touching only' records where [b
b].bb = cc; while with the where clause, your optimizer ***may*** miss that
short-cut, decide to "make" the join and then decide to make the elaborated
test on each row of the (then possibly huge) result of the join... much less
efficient. It is a possibility, not something occurring in each and every
cases.


With Jet, the first solution works, while I am not sure the second does, due
to the [ ]. Sure, sure, why someone would use ill form name in the first
place... It just happens some people do.



I still stand that the syntax SHOULD be avoided, in Jet, though, mainly if
the condition implying just one table is the preserved table of the outer
join.





Vanderghast, Access MVP



"David W. Fenton" wrote in message
36.88...
"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

While MS SQL Server and Jet indeed differ on this statement, today
(that may change in another Office patch), with Jet, we must
conclude that this syntax has to be avoided... Although it is
possible that someone cut and paste the statement from MS SQL
Server into Jet ... well, that someone is better to be aware of
the *possible* difference in result, even if no 'parsing' error
occurs!


I'm having some difficulty figuring out *why* someone would every
code the problematic syntax.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



  #37  
Old March 6th, 2009, 03:07 AM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Better "Join" vs "Where" clause?

"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

A possible reason is that it is shorter than the other
alternatives.


Er, what? Shorter only matters if you're hand-coding your SQL. The
query optimizer doesn't care if equivalent SQL statements are longer
or shorter.

Also,
in Jet, you may have problem with [] names to use the subquery
alternative.


Well, unless you use ANSI 92, in which case you can use () for your
derived tables, and not have the bracket problems.

Compa

FROM a LEFT JOIN [b b] (ON a.ab=[b b].ab AND [b b].bb=cc)

it is shorter than

FROM a LEFT JOIN (SELECT * from [b b] WHERE bb=cc) AS x ON
a.ab=x.ab

which is itself closer to the point, shorter and less prone to
error than to bring the condition to the where clause.


I don't even understand the first SQL statement, to be honest.

With the condition brought to the where clause, I doubt the
optimizer will use the same execution plan.


I think that when they JOIN and the WHERE clause actually are
equivalent, they will optimize the same, though that's going to be
highly dependent on the database engine. Certainly every test I've
ever done comparing equivalent implicit and explicit joins with Jet
has optimized identically (and a few years ago, I spent quite a bit
of time testing that, since I had feared that JOINS were better
optimized than the equivalent WHERE clauses -- I was wrong in Jet).

Indeed, with all in the ON clause, including
the sub-query, you clearly make the join by 'touching only'
records where [b b].bb = cc; while with the where clause, your
optimizer ***may*** miss that short-cut, decide to "make" the join
and then decide to make the elaborated test on each row of the
(then possibly huge) result of the join... much less efficient. It
is a possibility, not something occurring in each and every cases.


Well, again, it's going to be highly dependent on the database
engine involved, and the type of join you're trying to implement.
Left/Right joins are harder to code as WHERE clauses than they are
as joins, because it usually takes more than one WHERE criterion to
say the same thing as the JOIN statement.

With Jet, the first solution works, while I am not sure the second
does, due to the [ ]. Sure, sure, why someone would use ill form
name in the first place... It just happens some people do.

I still stand that the syntax SHOULD be avoided, in Jet, though,
mainly if the condition implying just one table is the preserved
table of the outer join.


I never even knew that you could join on a constant. I still can't
wrap my head around the very concept, to be honest.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #38  
Old March 6th, 2009, 02:40 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Better "Join" vs "Where" clause?

There is an extreme case, famous, in MS SQL Server only though, and the
example is in BOL I think, where it uses a full outer join to make what is
called a 'UNION JOIN'.

A UNION JOIN, between tables A and B, is, pictorially, the result like:

|
A | nulls
|
--------+-------------------
|
nulls | B
|



MS SQL Server has no explicit syntax for it, but can use:


FROM a FULL OUTER JOIN b ON 1 = 2



to simulate it. So, you can even have a ON clause on a pure constant.


Now, I agree, this is in a large part 'just for the show', but sometimes, it
just relaxing to see such things.



Vanderghast, Access MVP



"David W. Fenton" wrote in message
36.94...
"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

A possible reason is that it is shorter than the other
alternatives.


Er, what? Shorter only matters if you're hand-coding your SQL. The
query optimizer doesn't care if equivalent SQL statements are longer
or shorter.

Also,
in Jet, you may have problem with [] names to use the subquery
alternative.


Well, unless you use ANSI 92, in which case you can use () for your
derived tables, and not have the bracket problems.

Compa

FROM a LEFT JOIN [b b] (ON a.ab=[b b].ab AND [b b].bb=cc)

it is shorter than

FROM a LEFT JOIN (SELECT * from [b b] WHERE bb=cc) AS x ON
a.ab=x.ab

which is itself closer to the point, shorter and less prone to
error than to bring the condition to the where clause.


I don't even understand the first SQL statement, to be honest.

With the condition brought to the where clause, I doubt the
optimizer will use the same execution plan.


I think that when they JOIN and the WHERE clause actually are
equivalent, they will optimize the same, though that's going to be
highly dependent on the database engine. Certainly every test I've
ever done comparing equivalent implicit and explicit joins with Jet
has optimized identically (and a few years ago, I spent quite a bit
of time testing that, since I had feared that JOINS were better
optimized than the equivalent WHERE clauses -- I was wrong in Jet).

Indeed, with all in the ON clause, including
the sub-query, you clearly make the join by 'touching only'
records where [b b].bb = cc; while with the where clause, your
optimizer ***may*** miss that short-cut, decide to "make" the join
and then decide to make the elaborated test on each row of the
(then possibly huge) result of the join... much less efficient. It
is a possibility, not something occurring in each and every cases.


Well, again, it's going to be highly dependent on the database
engine involved, and the type of join you're trying to implement.
Left/Right joins are harder to code as WHERE clauses than they are
as joins, because it usually takes more than one WHERE criterion to
say the same thing as the JOIN statement.

With Jet, the first solution works, while I am not sure the second
does, due to the [ ]. Sure, sure, why someone would use ill form
name in the first place... It just happens some people do.

I still stand that the syntax SHOULD be avoided, in Jet, though,
mainly if the condition implying just one table is the preserved
table of the outer join.


I never even knew that you could join on a constant. I still can't
wrap my head around the very concept, to be honest.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



 




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 06:13 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.