>> "SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"

On 14 May 2004 13:02:13 -0700, KingGreg wrote:

>All,
>
>Oracle 9i provides a "USING" clause option for inner joins, that
>allows me to say:
>
>SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn
>
>assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable
>for our software make use of, but we also support SQL Server. There
>is no USING option available, and
>
>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>
>causes an ambiguous column error on KeyColumn.

>Is there any equivalent to this Oracle functionality on SQL Server?

>
> create table TBL1 (KeyColumn int not null primary key)
> create table TBL2 (KeyColumn int not null primary key)
> insert TBL1 (KeyColumn)
> values(1)
> insert TBL1 (KeyColumn)
> values(2)
> insert TBL2 (KeyColumn)
> values(1)
> insert TBL2 (KeyColumn)
> values(3)
> SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> drop table TBL1
> drop table TBL2
>
>
> KeyColumn KeyColumn
> ----------- -----------
> 1 1
>
> (1 row(s) affected)
>

KingGreg wrote:
> I understand that I was not clear because you have to be using derived
> table. See below:
>
>
>
>>create table TBL1 (KeyColumn int not null primary key)
>>create table TBL2 (KeyColumn int not null primary key)
>>insert TBL1 (KeyColumn)
>>values(1)
>>insert TBL1 (KeyColumn)
>>values(2)
>>insert TBL2 (KeyColumn)
>>values(1)
>>insert TBL2 (KeyColumn)
>>values(3)
>>SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
>>drop table TBL1
>>drop table TBL2
>>
>>
>>KeyColumn KeyColumn
>>----------- -----------
>>1 1
>>
>>(1 row(s) affected)
>>
>
>
> Try :
>
> 1 SELECT KeyColumn
> 2 FROM (
> 3 SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn
> 4 ) TBL
>
> Gives error : Column 'KeyColumn' specified multiple times for TBL
>
> As you noted it does not fail when running line 3 by itself.
>
> I guess I must be a bad SQL programmer, but this is nonetheless the
> direction I am pursuing because of numerous other limitations in SQL
> Server or Oracle that prevents using some other solution.
>
> KingGreg

Joe Celko wrote:

>>>"SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"
>
> causes an ambiguous column error on KeyColumn <<
>
> And the USING clause is limited to equi-joins. But the real problem is
> that good SQL programmers do not use "SELECT *" in production code. It
> changes at run time and is too unclear and dangerous.
>