简短的回答:

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

对于那些在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。

简短的回答:

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

数据库表

为了演示USING和ON子句是如何工作的，让我们假设我们有下面的post和post_comment数据库表，它们通过post_comment表中的post_id外键列引用post表中的post_id主键列形成一对多的表关系:

父post表有3行:

| post_id | title     |
|---------|-----------|
| 1       | Java      |
| 2       | Hibernate |
| 3       | JPA       |

post_comment子表有3条记录:

| post_comment_id | review    | post_id |
|-----------------|-----------|---------|
| 1               | Good      | 1       |
| 2               | Excellent | 1       |
| 3               | Awesome   | 2       |

使用自定义投影的JOIN ON子句

传统上，在编写INNER JOIN或LEFT JOIN查询时，我们碰巧使用ON子句来定义连接条件。

例如，要获得评论及其相关的文章标题和标识符，我们可以使用下面的SQL投影查询:

SELECT
   post.post_id,
   title,
   review
FROM post
INNER JOIN post_comment ON post.post_id = post_comment.post_id
ORDER BY post.post_id, post_comment_id

然后，我们得到下面的结果集:

| post_id | title     | review    |
|---------|-----------|-----------|
| 1       | Java      | Good      |
| 1       | Java      | Excellent |
| 2       | Hibernate | Awesome   |

使用自定义投影的JOIN USING子句

当外键列与其引用的列具有相同的名称时，我们可以使用USING子句，如下例所示:

SELECT
  post_id,
  title,
  review
FROM post
INNER JOIN post_comment USING(post_id)
ORDER BY post_id, post_comment_id

并且，这个特定查询的结果集与前面使用ON子句的SQL查询相同:

| post_id | title     | review    |
|---------|-----------|-----------|
| 1       | Java      | Good      |
| 1       | Java      | Excellent |
| 2       | Hibernate | Awesome   |

USING子句适用于Oracle、PostgreSQL、MySQL和MariaDB。SQL Server不支持USING子句，因此需要使用ON子句。 USING子句可以与INNER、LEFT、RIGHT和FULL JOIN语句一起使用。

带有SELECT *的SQL JOIN ON子句

现在，如果我们改变之前的ON子句查询，使用select *选择所有列:

SELECT *
FROM post
INNER JOIN post_comment ON post.post_id = post_comment.post_id
ORDER BY post.post_id, post_comment_id

我们将得到以下结果集:

| post_id | title     | post_comment_id | review    | post_id |
|---------|-----------|-----------------|-----------|---------|
| 1       | Java      | 1               | Good      | 1       |
| 1       | Java      | 2               | Excellent | 1       |
| 2       | Hibernate | 3               | Awesome   | 2       |

可以看到，post_id是重复的，因为post和post_comment表都包含一个post_id列。

SQL JOIN USING子句与SELECT *

另一方面，如果我们运行一个SELECT *查询，其中包含JOIN条件的USING子句:

SELECT *
FROM post
INNER JOIN post_comment USING(post_id)
ORDER BY post_id, post_comment_id

我们将得到以下结果集:

| post_id | title     | post_comment_id | review    |
|---------|-----------|-----------------|-----------|
| 1       | Java      | 1               | Good      |
| 1       | Java      | 2               | Excellent |
| 2       | Hibernate | 3               | Awesome   |

您可以看到，这一次post_id列是重复数据删除的，因此结果集中只包含一个post_id列。

结论

如果数据库模式设计为外键列名与其引用的列匹配，并且JOIN条件只检查外键列值是否等于另一个表中它的镜像列的值，那么您可以使用USING子句。

否则，如果外键列名与引用列不同，或者希望包含更复杂的连接条件，则应该使用ON子句。

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

然而，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.

当我发现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)
;