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?

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

Solution 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.

Comments

[TWiStErRob]

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.

[DCookie]

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.