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

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Sun, 29 Jun 2008 10:26:36 +0100
Message-ID: <f6OdnVW1EKPSyPrVnZ2dneKdnZydnZ2d@giganews.com>


<dananrg_at_yahoo.com> wrote in message
news:3829ceae-09bb-4388-89a9-925852f8ad41_at_34g2000hsh.googlegroups.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

Change Query #2 to:

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

USING means that only one department_id column is output therefore the alias isn't required.

-- 
David Portas
Received on Sun Jun 29 2008 - 04:26:36 CDT

Original text of this message