Correct, with a join you have to alias or use FQN or you get ambiguous column errors.
"This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Dawson, Michael <[EMAIL PROTECTED]> To: CF-Talk <[email protected]> Sent: Tue Jul 11 17:03:20 2006 Subject: RE: Select * in SQL A well-written driver would then throw an "ambiguous column name" error, if that happens. First, you would need to create a table alias: SELECT t1.* FROM table1 t1 Then, if you add a column, as you mention, that query would not return the newly-added column. You would need to do: SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.fk I don't usually specify table aliases, but this is a good case of why you should. When I join tables, however, I do always use a table alias. It makes it much easier to see where the columns originate. I also think a "SELECT *" puts a somewhat-larget hit on the DB than specifying all column names. I think it is because the DB must look up all of the column names before it can validate the column names. If you specify the column names, then the DB can skip the column name lookup step before validating the column names. I'm waiting for Jochem to give the final answer on this... M!ke -----Original Message----- From: Jim [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 11, 2006 10:39 AM To: CF-Talk Subject: Re: Select * in SQL Heres a real world scenario I've come across: Code throughout the app is using data returned from a query that has select *, and pulls data from more than one table. Someone adds a column to one of the tables in the query that has the same name as a column in another of the tables in the query. CF now returns a query with 2 columns named the same and your data is funked up all over the site because using query.columnname is referencing the wrong column. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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/cf_lists/message.cfm/forumid:4/messageid:246128 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
