MySQL加入vs使用?

在一个MySQL JOIN中，ON和USING()之间的区别是什么?据我所知，USING()只是更方便的语法，而ON在列名不相同时允许更多的灵活性。然而，这个差异是如此之小，你会认为他们只是取消USING()。

还有什么比看上去更重要的吗?如果是，在给定的情况下我应该使用哪一种?

251327 次浏览
小开
最佳答案

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

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

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

使用是有用的，当两个表共享一个完全相同的名称的列，他们连接。在这种情况下，人们可以说:

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 ...

为了说明，要用在完成上述操作，我们必须这样写:

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是无效的，因为这会导致歧义:

错误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>
小开

当我发现ON比USING更有用时，我想我会在这里插话。当OUTER连接被引入查询时。

在维护OUTER连接的同时，允许限制查询要连接到的表的结果集，从而使ON受益。试图通过指定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)
;
小开

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

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

它所谈论的表格:

enter image description here

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

ON子句是最常用的一种连接条件:它需要a 与WHERE中使用的相同类型的布尔值表达式 条款。如果是ON表达式，则T1和T2中的一对行匹配

USING子句是一种简写，允许您利用 连接双方使用相同名称的特定情况 对于连接列。对象的逗号分隔列表 共享列名并形成连接条件，其中包含 每一个的相等比较。例如，连接T1和T2 USING (a, b)产生连接条件ON T1。a = T2。a和T1。b = T2.b . < / p > 此外，JOIN USING的输出抑制了冗余列: 没有必要打印两个匹配的列，因为它们必须打印 有相等的值。而JOIN ON则生成T1后面的所有列 JOIN USING会为T2中的所有列生成一个输出列 所列列对(按所列顺序)的

.

.
小开

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

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

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

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

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

小开

数据库表

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

SQL USING vs ON子句表关系

父表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子句。

小开

简短的回答:

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

提交答案