MySQL JOIN ON vs USING? [英] MySQL JOIN ON vs USING?

问题描述

在 MySQL JOIN 中，ON 和 USING() 有什么区别?据我所知，USING() 只是更方便的语法，而 ON 在列名不相同时允许更多的灵活性.然而，这种差异非常小，你会认为他们会取消 USING().

In a MySQL JOIN, what is the difference between ON and USING()? As far as I can tell, USING() is just more convenient syntax, whereas ON allows a little more flexibility when the column names are not identical. However, that difference is so minor, you'd think they'd just do away with USING().

还有比这更重要的东西吗?如果是，在特定情况下我应该使用哪个?

Is there more to this than meets the eye? If yes, which should I use in a given situation?

推荐答案

它主要是语法糖，但有一些差异值得注意:

It is mostly syntactic sugar, but a couple differences are noteworthy:

ON 是两者中更通用的.可以在一个列、一组列甚至一个条件上连接表.例如:

ON is the more general of the two. One can join tables ON a column, a set of columns and even a condition. For example:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

USING 在两个表共享它们连接的同名列时很有用.在这种情况下，人们可能会说:

USING is useful when both tables share a column of the exact same name on which they join. In this case, one may say:

SELECT ... FROM film JOIN film_actor USING (film_id) WHERE ...

另外一个好处是不需要完全限定连接列:

An additional nice treat is that one does not need to fully qualify the joining columns:

SELECT film.title, film_id -- film_id is not prefixed
FROM film
JOIN film_actor USING (film_id)
WHERE ...

为了说明，要使用 ON 执行上述操作，我们必须编写:

To illustrate, to do the above with ON, we would have to write:

SELECT film.title, film.film_id -- film.film_id is required here
FROM film
JOIN film_actor ON (film.film_id = film_actor.film_id)
WHERE ...

注意 SELECT 子句中的 film.film_id 限定条件.只说 film_id 是无效的，因为这会造成歧义:

Notice the film.film_id qualification in the SELECT clause. It would be invalid to just say film_id since that would make for an ambiguity:

ERROR 1052 (23000):字段列表中的film_id"列不明确

ERROR 1052 (23000): Column 'film_id' in field list is ambiguous

对于select *，ON时加入列在结果集中出现两次，而USING只出现一次:

As for select *, the joining column appears in the result set twice with ON while it appears only once with USING:

mysql> create table t(i int);insert t select 1;create table t2 select*from t;
Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select*from t join t2 on t.i=t2.i;
+------+------+
| i    | i    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select*from t join t2 using(i);
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

这篇关于MySQL JOIN ON vs USING?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！