MySQL加入与使用? [英] 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 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:

错误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加入与使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！