Several people wrote:
>
> "ROM table1,table2 WHERE table1.foo = table2.foo (Is NOT ANSI compliant
> SQL)"
There is nothing wrong with that syntax under any version of the SQL standard
from ANSI or the ISO/IEC. This syntax is not even deprecated in the standard
and I think it is highly unlikelly it ever will be. It is supported by more
implementations then a syntax using the JOIN keyword and there is no way any
implementation is going to deprecate the syntax. The difference in processing
time should be neglegible too.
> This should be avoided.
But that is true too :)
The reason why you should use explicit JOIN syntax instead of just a comma
separated list of fields is that it helps you to write SQL and it helps you to
write good SQL. When you use the explicit JOIN syntax you can distinguish the
predicates that relate your tables to eachother from the predicates the filter
your resultset. For an example, compare the following queries:
SELECT
*
FROM
dates_table,
events_table
WHERE
dates_table.EventID = events_table.EventID
AND
dates_table.startdate > '2007-01-01'
SELECT
*
FROM
dates_table
INNER JOIN
events_table ON dates_table.EventID = events_table.EventID
WHERE
dates_table.startdate > '2007-01-01'
In the first form, it is not immediately clear which of the two predicates
relates the two tables and which one filters the resultset. In the second form,
it is.
Additionally, for the way I write queries, it is a much more natural form. I
always start with the FROM and list the tables and the relations between them
that follow from the data model. Then I list the records I want back from the
query, then the filter on the number of rows. If required I can themn add the
links to any 'dimension tables' (loosely used here) by just editiong the query
in one place.
And if you are worried about the verbosity of explicit JOIN syntax, there are
some shortcuts. In this case:
SELECT *
FROM dates_table NATURAL JOIN events_table
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:253705
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
