Oracle USING 子句最佳实践

Oracle USING clause best practice

提问by RAY

Disclaimer: I'm a developer and not a DBA.

免责声明：我是开发人员而不是 DBA。

I've been a huge fan of the USING clause in Oracle since I accidentally stumbled upon it and have used it in place of the old-fashioned ON clause to join fact tables with dimension tables ever since. To me, it creates a much more succinct SQL and produces a more concise result set with no unnecessary duplicated columns.

我一直非常喜欢 Oracle 中的 USING 子句，因为我不小心偶然发现了它，并使用它代替了老式的 ON 子句，从那时起将事实表与维度表连接起来。对我来说，它创建了一个更简洁的 SQL 并生成一个更简洁的结果集，没有不必要的重复列。

However, I was asked yesterday by a colleague to convert all my USING clauses into ONs. I will check with him and ask him what his reasons are. He works much more closely with the database than I do, so I assume he has some good reasons.

但是，昨天一位同事要求我将我所有的 USING 子句转换为 ON。我会跟他核实一下，问他的理由是什么。他与数据库的工作比我更密切，所以我认为他有一些很好的理由。

I have not heard back from him (we work in different timezones), but I wonder if there are any guidelines or best practices regarding the use of the "using" clause? I've googled around quite a bit, but have not come across anything definitive. In fact, I've not even even a good debate anywhere.

我没有收到他的回复（我们在不同的时区工作），但我想知道是否有关于使用“using”子句的任何指导方针或最佳实践？我在谷歌上搜索了很多，但没有遇到任何明确的问题。事实上，我什至没有在任何地方进行过很好的辩论。

Can someone shed some light on this? Or provide a link to a good discussion on the topic?

有人可以对此有所了解吗？或者提供有关该主题的良好讨论的链接？

Thank you!

谢谢！

回答by Alex Poole

You're presumably already aware of the distinction, but from the documentation:

您可能已经意识到这种区别，但是从文档中：

ON conditionUse the ONclause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHEREclause.

USING (column)When you are specifying an equijoin of columns that have the same name in both tables, the USING columnclause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.

ON条件使用ON子句指定连接条件。这样做可以让您指定独立于WHERE子句中的任何搜索或过滤条件的连接条件。

USING ( column)当您指定两个表中具有相同名称的列的等值连接时，该USING column子句指示要使用的列。仅当两个表中的连接列具有相同名称时才能使用此子句。在此子句中，不要使用表名或表别名来限定列名。

So these would be equivalent:

所以这些是等价的：

select e.ename, d.dname
from emp e join dept d using (deptno);

select e.ename, d.dname
from emp e join dept d on d.deptno = e.deptno;

To a large extent which you use is a matter of style, but there are (at least) two situations where you can't use using: (a) when the column names are not the same in the two tables, and (b) when you want to use the joining column:

在很大程度上，您使用的是风格问题，但（至少）有两种情况您不能使用using：（a）当两个表中的列名不同时，以及（b）当您要使用连接列：

select e.ename, d.dname, d.deptno
from emp e join dept d using(deptno);

select e.ename, d.dname, d.deptno
                         *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

You can of course just leave off the qualifier and select ..., deptno, as long as you don't have another table with the same column that isn't joined using it:

您当然可以不使用限定符 and select ..., deptno，只要您没有使用它连接的具有相同列的另一个表：

select e.ename, d.dname, deptno
from emp e join dept d using (deptno) join mytab m using (empno);

select e.ename, d.dname, deptno
                         *
ERROR at line 1:
ORA-00918: column ambiguously defined

In that case you can onlyselect the qualified m.deptno. (OK, this is rather contrived...).

在这种情况下，您只能选择合格的m.deptno. （好吧，这有点做作......）。

The main reason I can see for avoiding usingis just consistency; since you sometimes can't use it, occasionally switching to onfor those situations might be a bit jarring. But again that's more about style than any deep technical reason.

我可以看到避免的主要原因using只是一致性；由于您有时无法使用它，因此偶尔切换到on这些情况可能会有点刺耳。但同样，这更多是关于风格而不是任何深层的技术原因。

Perhaps your colleague is simply imposing (or suggesting) coding standards, but only they will know that. It also isn't quite clear if you're being asked to change some new code you've written that is going through review, or old code. If it's the latter then regardless of the reasons for them preferring on, I think you'd need to get a separate justification for modifying proven code, as there's a risk of introducing new problems even when the modified code is retested - quite apart from the cost/effort involved in the rework and retesting.

也许您的同事只是在强加（或建议）编码标准，但只有他们自己知道。如果您被要求更改一些正在审核中的新代码或旧代码，这也不太清楚。如果是后者，那么无论他们更喜欢的原因是什么on，我认为您需要为修改经过验证的代码获得单独的理由，因为即使重新测试修改后的代码也有引入新问题的风险 - 除了成本之外/努力参与返工和重新测试。

A couple of things strike me about your question though. Firstly you describes the onsyntax as 'old-fashioned', but I don't think that's fair - both are valid and current (as of SQL:2011 I think, but citation needed!). And this:

不过，关于你的问题，有几件事让我印象深刻。首先，您将on语法描述为“老式”，但我认为这不公平——两者都是有效的和最新的（我认为从 SQL:2011 开始，但需要引用！）。和这个：

produces a more concise result set with no unnecessary duplicated columns.

生成更简洁的结果集，没有不必要的重复列。

... which I think suggests you're using select *, otherwise you would just select one of the values, albeit with a couple of extra characters for the qualifier. Using select *is generally considered bad practice (herefor example) for anything other than ad hoc queries and some subqueries.

...我认为这表明您正在使用select *，否则您只需选择其中一个值，尽管为限定符添加了几个额外的字符。对于临时查询和某些子查询以外的任何内容，使用select *通常被认为是不好的做法（例如此处）。

回答by Vincent Malgrat

Related question.

相关问题。

It seems the main difference is syntactic: the columns are merged in a USINGjoin.

似乎主要区别在于语法：列在USING连接中合并。

In all cases this means that you can't access the value of a joined column from a specific table, in effect some SQL will not compile, for example:

在所有情况下，这意味着您无法访问特定表中连接列的值，实际上某些 SQL 将无法编译，例如：

SQL> WITH t AS (SELECT 1 a, 2 b, 3 c FROM dual),
  2       v AS (SELECT 1 a, 2 b, 3 c FROM dual)
  3  SELECT t.* FROM t JOIN v USING (a);

SELECT t.* FROM t JOIN v USING (a)
         ^    
ORA-25154: column part of USING clause cannot have qualifier

In an outer join this means you can't access the outer table value:

在外部联接中，这意味着您无法访问外部表值：

SQL> WITH t AS (SELECT 1 a, 2 b, 3 c FROM dual),
  2       v AS (SELECT NULL a, 2 b, 3 c FROM dual)
  3  SELECT * FROM t LEFT JOIN v USING (a)
  4   WHERE v.a IS NULL;

 WHERE v.a IS NULL
         ^
ORA-25154: column part of USING clause cannot have qualifier

This means that there is no equivalent for this anti-join syntax with the USINGclause:

这意味着此反连接语法与以下USING子句没有等效项：

SQL> WITH t AS (SELECT 1 a, 2 b, 3 c FROM dual),
  2       v AS (SELECT NULL a, 2 b, 3 c FROM dual)
  3  SELECT * FROM t LEFT JOIN v ON v.a = t.a
  4   WHERE v.a IS NULL;

         A          B          C A          B          C
---------- ---------- ---------- - ---------- ----------
         1          2          3  

Apart from this, I'm not aware of any difference once the SQL is valid.

除此之外，一旦 SQL 有效，我就不知道有什么区别。

However, since it seems this syntax is less commonly used, I wouldn't be surprised if there were specific bugs that affect only the USINGclause, especially in early versions where ANSI SQL was introduced. I haven't found anything on MOS that could confirm this, partly because the USINGword is ubiquitous in bug descriptions.

但是，由于这种语法似乎不太常用，如果存在仅影响USING子句的特定错误，我不会感到惊讶，尤其是在引入 ANSI SQL 的早期版本中。我在 MOS 上没有找到任何可以证实这一点的内容，部分原因是USING一词在错误描述中无处不在。

If the reason for not using this feature is because of bugs, it seems to me the burden of the proof lies with your colleague: the bugs must be referenced/documented, so that the ban can eventually be lifted once the bugs are patched (database upgrade...).

如果不使用此功能的原因是由于错误，在我看来，举证责任在于您的同事：必须引用/记录错误，以便在修补错误后最终可以解除禁令（数据库升级...）。

If the reason is cosmetic or part of a coding convention, surely it must be documented too.

如果原因是装饰性的或编码约定的一部分，那么肯定也必须记录下来。

回答by Jon Waterhouse

With USING you also cannot do a join like: select a.id,aval,bval,cval from a left join b on a.id = b.id left join c on c.id = b.id;

使用 USING 你也不能做这样的连接： select a.id,aval,bval,cval from a left join b on a.id = b.id left join c on c.id = b.id;

that is, only give the column from C when it is matched to a row in the B table.

即，仅在与 B 表中的行匹配时才给出来自 C 的列。