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, in a client/server production there are differences which will affect performance. First, you would rarely use a query like your example for fetching a dataset into your client application. You would limit the rows returned with an "authentic" WHERE clause. So, in real life, the implicit join clause would be a mixture of join criteria and authentic WHERE criteria.

There is a difference when you come to work with the output sets in a client application. Variations in the values applied to authentic WHERE criteria don't affect the structure of the output set - therefore, when applying a different set of WHERE values, it is not necessary to re-prepare a dataset that uses the explicit syntax. You just apply parameters to refresh the WHERE clause.

If you have join criteria in the WHERE clause, your data access layer either has to have some mechanism to distinguish WHERE criteria from join criteria or it has to invalidate the entire statement and effectively submit a new one, thus requiring a complete reprepare sequence even if it's only the WHERE criteria that changed.

Some of the RAD environments (e.g. IBO in Delphi and BCCB) take advantage of being able to keep a statement prepared, so it becomes a definitely GOOD thing to avoid the implicit syntax.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________