Mike Raiford <mra### [at] hotmailcom> wrote:
> Nicolas Alvarez wrote:

> >> Dan Byers wrote:
> >>
> >>> Your example would be fine, although supposedly M$ frowns on that and
> >>> is trying
> >>> to steer people away from it.
> >>
> >> isn't the join syntax ANSI SQL? I thought the simpler syntax was
> >> actually a feature of Transact SQL, (Though I think other RDBMS
> >> engines use it, too...)
> >
> > I think what Dan meant is that Tom's original example is frowned upon,
> > and people should use JOINs instead.
>
> But, frowned upon in the general sense?

Actually, it's frowned upon where outer joins are concerned.  This is from the
online manual (SQL Server 2000 - I haven't checked SQL Server 2005 yet):

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

What this translates to is the following code produces vastly different results:

SELECT  P.NAME, L.TYPE
FROM    PERSON P LEFT OUTER JOIN LIMBS L ON
             P.INDEX = L.PERSONINDEX
             AND P.NAME = 'Bones'

SELECT  P.NAME, L.TYPE
FROM    PERSON P LEFT OUTER JOIN LIMBS L ON
             P.INDEX = L.PERSONINDEX
WHERE   P.NAME = 'Bones'

The first example pulls everyone in PERSON and any limbs they have, provided the
P.NAME = 'Bones'.  If there are no matches, L.TYPE will be null.  Exactly how
it's supposed to work.  In example number 2, the WHERE clause acts like a
global filter, so it will throw out any record that doesn't equal 'Bones' in
the P.NAME field, therefore acting like an INNER JOIN.

--
Dan
GoofyGraffix.com

Post a reply to this message