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

Once you already specific explicit join conditions, if there are columns ambiguity, subsequent joins cannot use USING clause.

place ml_client_account before account_master:

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

Answer 2

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.

Answer 3

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