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


.

Relevant Pages

  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... "SQL Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... Even if I were to bypass the Hibernate generated SQL and roll my own, that did not work at all, it returned all rows unfiltered. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: 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 ... you can even have a ON clause on a pure constant. ... ever done comparing equivalent implicit and explicit joins with Jet ...
    (microsoft.public.access.queries)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: the "having" clause
    ... Start in the FROM clause and build a working table from all of the ... rest of the containing query. ... e) Go to the SELECT clause and construct the expressions in the list. ... As you can see, things happen "all at once" in SQL, not from left to ...
    (microsoft.public.sqlserver.programming)
  • Re: Report Parameters - no results
    ... Allen Browne - Microsoft MVP. ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... > What else can I do to try to correct my sql? ... >> the WHERE clause in your query. ...
    (microsoft.public.access.reports)