Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!34g2000hsh.googlegroups.com!not-for-mail
From: dananrg@yahoo.com
Newsgroups: comp.databases.oracle.server
Subject: Bug with ANSI/92 joins USING clause in Oracle 10g? Can't SELECT on 
 join fields?
Date: Sat, 28 Jun 2008 03:49:02 -0700 (PDT)
Organization: http://groups.google.com
Lines: 35
Message-ID: <3829ceae-09bb-4388-89a9-925852f8ad41@34g2000hsh.googlegroups.com>
NNTP-Posting-Host: 24.163.52.187
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1214650143 2136 127.0.0.1 (28 Jun 2008 10:49:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 28 Jun 2008 10:49:03 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 34g2000hsh.googlegroups.com; posting-host=24.163.52.187; 
 posting-account=K8CuEAkAAAC5wTKBRAU1aEPOm2ZVfy3u
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) 
 Gecko/20080404 Firefox/2.0.0.14,gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.oracle.server:445999
X-Received-Date: Sat, 28 Jun 2008 06:49:03 EDT (text.usenetserver.com)

The following query, using the ON clause, works fine:

-- Query #1
select e.employee_id, d.department_id, d.department_name
from employees e INNER JOIN departments d
ON e.department_id = d.department_id

The same query, employing (pun intended) the USING clause does not
work:

-- Query #2
select e.employee_id, d.department_id, d.department_name
from employees e INNER JOIN departments d
USING (department_id)

ORA-25154: column part of USING clause cannot have qualifier

Finally, the above query modified to SELECT * works fine:

-- Query #3
select *
from employees e INNER JOIN departments d
USING (department_id)

Is the error from query #2 expected based on how INNER JOINS should
work as specified in ANSI/92, or is it a bug in 10g? And if it's a bug
in 10g, has it been fixed in 11g? Here's hoping it's merely user error
on my part.

I like the USING clause for simplifying joins. It's especially helpful
for increasing readability on joins for tables with large composite
keys (e.g. > 2 key fields). But if I can't SELECT on any fields
employed in the USING clause, that's a show stopper for me.

Dana