“使用"和“使用"有什么区别?和“开"在 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) ...
我认为这不是真的，但为什么呢?on 子句中的条件如何与连接与 if 在 where 子句中交互?
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屋！
