Bug with ANSI/92 joins USING clause in Oracle 10g? Can't SELECT on join fields?

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 Received on Sat Jun 28 2008 - 05:49:02 CDT