MySQL JOIN ON和USING的区别？

Question

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

除了显而易见的差异之外，是否还有更多需要注意的地方？如果有的话，在特定情况下应该使用哪一个？

- Nathanael

---

还有自然连接：https://dev59.com/eGoy5IYBdhLWcg3wN7e8 - allyourcode

请注意，using 除了用于连接之外还有其他用途。请参阅 https://dev59.com/o2Yr5IYBdhLWcg3wW45t#13750399。 - Pacerier

这回答了你的问题吗？MySQL表连接中“using”和“on”的区别是什么？(https://dev59.com/VXRC5IYBdhLWcg3wOeWB) - philipxy

Answer 1

这大部分是语法糖，但有几个差异值得注意：

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.film_id限定。如果只写film_id，那么就会产生歧义：

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

至于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>

- Shlomi Noach

---

关于语法差异的回答很好。我对性能差异很感兴趣，如果有的话。我想 USING 与 ON 的解释是相同的。 - Jason McCreary

实际上，两者都被解释为普通的Theta风格。您可以通过在查询中调用EXPLAIN EXTENDED，然后跟随SHOW WARNINGS来查看。 - Shlomi Noach

你也可以使用 USING(category,field_id) 来连接由复合主键组成的表，这在联接时非常有用。此外，我听说优化器有时会使用 USING 来提高性能。 - Timo Huovinen

“USING” 是 MySQL 的定义还是标准用法？ - PhoneixS

@PhoneixS 这是在 ANSI SQL 92 标准 中规定的。 - Shlomi Noach

Answer 2

维基百科提供了以下关于USING的信息：

然而，USING结构不仅仅是语法糖，因为结果集与具有显式谓词的版本的结果集不同。特别是，在USING列表中提到的任何列将仅出现一次，使用未经限定的名称，而不是在连接中的每个表中各出现一次。在上面的情况下，将有一个单独的DepartmentID列，没有employee.DepartmentID或department.DepartmentID。

它所涉及的表：

Postgres 文档也很好地定义了它们：

ON子句是最通用的连接条件：它接受与WHERE子句相同类型的布尔值表达式。如果T1和T2中的一对行匹配，则表示ON表达式计算结果为true。 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的任何剩余列。

- Robert

Answer 3

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

ON 允许限制查询外部连接的表的结果集，同时保持外部连接。通过指定 WHERE 子句来限制结果集将有效地将外部连接更改为内部连接。

尽管这可能是相对较少见的情况，但值得一提......

例如：

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

- Tom Mac

---

非常好的观点。在所有using提供的优势中，它不能与其他谓词结合使用：select*from t join t2 using(i) and on 1会无法工作。 - Pacerier

where hasAirport; - 这是什么意思？没有值可供比较。 - Istiaque Ahmed

另外请注意，您可以使用 ON 比 = 做更多比较。例如 SELECT * FROM country LEFT JOIN city ON country.countryId=city.countryId AND city.city BETWEEN 'C' AND 'E' 将列出所有国家以及以 C 或 D 开头的城市（如果有的话）。 （还有名为 'E' 的城镇） - Roemer

我曾经甚至在ON中使用子查询进行JOIN!!! 这一切都是可能的，有时非常有效。 - Roemer

Answer 4

数据库表

为了演示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语句一起使用。

现在，如果我们将前面的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和post_comment表都包含一个post_id列，因此post_id被重复了。

使用SELECT *的SQL JOIN USING子句

另一方面，如果我们运行一个带有USING子句的SELECT *查询作为JOIN条件：

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子句。 否则，如果外键列名与引用列不同或者您想要包含更复杂的JOIN条件，则应改用ON子句。

- Vlad Mihalcea

---

这些查询并不相同。它们存在微妙的语法差异。例如，请参考被接受的答案。 - Lukas Eder

@LukasEder 表示，“查询结果集将是相同的”。（在标准 SQL 中，ON 和 USING 的 JOIN 计算不同的表。MySQL 允许通过点操作提及 USING 列，这在标准 SQL 中是不允许的。但 SELECT * 仅返回每个 USING 列的一个副本。因此，再次强调，ON 和 USING 的 JOIN 计算不同的表。） - philipxy

感谢您提到在SQL Server中USING不可用。 - Brad Turek

Answer 5

对于那些在phpMyAdmin中进行实验的人，需要注意：

phpMyAdmin似乎存在一些USING的问题。记录一下，这是在Linux Mint上运行的phpMyAdmin版本：“4.5.4.1deb2ubuntu2”，数据库服务器为“10.2.14-MariaDB-10.2.14+maria~xenial - mariadb.org二进制发行版”。我已经在phpMyAdmin和终端（命令行）中使用JOIN和USING命令，但phpMyAdmin中的结果有些令人困惑：

1）末尾的LIMIT子句似乎被忽略了。
2）页面顶部显示的结果数量有时是错误的：例如返回了4行，但顶部却显示“显示0-24行（共2503行，查询耗时0.0018秒）”。

正常登录mysql并运行相同的查询不会产生这些错误。在phpMyAdmin中使用JOIN ... ON ...运行相同的查询也不会出现这些错误。可能是phpMyAdmin的一个bug。

- mike rodent

Answer 6

简短回答：

• 使用 USING：当子句含义不明确时
• 使用 ON：当子句具有不同的比较参数时

- Chris Baaijens