Re: Which column is taken in JOIN with multiple columns and different content ?

On Mar 3, 4:38 am, Digeratus 2006 <digeratus2..._at_nospam.hotmaildotcom> wrote:
> mer..._at_gmx.net (Tobias Merler) wrote innews:45e84bdf$0$23135$9b4e6d93_at_newsspool1.arcor-online.net:
>
>
>
> > Assume I join two tables.
> > Both of these tables contain a column with name "col1". This column
> > ist not he primary key. However this column contains different values
> > which value is taken ?
>
> > Lets see the following example:
>
> > Select *
> > FROM tablea a, tableb b
> > Where a.id = b.id
>
> > tablea:
>
> > id col1 col2
>
> > 1 aaa ddd
> > 2 bbb eee
> > 3 ccc fff
>
> > tableb:
>
> > id col1 col3
>
> > 1 zzz uuu
> > 2 xxx vvv
> > 3 yyy www
>
> > The result table will look like:
>
> > id col1 col2 col3
>
> > 1 ??? ddd uuu
> > 2 ??? eee vvv
> > 3 ??? fff www
>
> > Which values will be for the questions marks ?
>
> > Ok, I know the data model is not perfect and the two tables could be
> > merged. But keep in mind that the sample is only a simplified version
> > of a more complex SQL statement. So lets concentrate on my question.
>
> > Thank you
>
> > Toby
>
> Toby,
> Because of the conflicting column names, Oracle would give you a syntax
> error, try it. The syntax in Oracle would be select a.*, b.*. The
> resulting table should be apparent now.
>
> Andy Young

I'm nore puzzled about why anyone would think there might be a problem. Would you also predict an error from this?

SQL> select deptno, dname, dname from dept;

Perhaps you are thinking of the error you get when a column is ambiguously defined in a subquery and you then refer to it in the outer query. Received on Sat Mar 03 2007 - 05:52:11 CST