On 17 Feb 2002, at 16:09, Helen Borrie wrote:

> At 09:42 PM 16-02-02 -0700, you wrote:
> >> In both cases I would recommend using a join based syntax as in:
> >
> >> SELECT x.A, y.B
> >> FROM TABLEX x
> >> JOIN TABLEY y WHERE Y.B = X.A
> >
> >This has actually been a bone of contention between me and another
> >developer, re what the best way to do joins are.
> >
> >I had been under the impression that
> >
> >'select x.a, y.b from tablex x, tabley y where y.b = x.a' is
> >equivalant, both in terms of result set and in terms of execution
> >speed as the join statement listed above. At least, it's the way
> >I've been doing joins for years now.
> >
> >Is there a functional/speed/etc difference between the two types of
> >joins (Explicit using the join statement, vs implicit using the
> >associated table aliases)?
> >
> >- Jason
> >
> >PS - Please no "Because it's more clear" arguments.. I'm looking for
> >real technical differences.
>
> In some RDBMSs there is a difference in the way the intermediate
> result sets are constructed but, from recollection, Ann H has said
> that IB constructs the same intermediate structures for simple inner
> joins like your example. On simple statements like yours that I've
> tested, the timings seem very similar.
>

However this doesn't mean that it could not potentially change in
the future, as using the above syntax does give the parser more
clues, in that if you add a where on the end such as:

SELECT x.A, y.B
FROM TABLEX x
JOIN TABLEY y WHERE Y.B = X.A
WHERE x.C = 1

Means that the plan could be rearranged so that the records are
extracted from X and then joined to Y. Where as a combined
where statement, this is a lot harder to determine, without building
a lot of extra intellegence into the engine.

SELECT x.A, y.B
FROM TABLEX x, TABLEY y
WHERE Y.B = X.A AND x.C = 1


Besides all of that, it is much clearer.

Paul

Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com