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 |
#11
|
|||
|
|||
Better "Join" vs "Where" clause?
Rick Brandt wrote in
: 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. I only use implicit joins where it's absolutely necessary. One such situation is if you need to join on a GUID field. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#12
|
|||
|
|||
Better "Join" vs "Where" clause?
"Bob Barrows" wrote in
: If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#13
|
|||
|
|||
Better "Join" vs "Where" clause?
On Sun, 01 Mar 2009 20:44:08 +0000, David W. Fenton wrote:
"Bob Barrows" wrote in : If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. What is different about it other than a very liberal use of parenthesis? -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#14
|
|||
|
|||
Better "Join" vs "Where" clause?
David W. Fenton wrote:
"Bob Barrows" wrote in : If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. Sorry, but this just isn't true. SQL Server handles Jet's plethora of parentheses quite handily, thank you. It's the other way around: joins that work nicely in T-SQL fail miserably in Jet due to its insistence on using parentheses to force the FROM clause to consist of a single table expression to table expression join. 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. In fact, the *= syntax for performing outer joins implicitly in the WHERE clause has been deprecated, mainly for this reason. -- 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" |
#15
|
|||
|
|||
Better "Join" vs "Where" clause?
I prefer join over where for better updatability, and also because Jet does
not allow (the deprecated) outer join syntax in the where clause. As mentioned, use [ ] around ill formed name, or when you build SQL string on the fly, where the user supplies the name: str = "... FROM [" & suppliedTableName & "] ... " (Assuming such practice is acceptable in the first place). Vanderghast, Access MVP 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 |
#16
|
|||
|
|||
Better "Join" vs "Where" clause?
Rick Brandt wrote in
: On Sun, 01 Mar 2009 20:44:08 +0000, David W. Fenton wrote: "Bob Barrows" wrote in : If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. What is different about it other than a very liberal use of parenthesis? I have never bothered to figure it out, but the fact is that you can't cut and paste every Jet join into SQL server and expect it to work unchanged. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#17
|
|||
|
|||
Better "Join" vs "Where" clause?
"Bob Barrows" wrote in
: David W. Fenton wrote: "Bob Barrows" wrote in : If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. Sorry, but this just isn't true. SQL Server handles Jet's plethora of parentheses quite handily, thank you. Sorry, Bob, but it's simply not true that every valid Jet SQL join will work unchanged in SQL Server (well, unless you're using ANSI 92 SQL mode in Access, which most people are not). I've encountered plenty of Jet joins that didn't work in SQL Server. Of course, maybe we're talking past each other -- I'm talking about running the SQL directly, in the SQL Server tools, rather than running it in Access via ODBC (which takes care of any incompatibilities). It's the other way around: joins that work nicely in T-SQL fail miserably in Jet due to its insistence on using parentheses to force the FROM clause to consist of a single table expression to table expression join. Sure, that's true, but not relevant to the question of explicit joins being more compatible than implicit joins. If you want your SQL to be upsizable to SQL Server with the least trouble, the best thing to do is not to muck around with implicit vs. explicit joins, but to switch Access to use ANSI 92 SQL. 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. 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#18
|
|||
|
|||
Better "Join" vs "Where" clause?
David W. Fenton wrote:
Rick Brandt wrote in : On Sun, 01 Mar 2009 20:44:08 +0000, David W. Fenton wrote: "Bob Barrows" wrote in : If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. What is different about it other than a very liberal use of parenthesis? I have never bothered to figure it out, but the fact is that you can't cut and paste every Jet join into SQL server and expect it to work unchanged. That's interesting. I've never run into anything like that. I just tried a couple different examples which worked fine in my SQL2000 server. Do you have an example of one that would fail? -- 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" |
#19
|
|||
|
|||
Better "Join" vs "Where" clause?
David W. Fenton wrote:
Of course, maybe we're talking past each other -- I'm talking about running the SQL directly, in the SQL Server tools, rather than running it in Access via ODBC (which takes care of any incompatibilities). No, I'm talking about importing the Access tables into SQL Server, and pasting the SQL that Access generated into SQL Query Analyzer. If you want your SQL to be upsizable to SQL Server with the least trouble, the best thing to do is not to muck around with implicit vs. explicit joins, but to switch Access to use ANSI 92 SQL. I've never touched that option 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 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 ..." AFAIK one cannot perform an outer join in Access without using the explicit JOIN syntax. ****paste from SQL BOL*********************************************** The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result. Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release. For example, these queries both specify a left outer join to SELECT 23 rows that display the title identification number, title name, and the number of books sold: -- Join in WHERE clause. USE pubs SELECT t.title_id, t.title, s.qty FROM titles AS t, sales AS s WHERE t.title_id *= s.title_id -- Join in FROM clause. USE pubs SELECT t.title_id, t.title, s.qty FROM titles AS t LEFT OUTER JOIN sales AS s ON t.title_id = s.title_id In this query, a search condition is also specified in the WHERE clause: -- Join and search condition in WHERE clause. USE pubs SELECT t.title_id, t.title, s.qty FROM titles AS t, sales AS s WHERE t.title_id *= s.title_id AND s.stor_id = '7066' The condition stor_id = '7066' is evaluated along with the join. The join only selects the rows for stor_id 7066 from the sales table, but because it is an outer join null values are supplied as the store information in all the other rows. This query returns 18 rows. The join condition can be moved to the FROM clause, and the stor_id condition left in the WHERE clause: USE pubs SELECT t.title_id, t.title, s.qty FROM titles AS t LEFT OUTER JOIN sales AS s ON t.title_id = s.title_id WHERE s.stor_id = '7066' This query returns only two rows because the restriction of stor_id = '7066' is applied after the left outer join has been performed. This eliminates all the rows from the outer join that have NULL for their stor_id. To return the same information with the join condition in the FROM clause, specify the stor_id = '7066' condition as part of the ON join_criteria section in the FROM clause and remove the WHERE clause: USE pubs SELECT t.title_id, t.title, s.qty FROM titles AS t LEFT OUTER JOIN sales AS s ON t.title_id = s.title_id AND s.stor_id = '7066' -- 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" |
#20
|
|||
|
|||
Better "Join" vs "Where" clause?
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. 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). 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 Other mundane details, too. Vanderghast, Access MVP "Bob Barrows" wrote in message ... David W. Fenton wrote: Rick Brandt wrote in : On Sun, 01 Mar 2009 20:44:08 ?, David W. Fenton wrote: "Bob Barrows" wrote in : If you ever plan to upgrade to SQL Server, you had better get used to using the JOIN syntax, especially for outer joins. Good advice in principle, but in fact, Jet SQL's join syntax is different from SQL Server's (and just about any other database engine), so it won't necessarily upsize better just because you've used a join. What is different about it other than a very liberal use of parenthesis? I have never bothered to figure it out, but the fact is that you can't cut and paste every Jet join into SQL server and expect it to work unchanged. That's interesting. I've never run into anything like that. I just tried a couple different examples which worked fine in my SQL2000 server. Do you have an example of one that would fail? -- 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" |
Thread Tools | |
Display Modes | |
|
|