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
  #21  
Old March 3rd, 2009, 12:58 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Better "Join" vs "Where" clause?

Michel Walsh wrote:
Outer join with a condition in the on clause implying only one table.
The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID AND
authors.City = 'Iqaluit'



as example. With Jet 3.5, you got no record, but with MS SQL Server,
you get all records from authors.


Interesting. All of my attempts to use that syntax in Jet had failed
(syntax error), forcing me to resort to using a subquery to get that effect:

.... LEFT JOIN (select authorid from books where City = 'Iqaluit') as q
ON authors.authorID = q.authorID

Are you sure your example statement doesn't raise an error in Jet?



There is also the case of update through joins, which is immediately
available with JET, but with proprietary syntax for MS SQL Server (or
explicitly over a view).


I know about the different update/delete syntax - I thought we were
discussing the join syntax itself ... in select statements. David? is this
what you were referring to? If so, I withdraw my objection to your statement
except to say that it isn't the join syntax per se that is causing the
problems in this case: it's the different syntax required for update/delete
statements in T-SQL.



Not linked to join, but you cannot use ALIAS over alias in MS SQL
Server, while you can in Jet:

SELECT price * 1.06 AS TPS, (price + TPS) * 1.045 AS PST, price
+TPS + PST AS totalPrice FROM somewhere



I always thought that worked in Access because Access rewrote the query
before passing it to Jet. I'll have to try executing a similar sql statement
via ADO to see if that's the case.

--
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"


  #22  
Old March 3rd, 2009, 08:14 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Better "Join" vs "Where" clause?

Try, in Northwind:

------------------------------------
SELECT Orders.ShipCity
FROM Orders LEFT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND (((Orders.ShipCity)="Iqaluit")));
-----------------------------------

KEEP the parenthesis !


Jet returns no record (as per today state of the art of Jet 4.0 patches,
using Access 2003)

While MS SQL Server returns all the records from orders, ... even those
where shipCity is not equal to Iqaluit, that is.

MS SQL Server is right, since a left join should preserve all records from
the left table, but that is very COUNTER INTUITIVE.

Jet is more intuitive, wrong (by the SQL standard), but acceptable ... by
many (kind of)


Vanderghast, Access MVP




  #23  
Old March 3rd, 2009, 08:37 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Better "Join" vs "Where" clause?

Ah, the parentheses are the key!

Michel Walsh wrote:
Try, in Northwind:

------------------------------------
SELECT Orders.ShipCity
FROM Orders LEFT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND (((Orders.ShipCity)="Iqaluit")));
-----------------------------------

KEEP the parenthesis !


Jet returns no record (as per today state of the art of Jet 4.0
patches, using Access 2003)

While MS SQL Server returns all the records from orders, ... even
those where shipCity is not equal to Iqaluit, that is.

MS SQL Server is right, since a left join should preserve all records
from the left table, but that is very COUNTER INTUITIVE.

Jet is more intuitive, wrong (by the SQL standard), but acceptable
... by many (kind of)


Vanderghast, Access MVP


--
HTH,
Bob Barrows


  #24  
Old March 4th, 2009, 05:21 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:


[]

And so far as I know, SQL Server also optimizes implicit joins
identically to explicit ones. In fact, it seems to me that
because of Jet's oddball join syntax, it might be more portable
to use implicit joins, since there's a lot more in common in
terms of WHERE clauses than JOINs.

You may have a point with inner joins, but with outer joins,
using the JOIN syntax vs the WHERE syntax can cause different
results to be returned.


Examples, please.


Sure, they're easy enough to find in BOL ... let's see ... ah,
here we go - it's long so I'm putting it at the bottom


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

Just because different databases optimize a SQL statement
differently does not mean they return different results.

In
fact, the *= syntax for performing outer joins implicitly in the
WHERE clause has been deprecated, mainly for this reason.


Sorry, but I don't know what you're referring to with "*=". I
don't recognize that as valid Jet SQL.


It isn't - I should have said "... the *= syntax for performing
outer joins implicitly in the
WHERE clause in SQL Server ..."


So, er, um, what is your point here?

I'm lost as you seem to be talking about SQL Server alone, rather
than some difference in the result sets between the equivalent SQL
statements using implicit vs. explicit joins in Jet.

AFAIK one cannot perform an outer join in Access without using the
explicit JOIN syntax.


And....????

Frankly, I didn't read the long quotation in any detail. What I did
read didn't seem relevant to the discussion. Perhaps you could point
out an actual example of Jet SQL with equivalent implicit vs.
explicit joins that returns a different result.

That *was* what we were talking about, right?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #25  
Old March 4th, 2009, 05:27 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
:

Outer join with a condition in the on clause implying only one
table. The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
AND authors.City = 'Iqaluit'


I wouldn't call this a JOIN. It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
WHERE authors.City = 'Iqaluit'

The implicit join version:

SELECT authors.*
WHERE (authors.authorID = books.authorID OR books.authorID Is Null)
AND authors.City = 'Iqaluit'

(I *think* that's correct)

If the result sets returned by the implicit join and explicit join
versions are different, then the implicit join has not been properly
written, seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #26  
Old March 4th, 2009, 11:18 AM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Better "Join" vs "Where" clause?

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

Outer join with a condition in the on clause implying only one
table. The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
AND authors.City = 'Iqaluit'


I wouldn't call this a JOIN.


Why not? it's equivalent to this, which is certailnly a join:

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




It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):


See Michel's post. It doesn't.

--
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"


  #27  
Old March 4th, 2009, 11:44 AM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Better "Join" vs "Where" clause?

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.


Vanderghast, Access MVP


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

Outer join with a condition in the on clause implying only one
table. The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
AND authors.City = 'Iqaluit'


I wouldn't call this a JOIN.


Why not? it's equivalent to this, which is certailnly a join:

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




It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):


See Michel's post. It doesn't.

--
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"



  #28  
Old March 4th, 2009, 11:52 AM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Better "Join" vs "Where" clause?

David W. Fenton wrote:
"Bob Barrows" wrote in
:

David W. Fenton wrote:


[]

And so far as I know, SQL Server also optimizes implicit joins
identically to explicit ones. In fact, it seems to me that
because of Jet's oddball join syntax, it might be more portable
to use implicit joins, since there's a lot more in common in
terms of WHERE clauses than JOINs.

You may have a point with inner joins, but with outer joins,
using the JOIN syntax vs the WHERE syntax can cause different
results to be returned.

Examples, please.


Sure, they're easy enough to find in BOL ... let's see ... ah,
here we go - it's long so I'm putting it at the bottom


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


I didn't intend to say it was: at the time I made the above statement, I was
under the mistaken impression that Jet did not allow the syntax the BOL
article discussed, but Michel has definitely shown that the syntax is
allowed and the results are different.


Just because different databases optimize a SQL statement
differently does not mean they return different results.


Nothing to do with optimization: order of evaluation is the issue that
causes different results to be returned.


Frankly, I didn't read the long quotation in any detail. What I did
read didn't seem relevant to the discussion. Perhaps you could point
out an actual example of Jet SQL with equivalent implicit vs.
explicit joins that returns a different result.

See Michel's post - I won't repeat it.

But my introducing this subject has caused us to veer away from the reason I
stepped into this thread in the first place: 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.

--
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"


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

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"


  #30  
Old March 4th, 2009, 01:26 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default Better "Join" vs "Where" clause?

A Bob hints it, there are alternative syntax, sure, but some are simply
shorter.

Compare

SELECT a.*, b.*
FROM a LEFT JOIN b ON (a.ab=b.ab AND b.bb='bb')


and


SELECT a.*, c.*
FROM a LEFT JOIN (SELECT * FROM b WHERE b.bb='bb') AS x
ON a.ab=x.ab




As per today, both returns the same result, in both Jet and MS SLQ Server.



NOTE that you have to be very careful about the details:


SELECT Orders.ShipCity
FROM Orders LEFT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND [Order Details].Quantity = -1);


return the same (similar) result, with both, Jet and MS SQL Server, but



SELECT Orders.ShipCity
FROM Orders RIGHT JOIN [Order Details]
ON (Orders.OrderID = [Order Details].OrderID
AND [Order Details].Quantity = -1);




does not ( at least, today ). Jet returns no record, while MS SQL Server
returns as many records as there is in Orders.



Vanderghast, Access MVP



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

Outer join with a condition in the on clause implying only one
table. The result differs even among Jet versions (and patches):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
AND authors.City = 'Iqaluit'


I wouldn't call this a JOIN. It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
WHERE authors.City = 'Iqaluit'

The implicit join version:

SELECT authors.*
WHERE (authors.authorID = books.authorID OR books.authorID Is Null)
AND authors.City = 'Iqaluit'

(I *think* that's correct)

If the result sets returned by the implicit join and explicit join
versions are different, then the implicit join has not been properly
written, seems to me.

--
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:01 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.