Mixing "USING" and "ON" in Oracle ANSI join

Question

I wrote an Oracle SQL expression like this:

SELECT
...
FROM mc_current_view a
JOIN account_master am USING (account_no)
JOIN account_master am_loan ON (am.account_no = am_loan.parent_account_no)
JOIN ml_client_account mca USING (account_no)

When I try to run it, Oracle throws an error in the line with "ON" self-join saying: "ORA-25154: column part of USING clause cannot have qualifier".

If I omit the "am" qualifier, it says: "ORA-00918: column ambiguously defined".

What's the best way to resolve this?

score 14 · Accepted Answer · answered Jan 26 '09 at 18:25

14

The error message is actually (surprise!) telling you exactly what the problem is. Once you use the USING clause for a particular column, you cannot use a column qualifier/table alias for that column name in any other part of your query. The only way to resolve this is to not use the USING clause anywhere in your query, since you have to have the qualifier on the second join condition:

SELECT
...
FROM mc_current_view a
JOIN account_master am ON (a.account_no = am.account_no)
JOIN account_master am_loan ON (am.account_no = am_loan.parent_account_no)
JOIN ml_client_account mca ON (a.account_no = mca.account_no);

answered Jan 26 '09 at 18:25

DCookie

42,630
11
83
92

1

Can you please give a ref for this: "Once you use the USING clause for a particular column, you cannot use a column qualifier/table alias for that column name in any other part of your query."? It looks like a stupid constraint and invalidates the sole existence of USING. – TWiStErRob Aug 27 '13 at 12:29
It's been so long I don't have the reference at hand. However, if you try it, you'll see it is true (at least in 10g). IMO, the USING clause is perfectly useless. – DCookie Aug 29 '13 at 00:13

score 8 · Answer 2 · answered Jan 19 '09 at 10:02

My preference is never to use USING; always use ON. I like to my SQL to be very explicit and the USING clause feels one step removed in my opinion.

In this case, the error is coming about because you have account_no in mc_current_view, account_master, and ml_client_account so the actual join can't be resolved. Hope this helps.

score 0 · Answer 3 · answered Mar 14 '11 at 18:45

The using is cleaner (imo) but it is still desirable to externally refererence the join fields as in the org example or an example like this:

select A.field,
       B.field,
       (select count(C.number)
          from tableC C
         where C.join_id = join_id  -- wrong answer w/o prefix, exception with.
        ) avg_number
  from tableA A
  join tableB B using (join_id);

It gives the wrong answer because the join_id within the subquery implies C.join_id (matching all records) rather than A or B. Perhaps the best way to resolve might be just to allow explicit references with using, having the best of both worlds. Seems like there is a need because of cases like these.

Mixing "USING" and "ON" in Oracle ANSI join

3 Answers3

Linked