MS-SQL Server equivalent to Oracle 9i?

[KingGreg]

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?

KingGreg

[Joe Celko]

Re: MS-SQL Server equivalent to Oracle 9i?

>> "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.

NATURAL JOIN and USING were two of the worst ideas we put into SQL-92.
I hope they get deprecated soon.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hugo Kornelis]

Re: MS-SQL Server equivalent to Oracle 9i?

On 14 May 2004 13:02:13 -0700, KingGreg wrote:
[color=blue]
>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.[/color]

I can't reproduce this error:

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)

Can you post the actual SQL that returns this error, as I assume there is
an error somewhere in the query.

[color=blue]
>Is there any equivalent to this Oracle functionality on SQL Server?[/color]

No, there isn't.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[KingGreg]

Re: MS-SQL Server equivalent to Oracle 9i?

I understand that I was not clear because you have to be using derived
table. See below:

[color=blue]
>
> 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)
>[/color]

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

[Serge Rielau]

Re: MS-SQL Server equivalent to Oracle 9i?

Can you be more specific?
I agree with Joe that USING and NATURAL JOIN are undesiravel features,
especially since their only purpose in life seems to be to add convenience.
Obviously you are of a different opinion. As a developer I (and quite
likely MS folks listening in) am curious to learn where you see the
value ad.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Re: MS-SQL Server equivalent to Oracle 9i?

KingGreg wrote:[color=blue]
> I understand that I was not clear because you have to be using derived
> table. See below:
>
>
>[color=green]
>>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)
>>[/color]
>
>
> 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[/color]

If in Oracle ... I suspect what you are trying to do is:

SELECT KeyColumn
FROM (
SELECT *
FROM TBL1
WHERE TBL1.KeyColumn = TBL2.KeyColumn) ;

Using ISO standard syntax. If in 9i or above you could also use
ANSI standard syntax.

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[David Portas]

Re: MS-SQL Server equivalent to Oracle 9i?

As you have an INNER JOIN it doesn't matter which value of keycolumn you
reference as long as you specify an alias. It's best to avoid using SELECT *
in production code anyway (except in an EXISTS subquery). Try this:

SELECT keycolumn
FROM
(SELECT Tbl1.keycolumn
FROM Tbl1 JOIN Tbl2
ON Tbl1.keycolumn = Tbl2.keycolumn) TBL

--
David Portas
SQL Server MVP
--

[William Cleveland]

Re: MS-SQL Server equivalent to Oracle 9i?

Joe Celko wrote:
[color=blue][color=green][color=darkred]
>>>"SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn"[/color][/color]
>
> 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.
>[/color]

It's not dangerous if your client code accesses the return fields by
name, and not by number. It is, however, generally returning more
data than you need, so it's a waster of resources, and you still
shouldn't do it.

Bill