MysQL中表连接中“using”和“on”之间的区别是什么？

Is this

这是

... T1 join T2 using(ID) where T2.VALUE=42 ...

the same as

同样的

... T1 join T2 on(T1.ID=T2.ID) where T2.VALUE=42 ...

for all types of joins?

对于所有类型的连接？

My understanding of using(ID) is that it's just shorthand for on(T1.ID=T2.ID). Is this true?

我对使用（ID）的理解是它只是on（（。T1.ID = T2.ID）的简写。这是真的？

Now for another question:

现在换另一个问题：

Is the above the same as

以上是一样的

... T1 join T2 on(T1.ID=T2.ID and T2.VALUE=42) ...

This I don't think is true, but why? How does conditions in the on clause interact with the join vs if its in the where clause?

我不认为这是真的，但为什么呢？如果在where子句中，on子句中的条件如何与join连接相互作用？

7 个解决方案

#1

I don't use the USING syntax, since

我不使用USING语法，因为

most of my joins aren't suited to it (not the same fieldname that is being matched, and/or multiple matches in the join) and
我的大多数联接都不适合它（不是匹配的相同字段名，和/或联接中的多个匹配）和
it isn't immediately obvious what it translates to in the case with more than two tables
在具有两个以上表的情况下，它并不是立即显而易见的

ie assuming 3 tables with 'id' and 'id_2' columns, does

即假设有3个带有'id'和'id_2'列的表，确实如此

T1 JOIN T2 USING(id) JOIN T3 USING(id_2)

become

成为

T1 JOIN T2 ON(T1.id=T2.id) JOIN T3 ON(T1.id_2=T3.id_2 AND T2.id_2=T3.id_2)

要么

T1 JOIN T2 ON(T1.id=T2.id) JOIN T3 ON(T2.id_2=T3.id_2)

or something else again?

还是别的什么？

Finding this out for a particular database version is a fairly trivial exercise, but I don't have a large amount of confidence that it is consistent across all databases, and I'm not the only person that has to maintain my code (so the other people will also have to be aware of what it is equivalent to).

为特定的数据库版本找到这个是一个相当简单的练习，但我并不十分相信它在所有数据库中都是一致的，而且我不是唯一一个必须维护我的代码的人（所以其他人也必须知道它相当于什么）。

An obvious difference with the WHERE vs ON is if the join is outer:

WHERE与ON的明显区别在于连接是否为外：

Assuming a T1 with a single ID field, one row containing the value 1, and a T2 with an ID and VALUE field (one row, ID=1, VALUE=6), then we get:

假设T1具有单个ID字段，一行包含值1，T2具有ID和VALUE字段（一行，ID = 1，VALUE = 6），那么我们得到：

SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID) WHERE T2.VALUE=42

gives no rows, since the WHERE is required to match, whereas

不提供任何行，因为WHERE需要匹配，而

SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID AND T2.VALUE=42)

will give one row with the values

将给出一行值

1, NULL, NULL

since the ON is only required for matching the join, which is optional due to being outer.

因为只需要匹配连接，因为外部是可选的。

#2

The USING clause is shorthand for an equi-join of columns, assuming the columns exist in both tables by the same name:

USING子句是列的equi-join的简写，假设两个表中的列都存在相同的名称：

A JOIN B USING (column1)

A JOIN B ON A.column1=B.column1

You can also name multiple columns, which makes joins on compound keys pretty straightforward. The following joins should be equivalent:

您还可以命名多个列，这使得复合键上的连接非常简单。以下连接应该是等效的：

A JOIN B USING (column1, column2)

A JOIN B ON A.column1=B.column1 AND A.column2=B.column2

Note that USING (<columnlist>) is required to have parentheses, whereas ON <expr> is not required to have parentheses (although parens may be used around <expr> just they may be included around an expression in any other context).

请注意，USING（）需要括号，而ON 不需要括号（尽管可以在周围使用parens，只是它们可以包含在任何其他上下文中的表达式周围）。

Also, no other tables joined in the query may have a column by that name, or else the query is ambiguous and you should get an error.

此外，查询中没有其他表加入的列可能具有该名称的列，否则查询不明确，您应该收到错误。

Regarding you question about additional conditions, assuming you use an INNER JOIN it should logically give the same result from the query, but the optimization plan may be affected, depending on the RDBMS implementation. Also OUTER JOIN gives a different result if you include conditions in the join versus the WHERE clause.

关于附加条件的问题，假设您使用INNER JOIN，它应该在逻辑上从查询中提供相同的结果，但优化计划可能会受到影响，具体取决于RDBMS实现。如果在连接和WHERE子句中包含条件，OUTER JOIN也会给出不同的结果。

#3

I believe you are correct - USING(xx) is short hand for joining on two columns with identical names.

我相信你是对的 - 使用（xx）是加入两个名称相同的列的简写。

As for the second question, both queries could be same or may be different depending upon the query planner implementation specific to the database. To find out for yourself (at least in postgres) do an EXPLAIN SELECT ... to see how the query plans will be executed.

至于第二个问题，根据特定于数据库的查询计划器实现，两个查询可以相同或可以不同。要自己查找（至少在postgres中），请执行EXPLAIN SELECT ...以查看查询计划的执行方式。

#4

If there is only one join then there is no difference.

如果只有一个连接，则没有区别。

Downside to the using clause is both tables must have the same column name.

在using子句的下方是两个表必须具有相同的列名。

#5

Your interpretation seems correct. This article may help.

你的解释似乎是对的。这篇文章可能有帮助。

As for the second question, I can't see why the result of your third example should be different from that of the first two. Any condition in an 'ON' clause has the same meaning as if it was in a 'WHERE' clause.

至于第二个问题，我不明白为什么你的第三个例子的结果应该与前两个例子的结果不同。 “ON”子句中的任何条件都与“WHERE”子句中的条件具有相同的含义。

#6

You got the answers here, I need not add to it. Once I did a performance test on this, and USING consistently and always ran faster than ON. Yes I am talking about 10 to 20 ms :) MySQL I am talking about

你在这里得到了答案，我不需要添加它。一旦我对此进行了性能测试，并且始终如一地使用并且总是比ON快。是的我说的是10到20毫秒:) MySQL我在谈论

#7

There is a difference in the result that I don't see mentioned in the other answers. If you do this:

在其他答案中我没有看到结果的差异。如果你这样做：

 JOIN ... ON t1.common = t2.common

then the result set will have two columns named common, specifically t1.common and t2.common, and the unqualified name will not work in the query because it is ambiguous.

那么结果集将有两个名为common的列，特别是t1.common和t2.common，并且非限定名称在查询中不起作用，因为它是不明确的。

If, on the other hand, you do this:

另一方面，如果你这样做：

 JOIN ... USING (common)

then the result set will have only one column named common, and it will be an unqualified name - neither t1.common nor t2.common will be present.

那么结果集只有一个名为common的列，它将是一个不合格的名称 - t1.common和t2.common都不存在。

#1