mysql join 子查询 哪个快

JOIN 关键字在多表查询中被广泛使用，本文围绕笛卡尔积，7 种连接方式，以及 3 种连接条件进行分析，并通过一个简单的案例进行演示。

1. 连接方式和连接条件

7 种不同的连接方式可以总结为下面的韦恩图，下文会介绍实现这 7 种连接方式的 SQL 语句。

3 种连接条件对应的关键字分别为 NATURAL，USING，ON。总的来说，这三个关键字的表达能力从前往后逐渐增强，也就是后面的关键字能实现前面的关键字所能实现的功能。

这 7 种连接方式和 3 种连接条件之间可以互相组合，所以可以组合出 21 种不同的连接语句，但是只要理解了 3 种连接条件之间的不同，可以很容易地分析出，不同连接方式下，这 3 种连接条件的查询结果。所以下文仅在内连接部分对比 3 种连接条件，其余的连接方式均使用 ON 关键字实现。

2. 建表

我们用一个英雄（hero）表，和一个武器（weapon）表，来演示各种连接方式和连接条件。

hero 表有 3 个字段：英雄名（name），英雄的重量（weight）和使用的武器名（weapon_name）。weapon 表有 2 个字段：武器名（weapon_name）和武器的重量（weight）。

以下是 hero 表的建表语句，插入语句，查询语句和查询结果。

CREATE TABLE hero (
	name VARCHAR(20),
	weight int,
	weapon_name VARCHAR(30)
) DEFAULT CHARSET UTF8;

INSERT INTO hero VALUES	('张飞', 100, '丈八蛇矛'),
						('关羽', 90, '青龙偃月刀'),
						('刘备', 80, '雌雄双股剑'),
						('诸葛亮', 70, '诸葛连弩');

SELECT * FROM hero;

以下是 weapon 表的建表语句，插入语句，查询语句和查询结果。

CREATE TABLE weapon (
	weapon_name VARCHAR(30),
	weight INT	
) DEFAULT CHARSET UTF8;

INSERT INTO weapon VALUES	('青龙偃月刀', 40),
							('丈八蛇矛', 30),
							('雌雄双股剑', 20),
							('方天画戟', 30);

SELECT * FROM weapon;

这两张表就创建好了，下面我们逐个分析各种连接方式。

3. 笛卡尔积

笛卡尔积可以理解为 for 循环里嵌套了另一个 for 循环。hero 表和 weapon 表里都有 4 条记录，所以用直接使用笛卡尔积查询后的结果应该有 16 条记录。

查询语句和查询结果如下。

SELECT * FROM hero, weapon;

如果我们想在 hero 表的基础上插入一列，该列显示武器的重量，我们可以在笛卡尔积的基础上，加一条 WHERE 语句：

SELECT * FROM hero AS H, weapon AS W
WHERE H.weapon_name = W.weapon_name;

WHERE 语句的功能可以视为，对笛卡尔积的查询结果进行筛选，筛选条件是 H.weapon_name = W.weapon_name。

这里有两点需要注意：1) 查询结果中没有 ‘诸葛亮’ 。2）查询结果中出现了两列 weapon_name 完全一样。

第一点是因为 ‘诸葛亮’ 使用的武器 ‘诸葛连弩’，没有出现在 weapon 表里面，所以 WHERE 语句把笛卡尔积查询结果里的所有包含 ‘诸葛亮’ 记录都给删除掉了。如果我们想在查询结果中保留 ‘诸葛亮’ 这条记录的话，就需要用到左外连接。

第二点是使用 WHERE 语句和使用 ON 关键字的相同之处，也是他们两个和 NATURAL 、USING 关键字不一样的地方。前两者的查询结果中都会出现这样名称相同的两列（注意这里说的是名称相同，使用 ON 关键字的查询结果中这两个列的名称虽然一样，但是内容可能会有不同，下文会说明），后两者只保存一列。

4. 内连接

内连接（INNER JOIN）查询，顾名思义，只会保留 hero 表和 weapon 表中公有的记录。

ON 关键字后面指定的查询条件与上文的 WHERE 语句一样，但是二者的作用范围是有所不同的，在左外连接部分会做出解释。

下是使用 ON 关键字的内连接语句，及其查询结果。

SELECT *
FROM hero INNER JOIN weapon ON hero.weapon_name = weapon.weapon_name;

（其中的 INNER 关键字可省略。）

可以看到，这里的查询结果与使用 笛卡尔积 + WHERE 语句 的查询结果是一样的。

这里如果用 USING 语句代替 ON 语句的话可以写为：

SELECT *
FROM hero INNER JOIN weapon USING (weapon_name);

可以看到，这里的查询结果与使用 ON 关键字的查询结果基本一致，只不过只保留了一列 weapon_name。

ON 的表达能力比 USING 强，是因为 ON 后面可以是两个表中列名不一样的列，比如 hero.name = weapon.weapon_name，当然这个查询会返回空集，但是这条语句是没有问题的。但是 USING 后面只能是两个表中列名一样的列。注意到两个表中 weapon_name 和 weight 列都是重名的，USING 就可以指定两个中的一个，或者两个都指定，作为连接条件。

而 NATURAL 关键字会自动识别两个表中列名一样的列，然后将这些列的值都一样作为连接条件。

NATURAL 语句及其查询结果为：

SELECT * 
FROM hero NATURAL JOIN weapon;

返回空集是因为两个表中没有 weight 和 weapon_name 都一样的记录。

如果用 USING 和 ON 关键字替换 NATURAL 可以写成：

SELECT * 
FROM hero JOIN weapon USING(weight, weapon_name);

和

SELECT * 
FROM hero JOIN weapon ON hero.weight = weapon.weight AND hero.weapon_name = weapon.weapon_name;

三者实现的功能是等价的，区别只是使用 ON 会保留所有列。

如果把刘皇叔的体重改成 20 的话，三个查询语句的查询结果如下。

UPDATE hero SET weight = 20 WHERE name = '刘备';

以上就是内连接查询的实现方式，以及 3 种查询条件之间的对比。

5. 三种外连接

左外连接右外连接和全外连接的韦恩图如下。相对于内连接来讲，左外连接保留了 JOIN 关键字左边的表中的所有记录，右外连接则保留了 JOIN 右边表中的所有记录，全外连接保留了两个表的所有记录。

左外连接的 SQL 语句和查询结果如下。

SELECT * 
FROM hero LEFT OUTER JOIN weapon ON hero.weapon_name = weapon.weapon_name;

（OUTER 关键字可以省略）

这里我们就可以对比一下左外连接中的 ON 和笛卡尔积查询中的 WHERE 的区别了。ON 指定的是连接条件，它的作用仅限于参与连接的那些记录，也就是出现在内连接结果集中的那些记录，所以即使 ‘诸葛亮’ 那条记录不满足 ON 后面的条件，这条记录也会出现在结果集中。而使用 WHERE 的查询语句，结果集中的所有记录，都必须满足 WHERE 后面的条件。

右外连接的 SQL 语句和查询结果如下。

SELECT * 
FROM hero RIGHT JOIN weapon ON hero.weapon_name = weapon.weapon_name;

全外连接的 SQL 语句和查询结果如下。

SELECT * 
FROM hero FULL JOIN weapon ON hero.weapon_name = weapon.weapon_name;

结果是没有结果。。

我也不太清楚为什么会有这样一个错误。但是条条大路通罗马，我们可以把左外连接和右外连接的结果进行 UNION 操作，来得到全外连接。

(SELECT * 
FROM hero RIGHT JOIN weapon ON hero.weapon_name = weapon.weapon_name)
UNION
(SELECT * 
FROM hero LEFT JOIN weapon ON hero.weapon_name = weapon.weapon_name);

6. 三种叫不出名字的连接

三种连接方式如下图所示，第一种的意思是找出 hero 表中 weapon_name 没在 weapon 表中出现过的记录，第二种和第三种应该也很好理解，但是表达成文字就很绕了。这三种情况可以在左外连接，右外连接，或者全外连接的基础上，加一条 WHERE 语句实现。

第一种连接的 SQL 语句和查询结果如下：

SELECT * 
FROM hero LEFT JOIN weapon ON hero.weapon_name = weapon.weapon_name
WHERE weapon.weapon_name IS NULL;

如果这条语句作为一个整体无法执行的话，试试这样子输入它们。

第二种如下。

SELECT *
FROM hero RIGHT JOIN weapon ON hero.weapon_name = weapon.weapon_name
WHERE hero.weapon_name IS NULL;

第二种连接的实现方式同理。

第三种因为全外连接无法实现，下面这条语句想必也无法执行，如果哪位读者知道为什么，欢迎留言，我用的是 MySQL 8.0 版本。

SELECT *
FROM hero FULL JOIN weapon ON hero.weapon_name = weapon.weapon_name
WHERE hero.weapon_name IS NULL OR weapon.weapon_name IS NULL;

前两种连接也可以用子查询实现。以第一种连接为例：

SELECT * 
FROM hero 
WHERE weapon_name NOT IN (SELECT weapon_name FROM weapon);

总结：JOIN 关键字在多表查询中被广泛使用，本文通过一个简单的案例，演示了笛卡尔积、7 种连接法方式和 3 种查询条件，其中 2 种连接方式无法执行，可以改用 UNION 实现。