"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

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'

I wouldn't call this a JOIN. It's also something I'd never think of
writing, as it's putting a WHERE clause criterion in the JOIN
clause. Looks like a really bad practice to me. If written thus, I'd
expect it to return the same results in both Jet and SQL Server (and
every other db engine):

SELECT authors.*
FROM authors LEFT JOIN books ON authors.authorID = books.authorID
WHERE authors.City = 'Iqaluit'

The implicit join version:

SELECT authors.*
WHERE (authors.authorID = books.authorID OR books.authorID Is Null)
AND authors.City = 'Iqaluit'

(I *think* that's correct)

If the result sets returned by the implicit join and explicit join
versions are different, then the implicit join has not been properly
written, seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/