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/

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

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

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"

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

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/

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

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"

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"

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"