在 Oracle ANSI 连接中混合使用“USING”和“ON”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/456684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mixing "USING" and "ON" in Oracle ANSI join
提问by Sergey Stadnik
I wrote an Oracle SQL expression like this:
我写了一个这样的 Oracle SQL 表达式：
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".
当我尝试运行它时，Oracle 在带有“ON”自连接的行中抛出一个错误，说：“ORA-25154：USING 子句的列部分不能有限定符”。
If I omit the "am" qualifier, it says: "ORA-00918: column ambiguously defined".
如果我省略“am”限定符，它会显示：“ORA-00918：列定义不明确”。
What's the best way to resolve this?
解决此问题的最佳方法是什么？
回答by DCookie
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:
错误消息实际上（令人惊讶！）告诉您问题是什么。一旦对特定列使用了 USING 子句，就不能在查询的任何其他部分为该列名使用列限定符/表别名。解决此问题的唯一方法是不要在查询中的任何地方使用 USING 子句，因为您必须在第二个连接条件上使用限定符：
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);
回答by Nick Pierpoint
My preference is never to use USING; always use ON. I like to my SQL to be very explicit and the USINGclause feels one step removed in my opinion.
我的偏好是从不使用USING；始终使用ON。我喜欢我的 SQL 非常明确，并且在我看来，USING子句感觉被删除了一步。
In this case, the error is coming about because you have account_noin mc_current_view, account_master, and ml_client_accountso the actual join can't be resolved. Hope this helps.
在这种情况下，出现错误是因为您有account_noin mc_current_view, account_master，ml_client_account因此无法解析实际的连接。希望这可以帮助。
回答by crokusek
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:
使用更干净（imo），但仍然需要像 org 示例或这样的示例那样从外部引用连接字段：
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.
它给出了错误的答案，因为子查询中的 join_id 暗示了 C.join_id（匹配所有记录）而不是 A 或 B。也许解决的最佳方法可能只是允许显式引用与 using，两全其美。由于这些情况，似乎有必要。
