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)
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