> From: Mark Fuqua [mailto:[EMAIL PROTECTED] > Can anyone explain to me what type of join is created when > the join is done with the WHERE clause as opposed to the FROM > clause in a sql statement. > What are the advantages the joins in the FROM clause. > I know it is because I am still very green but I find WHERE > clause joins much easier to write.
Joins performed in the FROM clause are known as ANSI joins (ANSI-92 is the standard). Joins performed in the WHERE clause are known as traditional joins. ANSI style joins are the preferred method as they come from a standard that can be supported across many database platforms, assuming the platform supports ANSI-92. Some of the benefits of the ANSI style joins are: // start: taken from http://www.databasejournal.com/features/oracle/article.php/10893_2209301_3 Intuitive: The ANSI syntax uses specific, intuitive keywords to describe the exact operations to be performed while the sets of data are being joined. Distinctive: Since the new ANSI syntax is quite different from the traditional join method, it is immediately obvious which sets of data have been joined and what the relationships are. It is no longer necessary to untangle a complex set of selection criteria to determine which are join-related and which are merely filtering the final result set. Clarity: I have had to work with several different dialects of SQL over the past several years, including Informix, Sybase, DB2, and Access, and the syntax for outer joins is slightly different for each dialect. The ANSI syntax, however, leaves no doubt as to which "side" of the join is the outermost. More powerful: The new FULL OUTER JOIN syntax can be used in place of a UNION without fear of forgetting the difference between UNION and UNION ALL. // end ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:221587 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
