MySQL(四)：联接与集合操作

最新推荐文章于 2024-05-23 01:56:16 发布

GDUT_Ember

最新推荐文章于 2024-05-23 01:56:16 发布

阅读量193

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/GDUT_Trim/article/details/116074395

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

联接查询

联接查询是一种常见的数据库操作，即在两张表中进行匹配的操作，一般称之为水平操作，这是因为对几张表进行联接操作所产生的结果集可以包含这几张表中所有的列（可以看成是两张表，水平拼在一起，但其实本质上是进行笛卡尔积然后再过滤，然后再进行不同联结的处理），对应于联接（JOIN）的水平操作，一般将集合操作（UNION）视为垂直操作（可以理解成两张表竖着排）。

Mysql数据库支持如下的联接查询

CROSS JOIN (交叉联接)
INNER JOIN (内联接)
OUTER JOIN(外联接)
其他

每个联接都只发生在两个表之间，即使FROM子句中包含多个表也是如此的，每次联接操作也只进行逻辑操作的三个步骤（产生笛卡尔积的虚拟表，根据ON过滤器进行过滤，将保留表的数据插入虚拟表形成新的虚拟表），即每次联接产生一个虚拟表，如果FROM后面还有表的话，再用当前虚拟表与其联结，知道FROM子句中的表全部都被处理完为止。

但需要注意的是，不同联接类型执行的步骤不同，对于CROSS JOIN，只应用第一个阶段的笛卡尔积（单纯进行笛卡尔积），INNER JOIN应用第一和第二个步骤（进行笛卡尔积之后进行去重，去重的是ON过滤器进行过滤），OUTER JOIN应用所有的前三个步骤（进行笛卡尔积然后进行去重，去重的是ON过滤器进行过滤，然后进行保留表数据的插入）。

新旧查询语法

MySQL数据库支持两种不同的内联接操作语法。

//新，称为（ANSI 89语法）
SELECT ... FROM a,b WHERE a.x = b.x;
//旧 ANSI 92
SELECT ... FROM a INNER JOIN b ON a.x = b.x

CROSS JOIN

CROSS JOIN是对两个表执行笛卡尔积，返回两个表中所有列的组合，若左表有m行数据，右表有n行数据，则CROSS JOIN将返回m*n行的表（笛卡尔积）

交叉联接也有两种不同的SQL语法，结果也是一样的

SELECT ... FROM t1 CROSS JOIN t2;
SELECT ... FROM t1,t2; //不进行过滤的内联接

注意

如果是同一张表进行交叉联接，是要使用别名的，否则会报错
在这里插入图片描述
举个栗子

//会报错
SELECT * FROM t3,t3;
SELECT * FROM t3 CROSS JOIN t3;
//正确写法
SELECT * FROM t3 as a,t3 as b;
SELECT * FROM t3 as a CROSS JOIN t3 as b;

INNER JOIN

通过INNER JOIN用户可以根据一些过滤条件来匹配表之间的数据，在逻辑查询的三个步骤中前两个（形成笛卡尔积表，ON过滤器过滤数据），INNER JOIN没有第三步操作，也就是不会添加保留表的外部行，这是和OUTER JOIN最大的区别之一，也正因为不会添加外部行，所以过滤条件用在ON或者WHERE子句中产生的结果是没有区别的。

//找出部门为d001的经理的用户编号、姓名
//INNER是可以省略的
SELECT a.emp_no,first_name.last_name FROM employees a
[INNER] JOIN dept_manager b ON a.emp_no = b.emp_no
WHERE dept_no = 'd001';
//将WHERE的过滤交给ON实现也可以
SELECT a.emp_no,first_name,last_name FROM employees a
[INNER] JOIN dept_manager b ON a.emp_no = b.emp_no
AND dept_no = 'd001';
//另一种版本（ANSI 89）
//使用ANSI 89的话，只能使用WHERE进行过滤，如果忘记给条件的话，是很危险的，可能会返回大面积的笛卡尔积
SELECT a.emp_no,first_name,last_name FROM employees a,dept_manager b
WHERE a.emp_no = b.emp_no AND dept_no = 'd001';

需要注意的是，在MySQL中使用INNER JOIN是可以没有ON的，所以同样没有给过滤条件的话，也是会返回大面积的笛卡尔积，在MySQL数据库中，CROSS JOIN其实和INNER JOIN是同义词的关系，但INNER JOIN没有ON子句时，SQL解析器会将INNER JOIN理解为CROSS JOIN。

此外，如果ON子句中的列具有相同的名称，可以使用USING子句来进行简化

举个例子

SELECT a.emp_no.first_name,last_name 
FROM employees a
INNER JOIN dept_manager b
USING(emp_no)  //效果跟ON a.emp_no = b.emp_no一样
WHERE dept_no = 'd001';

OUTER JOIN

通过OUTER JOIN用户可以按照一些过滤条件来匹配表之间的数据，与INNER JOIN不同的是，在通过OUTER JOIN添加的保留表中存在未找到的匹配数据（即OUTER JOIN会执行逻辑查询步骤三，就是会将保留表中未匹配的外部行也添加到表中），MySQL数据库支持LEFT OUTER JOIN和RIGHT OUTER JOIN，与INNER关键字一样，可以省略OUTER关键字。

目前MySQL并不支持FULL OUTER JOIN

但可以通过LEFT JOIN然后UNION RIGHT JOIN操作即可。

OUTER JOIN应用逻辑查询的前三个步骤，即产生笛卡尔积、应用ON过滤器和添加外部行，对于保留表中的行数据，如果是未找到匹配数据而添加的记录，那么未匹配的数据会用NULL来替代（也就是另一张非保留表中的数据会为NULL）。

注意

与INNER JOIN的不同

INNER JOIN所有过滤条件都可以写在ON中，但OUTER JOIN却不可以这样，会得到不正确的结果，因为处理完ON之后是会进行添加外部行的，添加完外部行才执行WHERE（如果把WHERE的过滤写在ON子句中，那么外部行将不会被进行过滤），不像INNER JOIN，不需要添加外部行。
与INNER JOIN还有一处不同，OUTER JOIN是必须规定ON子句的，INNER JOIN如果没有ON子句，那么就会变成CROSS JOIN，OUTER JOIN不会变成CROSS JOIN

NATURAL JOIN

MySQL还支持NATURAL JOIN（自然联接），NATURAL JOIN等同于INNER JOIN与USING的组合，它隐含的作用是将两个表中具有相同名称的列进行匹配，同样的，NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) OUTER JOIN与USING的组合。

举个栗子

//一般内联接
SELECT a.emp_no,first_name,last_name
FROM employees a
INNER JOIN dept_manager b
ON a.emp_no = b.emp_no;

//使用USING省略
SELECT a.emp_no,first_name,last_name
FROM employees a
INNER JOIN dept_manager b
USING(emp_no);

//使用NATURAL JOIN替代
SELECT a.emp_no,first_name,last_name
FROM employees a
NATURAL JOIN dept_manager b;

STRAIGHT_JOIN

STRAIGHT_JOIN其实并不是新的联接类型，而是用户对SQL优化器的控制，等同于JOIN。通过STRAIGHT_JOIN，MySQL数据库会强制先读取左边的表，我们可以对比STRAIGHT_JOIN的SQL语句的执行计划，其SQL语句如下

SELECT a.emp_no,first_name,last_name
FROM employees a
INNER JOIN dept_manager b
ON a.emp_no = b.emp_no;

这条语句的执行过程是会先选择b表（右表），也就是dept_manager表，进行读取，然后进行匹配，如果使用STRAIGHT_JOIN会强制使用左表进行读取再匹配。

选择不同的表匹配有什么不同，这里假如b表24行，而a表有几万行，如果使用b表进行匹配，那么只需要匹配24次即可，但如果使用a表进行匹配，则需要进行几万次匹配，因为匹配此时主根据优先读取的表。

其他联接分类

SELF JOIN

SELF JOIN是同一个表的两个实例之间的JOIN操作，只是没有显示的归类而已，凡是跟同一个表的联接就是SELEF JOIN。

NONEQUI JOIN

前面的介绍的联接都是EQUAL JOIN（等值联接），即联接条件是基于"等于"运算符的联接操作，NONEQUI JOIN的联接条件是除了“等于”运算符之外的其他所有运算符。

SELECT a.emp_no,b.emp_no FROM dept_manager a
INNER JOIN dept_manager b
ON a.emp_no < b.emp_no;

SEMI JOIN 和ANTI SEMI JOIN

SEMI JOIN是根据一个表中存在的相关记录找到另一个表中相关数据的联接。如果从左表返回记录，该联接称为左半联接；如果从右表返回记录，该联接被称为右半联接。

SELECT DISTINCT c.customer_id,city
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id //使用c表存在的customer_id来找，普通JOIN,优先右表
WHERE c.city = 'HangZhou';

与SEMI JOIN相反的是ANTI SEMI JOIN，它是根据一个表中不存在的记录而从另一个表中返回记录，比如用OUTER JOIN并用WHERE过滤外部行。

SELECT c.customer_id,c.city
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.customer_id = o.customer_id
WHERE c.city = 'HangZhou' AND o.customer_id IS NULL; //根据o.customer_id不存在的记录返回

联接算法

联接算法是MySQL数据库用于处理联接的物理策略。最为通用的是Nested-Loops Join算法

但联接的表上有索引时，Nested-Loops Join是非常高效的算法，根据B+树的特性，其联接的时间复杂度为 $O (N)$ ,若没有索引，可以视为最坏的情况，时间复杂度为 $O(N^2)$ ，MySQL数据库根据不同的使用场合，支持两种的Nested-Loops Join算法，一种是Simple Nested-Loops Join算法，另一种是Block Nested-Loops Join算法。

Simple Nested-Loops Join算法

Simple Nested-Loops Join从第一张表中每次读取一条记录，然后将记录与嵌套表中的所有记录进行比较，其算法如下。

For each row r in R do //遍历第一张表
	For each row s in S do //遍历第二张表
		IF<满足联接条件>
		Then output the tuple <r,a>
	Next s
Next r

R为外部表，S为内部表，现在假设两张表进行联接的索引都不含有索引，算法扫描的次数为 $R_n*S_n$ ， $R_n和S_n$ 分别代表R表和S表的记录数，所以时间复杂度为 $O(R_n*S_n)$

如果是多张表，比如三张，那么就是三个嵌套For循环了。

但是如果当内部表所联接的列中包含索引时，Simple Nested-Loops Join算法可以利用索引的特性来进行快速匹配，此时的算法会进行下列调整。

For each row r in R do //遍历外部表
	lookup r in S index //在内部表的索引中找是否存在r来匹配
		If found S == r //如果找到
			Then output the tuple <r,a> //输出
Next r

对于联接的列含有索引的情况，外部表的每条记录不再需要扫描整张内部表，只需要扫描内部表的索引即可得到联接的判断结果，假设内部表联接列的索引高度为 $S_{BH}$ ，那么执行的复杂度为 $S_{BH}*R_n$ ，时间复杂度为 $O(S_{BH}*R_n)$ ，而索引是由B+树来实现的，而B+树的高度大概为3~4层，因此在有索引的情况下，Simple Nested-Loops Join算法的执行速度是比较快的。

在INNER JOIN中，两张联接表的顺序是可以变换的，即下面的两条SQL是一样的

R INNER JOIN S ON <Condition>
S INNER JOIN R ON <Condition>

优化器一般情况下总是选择联接列内含有索引的表来作内部表（不可以做外部表，外部表的话，就要遍历内部表看是否在索引中了，不符合逻辑），如果两张表的联接列都有索引，那么优化器会选择将记录数最少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关，所以选记录数少的表可以减少执行次数（其实在这里索引的高度也是有影响的，只不过索引的高度影响比不上记录数量的影响）。

Block Nested-Loops Join算法

上面说过Simple Nested-Loops Join对于没有索引的情况是比较不理想的，而Block Nested-Loops Join就是解决这个问题的，简单来说就是针对没有索引的联接情况设计的，其使用Join Buffer(联接缓冲，这里就先理解他是一个缓冲区吧)来减少内部循环读取表的次数。

例如，Block Nested-Loops Join算法先把对Outer Loop表（外部表）每次读取的10行记录（准确地说是10行需要进行联接的列）放入Join Buffer中，然后在Inner Loop表（内部表）中直接匹配这10行数据，因此，对Inner Loop表的扫描会减少十分之一，对于没有索引的表来说，Block Nested-Loops Join算法可以极大地提高联接的速度。

集合操作

通常来说，将联接操作看成是表之间的水平操作，因为该操作生成的虚拟表包含两个表中的列（相当于两个表水平拼在一起），而集合操作，一般将其视为垂直操作，MySQL数据库支持两种集合操作，一种是UNION ALL，另一种是UNION DISTINCT。

与联接操作一样，集合操作也是对两个输入进行操作，并且生成一个虚拟表，在联接操作中，一般把输入表称为左输入和右输入，或者第一个输入和第二个输入，集合操作的两个输入（即进行集合操作的两个表）必须拥有相同的列数，若数据类型不同，MySQL数据库会自动将进行隐式转化，同时，结果列的名称由第一个输入（也就是左表的列来决定）。

CREATE TABLE `x`(
	a CHAR(1)
)ENGINE=INNODB,CHARSET=utf8;
INSERT INTO X VALUES('a'),('c');
SELECT a AS m FROM X 
UNION SELECT 1 AS n FROM DUAL UNION SELECT 'abc' AS o FROM DUAL UNION SELECT NOW() AS p FROM DUAL;

在这里插入图片描述
关于隐式转化

MySQL数据库会自动对其进行判断，并选出一种类型进行隐式转换。另一方面，在上面栗子中对每个选取操作都进行了对列名取别名，但最后的列名采用的是最左表的列别名。

集合操作中SELECT语句和一般的SELECT语句差不多，只不过有以下的两点区别

只有最后一个SELECT可以应用INTO OUTFILE，但是整个集合的操作将被输出到文件中（虽然INTO OUTFILE虽然在最后与一个SELECT中，但导出的结果是整个集合操作的结果）。
不能在SELECT语句中使用HIGH_PRIORITY关键字

还有一点要注意的是，若SELECT语句中使用了包含LIMIT和ORDER BY子句的，且集合操作是关于多条SELECT语句的（一般SELECT中的子查询都是多个SELECT的，不可以直接 t1 UNION t2，这是不正确的语法），最好的做法是将参与集合操作的各SELECT语句添加括号，否则执行集合查询会得到错误提示。

# 错误示范
## LIMIT来添加括号
SELECT a FROM x ORDER BY a LIMIT 1 UNION SELECT a FROM y;
## 只对第一个数据添加括号
(SELECT a FROM x ORDER BY a LIMIT 1) UNION SELECT a FROM y;
# 正确写法
## 两边都加括号
(SELECT a FROM x ORDER BY a LIMIT 1) UNION (SELECT a FROM y);

UNION DISTINCT和UNION ALL

UNION DISTINCT是组合两个输入，并且应用DISTINCT过滤重复项，一般省略DISTINCT关键字，直接用UNION

举个例子

CREATE TABLE t7(
	a CHAR(1)
)ENGINE=INNODB,CHARSET=utf8;

CREATE TABLE t8(
	a CHAR(1)
)ENGINE=INNODB,CHARSET=utf8;

INSERT INTO t7 SELECT 'a';
INSERT INTO t7 SELECT 'b';
INSERT INTO t7 SELECT 'c';

INSERT INTO t8 SELECT 'd';
INSERT INTO t8 SELECT 'e';
INSERT INTO t8 SELECT 'a';
INSERT INTO t8 SELECT 'b';

<两条SQL执行起来是一样的>
SELECT a AS m FROM t7 UNION DISTINCT SELECT a AS b FROM t8;
SELECT a AS m FROM t7 UNION SELECT a AS b FROM t8;

在这里插入图片描述
UNION DISTINCT(UNION)的过程

创建一张临时表，即虚拟表
对这张临时表的列添加唯一索引进行去重（Unique Index）
将输入的数据插入临时表
返回虚拟表

因为添加了唯一索引，所以可以过滤掉集合中重复的项。

向临时表中添加了唯一索引，所以会影响插入速度（虽然一般不会去对临时表进行插入），所以如果确认了两张表是没有重复数据的，使用UNION ALL

UNION ALL组合两个输入中所有项的结果集，并包含重复的选项

SELECT a AS m FROM t7 UNION ALL SELECT a AS b FROM t8;

在这里插入图片描述
UNION DISTINCT可以使用UNION ALL然后加DISTINCT对列进行去重来代替吗？答案是不可的，因为执行的顺序不一样，集合操作可以理解成，是先将两个表生成的临时表进行合并的

举个栗子

SELECT DISTINCT a AS m FROM t7 UNION ALL SELECT a AS b FROM t8;

在这里插入图片描述
可以很清楚的看到并没有进行去重，这是因为执行顺序是这样的，先执行第一句SELECT，然后执行第二句SELECT，然后进行UNION ALL操作，也就是DISTINCT并不是对最后产生的临时表进行去重，而只是对所在的SELECT进行去重，所以要进行去重还是要用UNION DISTINCT

EXCEPT

EXCEPT集合操作允许用户找出位于第一个输入中但不位于第二个输入中的行数据，跟UNION一样，EXCEPT可以分为EXCEPT DISTINCT和EXCEPT ALL

EXCEPT DISTINCT返回位于第一个输入中但不位于第二个输入中的不重复行，对于执行这种需求常用的是LEFT JOIN或者NOT EXCEPT

//使用left join联接两张表，联接条件为两者输入中都有的数据、
//因为左表是保留表，所以右表为NULL的话，代表左表有，而右表没有
SELECT t7.a FROM t7 LEFT JOIN t8 ON t7.a = t8.`a` WHERE t8.`a` IS NULL;

//这里使用了关联子查询，首先关联子查询找到t8和t7表共有的数据
//然后使用EXISTS来从t7表中过滤关联子查询查出的共有数据
SELECT t7.a FROM t7 WHERE NOT EXISTS(SELECT t8.a FROM t8 WHERE t7.a = t8.a);

在这里插入图片描述
上面两种方式都可以查询成功，而且结果也一致。看似没有问题，但如果输入项中包含NULL值，情况就不是这么简单的了

举个栗子

这是t9表

在这里插入图片描述
这是t10表

那么如果进行上述的去重，结果应该是得到(‘e’,‘f’)，因为这个数据存在于t9表中，但不在t10表中

现在进行执行SQL

//使用左联结找到两表共同数据
//但由于左表是保留表，那么右表为NULL的话，代表左表有而右表无
SELECT a.`a`,a.`b` FROM t9 AS a LEFT JOIN t10 AS b ON a.`a` = b.`a` AND a.`b` = b.`b` 
WHERE b.`a` IS NULL AND b.`b` IS NULL;
SELECT a.`a`,a.`b`  FROM t9 AS a WHERE NOT EXISTS(SELECT b.`a`,b.`b` FROM t10 AS b WHERE b.`b` = a.`b` AND b.`a` = a.`a` );

在这里插入图片描述
结果显然不符合我们的预期

这是因为，在对NULL进行比较的时候，返回的是NULL，即UNKNOWN。

那么怎么才能实现想要的功能呢

首先将两张表进行不排除重复进行合并（但两张表是要进行去重的，并且加一个标识列来表明是哪张表），然后进行分组，如果某一个分组的数据数量大于1，那么就肯定是两边都有的，如果为1，那就可能是左表有右表无，或者右表有左表无，此时再根据标识列是哪张表就可以了。

SELECT * FROM (
	SELECT DISTINCT 't9' AS source,a,b FROM t9 
	UNION 
	SELECT DISTINCT 't10' AS source,a,b FROM t10) AS t
	GROUP BY a,b 
	HAVING COUNT(*) = 1 AND t.source = 't9';

GDUT_Ember

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL(四)：联接与集合操作

联接查询新旧查询语法CROSS JOININNER JOINOUTER JOINNATURAL JOINSTRAIGHT_JOIN其他联接分类SELF JOINNONEQUI JOINSEMI JOIN 和ANTI SEMI JOIN联接算法Simple Nested-Loops Join算法Block Nested-Loops Join算法集合操作UNION DISTINCT和UNION ALLEXCEPT联接查询联接查询是一种常见的数据库操作，即在两张表中进行匹配的操作，一般称之为水平操作，这是因.
复制链接

扫一扫

专栏目录