简短的回答:

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

简短的回答:

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>

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