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