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?

Answer 1

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

Answer 2

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.

Answer 3

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.