McEs, A Hacker Life: SQL

Monday, July 18, 2005

SQL

Murray: Like everybody else suggested, you alias the tables. A more readable approach is to actually put the optional "AS" keyword:

SELECT deliveries.departure_contact_id, depaturecontacts.name_last, deliveries.arrival_contact_id, arrivalcontacts.name_last FROM deliveries
LEFT OUTER JOIN contacts AS departurecontacts ON (deliveries.departure_contact_id = departurecontacts.contacts_id)
LEFT OUTER JOIN contacts AS arrivalcontacts ON (deliveries.arrival_contact_id = arrivalcontacts.contact_id);

¶ 1:36 PM

Comments:

Using "AS" is not a good idea, as it is not portable. Oracle, for example, does not understand it.

# posted by

Anonymous : July 18, 2005 1:46 PM

It is a good idea because it is ANSI-standard.Oracle supports that but doesn't force it.But you better use it always even with Oracle because of strange reactions of its SQL optimizer when the alias is the same as actual table name(this is pretty common unfortunately).
Search "cure of madness" in http://www.dbdebunk.com to see a real story in this context!

# posted by

Anonymous : November 04, 2005 8:27 PM

About Me

Twitter Updates