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 |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|