David W. Fenton wrote:
"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.

Why not? it's equivalent to this, which is certailnly a join:

SELECT authors.*
FROM authors LEFT JOIN
(select authorid from books where City = 'Iqaluit') as books
ON authors.authorID = books.authorID




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):

See Michel's post. It doesn't.

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