"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in
:

A possible reason is that it is shorter than the other
alternatives.

Er, what? Shorter only matters if you're hand-coding your SQL. The
query optimizer doesn't care if equivalent SQL statements are longer
or shorter.

Also,
in Jet, you may have problem with [] names to use the subquery
alternative.

Well, unless you use ANSI 92, in which case you can use () for your
derived tables, and not have the bracket problems.

Compa

FROM a LEFT JOIN [b b] (ON a.ab=[b b].ab AND [b b].bb=cc)

it is shorter than

FROM a LEFT JOIN (SELECT * from [b b] WHERE bb=cc) AS x ON
a.ab=x.ab

which is itself closer to the point, shorter and less prone to
error than to bring the condition to the where clause.

I don't even understand the first SQL statement, to be honest.

With the condition brought to the where clause, I doubt the
optimizer will use the same execution plan.

I think that when they JOIN and the WHERE clause actually are
equivalent, they will optimize the same, though that's going to be
highly dependent on the database engine. Certainly every test I've
ever done comparing equivalent implicit and explicit joins with Jet
has optimized identically (and a few years ago, I spent quite a bit
of time testing that, since I had feared that JOINS were better
optimized than the equivalent WHERE clauses -- I was wrong in Jet).

Indeed, with all in the ON clause, including
the sub-query, you clearly make the join by 'touching only'
records where [b b].bb = cc; while with the where clause, your
optimizer ***may*** miss that short-cut, decide to "make" the join
and then decide to make the elaborated test on each row of the
(then possibly huge) result of the join... much less efficient. It
is a possibility, not something occurring in each and every cases.

Well, again, it's going to be highly dependent on the database
engine involved, and the type of join you're trying to implement.
Left/Right joins are harder to code as WHERE clauses than they are
as joins, because it usually takes more than one WHERE criterion to
say the same thing as the JOIN statement.

With Jet, the first solution works, while I am not sure the second
does, due to the [ ]. Sure, sure, why someone would use ill form
name in the first place... It just happens some people do.

I still stand that the syntax SHOULD be avoided, in Jet, though,
mainly if the condition implying just one table is the preserved
table of the outer join.

I never even knew that you could join on a constant. I still can't
wrap my head around the very concept, to be honest.

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