它主要是语法糖，但有几个差异是值得注意的:

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

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

当两个表共享一个名称完全相同的列时，USING非常有用。在这种情况下，人们可以说:

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

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

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

为了说明，要使用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 ...

注意这部电影。SELECT子句中的film_id资格。只说film_id是无效的，因为这会导致歧义:

错误1052(23000):字段列表中的列“film_id”不明确

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

对于那些在phpMyAdmin中尝试这个的人，只有一个词:

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

我已经在phpMyAdmin和终端(命令行)中使用JOIN和using运行SELECT命令，在phpMyAdmin中产生一些令人困惑的响应:

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

正常登录mysql并运行相同的查询不会产生这些错误。当在phpMyAdmin中使用JOIN运行相同的查询时，也不会发生这些错误…在…大概是phpMyAdmin bug。

维基百科有以下关于使用的信息:

然而，USING结构不仅仅是语法糖，因为 属性的版本的结果集与之不同 明确的谓词。特别是USING中提到的任何列 List将只出现一次，带有一个非限定名称，而不是一次 对于连接中的每个表。在上面的例子中，会有一个单点 departmentd列，无员工。DepartmentID或 department.DepartmentID。

它所谈论的表格:

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.

简短的回答:

when子句有歧义 ON: when子句有不同的比较参数

当我发现ON比USING更有用的时候，我想在这里分享一下。它是在查询中引入OUTER连接时。

ON的好处在于，在维护OUTER连接的同时，允许限制查询OUTER连接到的表的结果集。试图通过指定WHERE子句来限制结果集，实际上会将OUTER连接更改为INNER连接。

尽管这可能是一个相对的极端情况。值得在.....

例如:

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)
; 

它主要是语法糖，但有几个差异是值得注意的:

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

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

当两个表共享一个名称完全相同的列时，USING非常有用。在这种情况下，人们可以说:

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

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

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

为了说明，要使用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 ...

注意这部电影。SELECT子句中的film_id资格。只说film_id是无效的，因为这会导致歧义:

错误1052(23000):字段列表中的列“film_id”不明确

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