Re: alias - do i need to use?

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:zNOdnb2Mh_9zV5LfRVn-hQ_at_comcast.com...
>
> "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
> news:1108315901.812802.286460_at_o13g2000cwo.googlegroups.com...
> > Besides Sybrand's accurately directed comments about actually trying
> > the SQL and reading the manuals if you are using the standard DEPT
> > table found in many Oracle examples then from memory I believe the
> > correct column name is dname not d_name. What you probably meant to
> > write was d.dname.
> >
> > As a matter of good form you should alias the column names in the
> > select list whenever you have a join so that whenever another developer
> > reads the SQL statement he or she will know where the columns come from
> > even without being familar with the tables. As long as all the columns
> > names in the select list are unique amoung the tables in the join there
> > is no technical requirement to alias the columns in the select list.
> > The same is true for the where clause. When the same column name
> > appears in two or more tables then you need to identify from which
> > table the column is to be accessed from.
> >
> > HTH -- Mark D Powell --
> >
>
> in addition to mark's comments -- even if a column of the same name does
not
> currently occur in both tables, it's not a bad idea to fully alias every
> column reference. that way, if either table is altered to include a new
> column with the same name as a column in the other table, the SQL won't
> break:
>
> SQL> select ename, dname, loc
> 2 from emp e, dept d
> 3 where e.deptno = d.deptno
> 4 /
>
> ENAME DNAME LOC
> ---------- -------------- -------------
> CLARK ACCOUNTING NEW YORK
> KING ACCOUNTING NEW YORK
> MILLER ACCOUNTING NEW YORK
> smith RESEARCH DALLAS
> ADAMS RESEARCH DALLAS
> FORD RESEARCH DALLAS
> SCOTT RESEARCH DALLAS
> JONES RESEARCH DALLAS
> ALLEN SALES CHICAGO
> BLAKE SALES CHICAGO
> MARTIN SALES CHICAGO
> JAMES SALES CHICAGO
> TURNER SALES CHICAGO
> WARD SALES CHICAGO
>
> SQL> alter table emp add loc varchar2(30);
>
> Table altered.
>
> SQL> select ename, dname, loc
> 2 from emp e, dept d
> 3 where e.deptno = d.deptno
> 4 /
> select ename, dname, loc
> *
> ERROR at line 1:
> ORA-00918: column ambiguously defined
>
> ++ mcs
>

Please correct me if I am wrong, but it seems to me I recall having read that there is also a slight performance advantage gained by explicitly aliasing all of the selected columns, whether a risk of ambiguity exists or not.

-- 
Randy Harris
(tech at promail dot com)