MySQL MySQL开启与使用？

MySQL ON vs USING?

提问by Nathanael

In a MySQL JOIN, what is the difference between ONand USING()? As far as I can tell, USING()is just more convenient syntax, whereas ONallows 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().

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

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

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

回答by Shlomi Noach

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

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

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

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

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

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

当两个表共享一个与它们连接的名称完全相同的列时，USING很有用。在这种情况下，人们可能会说：

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 ...

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

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

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 ...

Notice the film.film_idqualification in the SELECTclause. It would be invalid to just say film_idsince that would make for an ambiguity:

注意子句中的film.film_id限定SELECT。仅仅说是无效的，film_id因为这会造成歧义：

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

ERROR 1052 (23000)：字段列表中的“film_id”列不明确

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

至于select *，连接列在结果集中ON出现两次 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>

回答by Tom Mac

Thought I would chip in here with when I have found ONto be more useful than USING. It is when OUTERjoins are introduced into queries.

当我发现ON它比USING. 这是在OUTER查询中引入连接的时候。

ONbenefits from allowing the results set of the table that a query is OUTERjoining onto to be restricted while maintaining the OUTERjoin. Attempting to restrict the results set through specifying a WHEREclause will, effectively, change the OUTERjoin into an INNERjoin.

ON受益于允许OUTER在保持OUTER连接的同时限制查询所连接的表的结果集。尝试通过指定WHERE子句来限制结果集将有效地将OUTER连接更改为INNER连接。

Granted this may be a relative corner case. Worth putting out there though.....

当然，这可能是一个相对的极端情况。不过值得放在那里......

For example:

例如：

CREATE TABLE country (
   countryId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
   country varchar(50) not null,
  UNIQUE KEY countryUIdx1 (country)
) ENGINE=InnoDB;

insert into country(country) values ("France");
insert into country(country) values ("China");
insert into country(country) values ("USA");
insert into country(country) values ("Italy");
insert into country(country) values ("UK");
insert into country(country) values ("Monaco");


CREATE TABLE city (
  cityId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  countryId int(10) unsigned not null,
  city varchar(50) not null,
  hasAirport boolean not null default true,
  UNIQUE KEY cityUIdx1 (countryId,city),
  CONSTRAINT city_country_fk1 FOREIGN KEY (countryId) REFERENCES country (countryId)
) ENGINE=InnoDB;


insert into city (countryId,city,hasAirport) values (1,"Paris",true);
insert into city (countryId,city,hasAirport) values (2,"Bejing",true);
insert into city (countryId,city,hasAirport) values (3,"New York",true);
insert into city (countryId,city,hasAirport) values (4,"Napoli",true);
insert into city (countryId,city,hasAirport) values (5,"Manchester",true);
insert into city (countryId,city,hasAirport) values (5,"Birmingham",false);
insert into city (countryId,city,hasAirport) values (3,"Cincinatti",false);
insert into city (countryId,city,hasAirport) values (6,"Monaco",false);

-- Gah. Left outer join is now effectively an inner join 
-- because of the where predicate
select *
from country left join city using (countryId)
where hasAirport
; 

-- Hooray! I can see Monaco again thanks to 
-- moving my predicate into the ON
select *
from country co left join city ci on (co.countryId=ci.countryId and ci.hasAirport)
; 

回答by Robert Rocha

Wikipediahas the following information about USING:

维基百科有以下信息USING：

The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join. In the case above, there will be a single DepartmentID column and no employee.DepartmentID or department.DepartmentID.

然而，USING 构造不仅仅是语法糖，因为结果集不同于具有显式谓词的版本的结果集。具体来说，USING 列表中提到的任何列将只出现一次，并且名称不合格，而不是对于连接中的每个表出现一次。在上述情况下，将有一个 DepartmentID 列，而没有 employee.DepartmentID 或 Department.DepartmentID。

Tables that it was talking about:

它正在谈论的表：

The Postgresdocumentation also defines them pretty well:

在Postgres的文档还定义了他们很好：

The ON clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a WHERE clause. A pair of rows from T1 and T2 match if the ON expression evaluates to true.

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

ON 子句是最通用的一种连接条件：它采用与 WHERE 子句中使用的类型相同的布尔值表达式。如果 ON 表达式的计算结果为真，则来自 T1 和 T2 的一对行匹配。

USING 子句是一种简写，它允许您利用连接两侧对连接列使用相同名称的特定情况。它采用逗号分隔的共享列名称列表，并形成一个连接条件，其中包括对每个名称的相等比较。例如，使用 USING (a, b) 连接 T1 和 T2 会产生连接条件 ON T1.a = T2.a AND T1.b = T2.b。

此外，JOIN USING 的输出抑制了冗余列：不需要打印两个匹配的列，因为它们必须具有相等的值。JOIN ON 生成来自 T1 的所有列，然后是来自 T2 的所有列，JOIN USING 为每个列出的列对（按列出的顺序）生成一个输出列，然后是来自 T1 的任何剩余列，然后是来自 T2 的任何剩余列.

回答by mike rodent

For those experimenting with this in phpMyAdmin, just a word:

对于那些在 phpMyAdmin 中尝试这个的人，只需一句话：

phpMyAdmin appears to have a few problems with USING. For the record this is phpMyAdmin run on Linux Mint, version: "4.5.4.1deb2ubuntu2", Database server: "10.2.14-MariaDB-10.2.14+maria~xenial - mariadb.org binary distribution".

phpMyAdmin 似乎有一些问题USING。作为记录，这是在 Linux Mint 上运行的 phpMyAdmin，版本：“4.5.4.1deb2ubuntu2”，数据库服务器：“10.2.14-MariaDB-10.2.14+maria~xenial - mariadb.org 二进制分发版”。

I have run SELECTcommands using JOINand USINGin both phpMyAdmin and in Terminal (command line), and the ones in phpMyAdmin produce some baffling responses:

我已经在 phpMyAdmin 和终端（命令行）中SELECT使用JOIN和运行命令USING，而 phpMyAdmin 中的命令产生了一些令人困惑的响应：

1) a LIMITclause at the end appears to be ignored.
2) the supposed number of rows as reported at the top of the page with the results is sometimes wrong: for example 4 are returned, but at the top it says "Showing rows 0 - 24 (2503 total, Query took 0.0018 seconds.)"

1)LIMIT末尾的子句似乎被忽略了。
2) 页面顶部报告的假设行数有时是错误的：例如返回 4，但在顶部显示“显示行 0 - 24（总共 2503，查询用了 0.0018 秒。） ”

Logging on to mysql normally and running the same queries does not produce these errors. Nor do these errors occur when running the same query in phpMyAdmin using JOIN ... ON .... Presumably a phpMyAdmin bug.

正常登录mysql并运行相同的查询不会产生这些错误。在 phpMyAdmin 中使用JOIN ... ON .... 大概是 phpMyAdmin 错误。