在MySQL JOIN中，ON和USING()之间的区别是什么?据我所知，USING()只是更方便的语法，而ON在列名不相同时允许更大的灵活性。然而，这种差异是如此之小，您可能会认为他们只是取消了USING()。
还有什么比看上去更重要的吗?如果是，在给定的情况下我应该使用哪一种?
当前回答
对于那些在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。
其他回答
当我发现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>
数据库表
为了演示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子句。
对于那些在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子句有不同的比较参数
推荐文章
- MySQL对重复键更新在一个查询中插入多行
- mysql_connect():[2002]没有这样的文件或目录(试图通过unix:///tmp/mysql.sock连接)在
- MySQL:如何复制行，但改变几个字段?
- 不能删除或更新父行:外键约束失败
- Mysql错误1452:不能添加或更新子行:外键约束失败
- MySQL DISTINCT在GROUP_CONCAT()上
- 第一次设计数据库:我是否过度设计了?
- MySQL选择一个列DISTINCT，与其他列相对应
- 错误1022 -不能写;表中重复的键
- 如何修改列和更改默认值?
- 如何在MySQL 8.0中授予root用户所有权限
- mysqld_safe UNIX套接字文件目录“/var/run/mysqld”不存在
- 配置系统初始化失败
- 快速简单的方法迁移SQLite3到MySQL?
- 用MySQL计算中位数的简单方法
