“使用"和“使用"之间有什么区别?和“在...上"在表中加入MySQL? [英] What's the difference between "using" and "on" in table joins in MySQL?
问题描述
是这个
... T1 join T2 using(ID) where T2.VALUE=42 ...
与
... T1 join T2 on(T1.ID=T2.ID) where T2.VALUE=42 ...
对于所有类型的联接?
我对using(ID)的理解是，它只是on(T1.ID=T2.ID)的简写.这是真的吗?
My understanding of using(ID) is that it's just shorthand for on(T1.ID=T2.ID). Is this true?
现在再问一个问题:
Now for another question:
以上与
... T1 join T2 on(T1.ID=T2.ID and T2.VALUE=42) ...
我不认为这是真的，但是为什么呢?与在where子句中相比，on子句中的条件如何与联接交互?
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?
推荐答案
我不使用USING语法，因为
I don't use the USING syntax, since
- 我的大多数联接都不适合(不是相同的字段名被匹配，和/或联接中有多个匹配项)和
- 在两个以上的表的情况下，转换成什么并不立即显而易见
即假设3个表具有"id"和"id_2"列，
ie assuming 3 tables with 'id' and 'id_2' columns, does
T1 JOIN T2 USING(id) JOIN T3 USING(id_2)
成为
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)
还是其他?
针对特定的数据库版本进行查找是一个相当琐碎的练习，但是我对它在所有数据库中都保持一致没有很大的信心，而且我不是唯一需要维护我的代码的人(因此其他人也必须知道它的含义).
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).
与WHERE和ON的明显区别是，如果连接是外部的:
An obvious difference with the WHERE vs ON is if the join is outer:
假设一个T1带有一个ID字段，一行包含值1，一个T2带有一个ID和VALUE字段(一行，ID = 1，VALUE = 6)，那么我们得到:
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:
SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID) WHERE T2.VALUE=42
不提供任何行，因为需要匹配WHERE，而
gives no rows, since the WHERE is required to match, whereas
SELECT T1.ID, T2.ID, T2.VALUE FROM T1 LEFT OUTER JOIN T2 ON(T1.ID=T2.ID AND T2.VALUE=42)
将使用值给出一行
1, NULL, NULL
因为只需要ON才能匹配联接，这是可选的，因为它是外部的.
since the ON is only required for matching the join, which is optional due to being outer.
这篇关于“使用"和“使用"之间有什么区别?和“在...上"在表中加入MySQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
