SQL操作指南八（集合运算）

作者: 了不起的树懒 | 来源:发表于2018-11-11 17:22 被阅读0次

SQL操作指南八（集合运算）
sql 集合运算
SQL——集合运算
T-SQL基础（四）之集合运算
sql server集合运算
第八章：集合运算
Oracle PL/SQL (6) - 集合运算符、FORALL
第三章 SQL语言元素（二）
掌握关系代数运算
用 SQL 进行集合运算

表的加减法

表的加法——UNION
首先创建一个名称为shop_list2的表便于代码演示，如下：

+---------+-----------+-----------+------------+-----------+---------------+
| shop_id | shop_name | shop_type | sell_price | buy_price | register_date |
+---------+-----------+-----------+------------+-----------+---------------+
| 0001    | T恤衫     | 衣服      |       1000 |       500 | 2008-09-20    |
| 0002    | 打孔器    | 工具      |        500 |       320 | 2008-09-11    |
| 0003    | 运动T恤   | 衣服      |       4000 |      2800 | NULL          |
| 0009    | 手套      | 衣服      |        800 |       500 | NULL          |
| 0010    | 水壶      | 厨房用具  |       2000 |      1700 | 2009-09-20    |
+---------+-----------+-----------+------------+-----------+---------------+

接下来，将两张表相加：

SELECT shop_id,shop_name
FROM shop_list
UNION
SELECT shop_id,shop_name
FROM shop_list2;
+---------+-----------+
| shop_id | shop_name |
+---------+-----------+
| 0001    | T恤衫     |
| 0002    | 打孔器    |
| 0003    | 运动T恤   |
| 0004    | 菜刀      |
| 0005    | 砧板      |
| 0006    | 扳手      |
| 0009    | 手套      |
| 0010    | 水壶      |
+---------+-----------+

由表可以看出UNION集合就是并集，该集合运算符会除去重复的记录。

集合运算符的注意事项
1.作为运算对象的记录列数必须相同；
当两张表的列数不相同时，UNION是不能将两张表相加的。
2.作为运算对象的记录中列的类型必须一致；
不同类型的数据肯定不能存放在同一列的，如果非要相加的话，可以尝试类型转换。
3.可以使用任何SELECT 语句，但ORDER BY子句只能在最后使用一次；
示例：

SELECT shop_id,shop_name
FROM shop_list
WHERE shop_type = '厨房用具'
UNION
SELECT shop_id,shop_name
FROM shop_list2
WHERE shop_type='厨房用具'
ORDER BY shop_id;
+---------+-----------+
| shop_id | shop_name |
+---------+-----------+
| 0004    | 菜刀      |
| 0005    | 砧板      |
| 0010    | 水壶      |
+---------+-----------+

包含重复行的集合运算——ALL选项
UNION的结果没有包含重复行，那么只需要在UNION后面加上ALL关键字的结果就是可以显示所有行，包括重复行。

SELECT shop_id,shop_name
FROM shop_list
UNION ALL
SELECT shop_id,shop_name
FROM shop_list2;
+---------+-----------+
| shop_id | shop_name |
+---------+-----------+
| 0001    | T恤衫     |
| 0002    | 打孔器    |
| 0003    | 运动T恤   |
| 0004    | 菜刀      |
| 0005    | 砧板      |
| 0006    | 扳手      |
| 0001    | T恤衫     |
| 0002    | 打孔器    |
| 0003    | 运动T恤   |
| 0009    | 手套      |
| 0010    | 水壶      |
+---------+-----------+

选取表中公共部分——INTERSECT
INTERSECT即为获取两个表的交集。

SELECT shop_id,shop_name
FROM shop_list
INTERSECT
SELECT shop_id,shop_name
FROM shop_list2
ORDER BY shop_id;

MySQL暂时不支持INTERSECT用法。

表的减法——EXCEPT
EXCEPT即为表1减取与表2中重复的部分剩下的记录，用法与UNION相同。

SELECT shop_id,shop_name
FROM shop_list
EXCEPT
SELECT shop_id,shop_name
FROM shop_list2
ORDER BY shop_id;

Oracle不适用EXCEPT，其拥有特有的MINUS代替EXCEPT，MySQL暂时不支持EXCEPT这种用法。

联结（以列为单位对表进行联结）

前面学习的集合运算都是造成了行的变化，而联结的作用就是进行列的变化。

内联结——INNER JOIN
演示代码将会用到两张表：

+---------+-----------+-----------+------------+-----------+---------------+
| shop_id | shop_name | shop_type | sell_price | buy_price | register_date |
+---------+-----------+-----------+------------+-----------+---------------+
| 0001    | T恤衫     | 衣服      |       2000 |       500 | 2009-09-20    |
| 0002    | 打孔器    | 工具      |        600 |       100 | 2009-09-20    |
| 0003    | 运动T恤   | 衣服      |       3000 |      2800 | 2009-09-20    |
| 0004    | 菜刀      | 厨房用具  |       6000 |      2800 | 2009-09-20    |
| 0005    | 砧板      | 厨房用具  |       2000 |      1299 | 2009-09-20    |
| 0006    | 扳手      | 工具      |       3000 |      1500 | 2009-09-20    |
+---------+-----------+-----------+------------+-----------+---------------+

+---------+-----------+---------+--------+
| area_id | area_name | shop_id | number |
+---------+-----------+---------+--------+
| 000A    | 北京      | 0001    |     30 |
| 000A    | 北京      | 0002    |     50 |
| 000A    | 北京      | 0003    |     15 |
| 000B    | 上海      | 0002    |     30 |
| 000B    | 上海      | 0003    |    120 |
| 000B    | 上海      | 0004    |     20 |
| 000B    | 上海      | 0006    |     10 |
| 000B    | 上海      | 0007    |     40 |
| 000C    | 重庆      | 0003    |     20 |
| 000C    | 重庆      | 0004    |     50 |
| 000C    | 重庆      | 0006    |     90 |
| 000C    | 重庆      | 0007    |     70 |
| 000D    | 杭州      | 0001    |    100 |
+---------+-----------+---------+--------+

如上表，两张表的列可以分为如下两类：
1.两张表中有包含相同的列→shop_id；
2.只存在与一张表内的列→除shop_id以外的列；
所谓联结，就是"以1中的的列作为桥梁，将2中的满足其他条件的列汇集到同一结果中"。
我们可以试着从shop_list中取出shop_name和sell_price与shop_area中的内容进行结合。

SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price
FROM shop_area AS SA JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id;
+---------+-----------+---------+-----------+------------+
| area_id | area_name | shop_id | shop_name | sell_price |
+---------+-----------+---------+-----------+------------+
| 000A    | 北京      | 0001    | T恤衫     |       2000 |
| 000A    | 北京      | 0002    | 打孔器    |        600 |
| 000A    | 北京      | 0003    | 运动T恤   |       3000 |
| 000B    | 上海      | 0002    | 打孔器    |        600 |
| 000B    | 上海      | 0003    | 运动T恤   |       3000 |
| 000B    | 上海      | 0004    | 菜刀      |       6000 |
| 000B    | 上海      | 0006    | 扳手      |       3000 |
| 000C    | 重庆      | 0003    | 运动T恤   |       3000 |
| 000C    | 重庆      | 0004    | 菜刀      |       6000 |
| 000C    | 重庆      | 0006    | 扳手      |       3000 |
| 000D    | 杭州      | 0001    | T恤衫     |       2000 |
+---------+-----------+---------+-----------+------------+

联结需要注意的点：
1.FROM子句：因为联结需要用到两张或多张表，使用关键字INNER JOIN就可以把两张表联结在一起，虽然使用别名不是必须的，但是过长的表名会影响可读性，所以尽量使用别名。
2.ON子句：ON后面记载的是联结条件，我们可以在ON后面指定联结所使用的列（联结键），ON子句在进行联结时是必不可少的，并且必须书写在FROM和WHERE之间。
3.SELECT子句：由于使用多张表，所以列的归属会非常混乱。所以为了避免混淆列的归属，可以使用<表的别名>.<列名>的方法书写。

内联结和WHERE子句结合使用
如果不想了解所有地区的情况，只想了解上海地区的情况，可以在联结语句中加入WHERE来限定条件。

SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price
FROM shop_area AS SA JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id
WHERE SA.area_id='000B';
+---------+-----------+---------+-----------+------------+
| area_id | area_name | shop_id | shop_name | sell_price |
+---------+-----------+---------+-----------+------------+
| 000B    | 上海      | 0002    | 打孔器    |        600 |
| 000B    | 上海      | 0003    | 运动T恤   |       3000 |
| 000B    | 上海      | 0004    | 菜刀      |       6000 |
| 000B    | 上海      | 0006    | 扳手      |       3000 |
+---------+-----------+---------+-----------+------------+

像这样的联结运算结果，不仅可以使用WHERE子句，还能使用GROUP BY、HAVING、ORDER BY等。准确地说，可以完全把它看作一张新的表。

外联结——OUTER JOIN
外联结也是通过ON子句使用联结键将两张表进行联结，同时从两张表中选出相应的列，基本使用方法没有改变，但是结果却有所不同。

SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price
FROM shop_area AS SA RIGHT OUTER JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id;
+---------+-----------+---------+-----------+------------+
| area_id | area_name | shop_id | shop_name | sell_price |
+---------+-----------+---------+-----------+------------+
| 000A    | 北京      | 0001    | T恤衫     |       2000 |
| 000A    | 北京      | 0002    | 打孔器    |        600 |
| 000A    | 北京      | 0003    | 运动T恤   |       3000 |
| 000B    | 上海      | 0002    | 打孔器    |        600 |
| 000B    | 上海      | 0003    | 运动T恤   |       3000 |
| 000B    | 上海      | 0004    | 菜刀      |       6000 |
| 000B    | 上海      | 0006    | 扳手      |       3000 |
| 000C    | 重庆      | 0003    | 运动T恤   |       3000 |
| 000C    | 重庆      | 0004    | 菜刀      |       6000 |
| 000C    | 重庆      | 0006    | 扳手      |       3000 |
| 000D    | 杭州      | 0001    | T恤衫     |       2000 |
| NULL    | NULL      | NULL    | 砧板      |       2000 |
+---------+-----------+---------+-----------+------------+

即使联结键在两张表中没有都存在，该行还是会进行联结，不存在的数据以NULL显示。
外联结要点：
1.选取出单张表中的全部的信息：只要数据存在于某一个表中，就能够读取出来。
2.外联结的主表：外联结中的主表很重要，哪张为主表，那么最总的结果中会包含这张主表内的所有数据。指定主表的关键字是LEFT和RIGTH，使用LEFT时FROM子句写在左侧的为主表，反之右侧为主表。

3张以上表的联结
原则上，表的联结对表的数量没有限制。
首先创建一张名称为storage的表作为第三张需要联结的表：

+------------+---------+----------------+
| storage_id | shop_id | storage_number |
+------------+---------+----------------+
| S001       | 0001    |              0 |
| S001       | 0002    |            120 |
| S001       | 0003    |            200 |
| S001       | 0004    |              3 |
| S001       | 0005    |              0 |
| S001       | 0006    |             99 |
| S001       | 0007    |            999 |
| S001       | 0008    |            200 |
| S002       | 0001    |             10 |
| S002       | 0002    |             25 |
| S002       | 0003    |             34 |
| S002       | 0004    |             19 |
| S002       | 0005    |             99 |
| S002       | 0006    |              0 |
| S002       | 0007    |              0 |
| S002       | 0008    |             18 |
+------------+---------+----------------+

对第三张表进行联结

SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price,ST.storage_number
FROM shop_area AS SA INNER JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id
INNER JOIN storage AS ST
ON SA.shop_id=ST.shop_id
WHERE ST.storage_id='S001';
+---------+-----------+---------+-----------+------------+----------------+
| area_id | area_name | shop_id | shop_name | sell_price | storage_number |
+---------+-----------+---------+-----------+------------+----------------+
| 000A    | 北京      | 0001    | T恤衫     |       2000 |              0 |
| 000A    | 北京      | 0002    | 打孔器    |        600 |            120 |
| 000A    | 北京      | 0003    | 运动T恤   |       3000 |            200 |
| 000B    | 上海      | 0002    | 打孔器    |        600 |            120 |
| 000B    | 上海      | 0003    | 运动T恤   |       3000 |            200 |
| 000B    | 上海      | 0004    | 菜刀      |       6000 |              3 |
| 000B    | 上海      | 0006    | 扳手      |       3000 |             99 |
| 000C    | 重庆      | 0003    | 运动T恤   |       3000 |            200 |
| 000C    | 重庆      | 0004    | 菜刀      |       6000 |              3 |
| 000C    | 重庆      | 0006    | 扳手      |       3000 |             99 |
| 000D    | 杭州      | 0001    | T恤衫     |       2000 |              0 |
+---------+-----------+---------+-----------+------------+----------------+

可以简单地理解成，将1，2两张表先联结，然后将重新生成的表再与表3进行联结。

交叉联结——CROSS JOIN

SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name
FROM shop_area AS SA CROSS JOIN shop_list AS SL;
+---------+-----------+---------+-----------+
| area_id | area_name | shop_id | shop_name |
+---------+-----------+---------+-----------+
| 000A    | 北京      | 0001    | T恤衫     |
| 000A    | 北京      | 0001    | 打孔器    |
| 000A    | 北京      | 0001    | 运动T恤   |
| 000A    | 北京      | 0001    | 菜刀      |
| 000A    | 北京      | 0001    | 砧板      |
| 000A    | 北京      | 0001    | 扳手      |
| 000A    | 北京      | 0002    | T恤衫     |
| 000A    | 北京      | 0002    | 打孔器    |
| 000A    | 北京      | 0002    | 运动T恤   |
| 000A    | 北京      | 0002    | 菜刀      |
| 000A    | 北京      | 0002    | 砧板      |
| 000A    | 北京      | 0002    | 扳手      |
| 000A    | 北京      | 0003    | T恤衫     |
| 000A    | 北京      | 0003    | 打孔器    |
| 000A    | 北京      | 0003    | 运动T恤   |
| 000A    | 北京      | 0003    | 菜刀      |
| 000A    | 北京      | 0003    | 砧板      |
| 000A    | 北京      | 0003    | 扳手      |
| 000B    | 上海      | 0002    | T恤衫     |
| 000B    | 上海      | 0002    | 打孔器    |
| 000B    | 上海      | 0002    | 运动T恤   |
| 000B    | 上海      | 0002    | 菜刀      |
| 000B    | 上海      | 0002    | 砧板      |
| 000B    | 上海      | 0002    | 扳手      |
| 000B    | 上海      | 0003    | T恤衫     |
| 000B    | 上海      | 0003    | 打孔器    |
| 000B    | 上海      | 0003    | 运动T恤   |
| 000B    | 上海      | 0003    | 菜刀      |
| 000B    | 上海      | 0003    | 砧板      |
| 000B    | 上海      | 0003    | 扳手      |
| 000B    | 上海      | 0004    | T恤衫     |
| 000B    | 上海      | 0004    | 打孔器    |
| 000B    | 上海      | 0004    | 运动T恤   |
| 000B    | 上海      | 0004    | 菜刀      |
| 000B    | 上海      | 0004    | 砧板      |
| 000B    | 上海      | 0004    | 扳手      |
| 000B    | 上海      | 0006    | T恤衫     |
| 000B    | 上海      | 0006    | 打孔器    |
| 000B    | 上海      | 0006    | 运动T恤   |
| 000B    | 上海      | 0006    | 菜刀      |
| 000B    | 上海      | 0006    | 砧板      |
| 000B    | 上海      | 0006    | 扳手      |
| 000B    | 上海      | 0007    | T恤衫     |
| 000B    | 上海      | 0007    | 打孔器    |
| 000B    | 上海      | 0007    | 运动T恤   |
| 000B    | 上海      | 0007    | 菜刀      |
| 000B    | 上海      | 0007    | 砧板      |
| 000B    | 上海      | 0007    | 扳手      |
| 000C    | 重庆      | 0003    | T恤衫     |
| 000C    | 重庆      | 0003    | 打孔器    |
| 000C    | 重庆      | 0003    | 运动T恤   |
| 000C    | 重庆      | 0003    | 菜刀      |
| 000C    | 重庆      | 0003    | 砧板      |
| 000C    | 重庆      | 0003    | 扳手      |
| 000C    | 重庆      | 0004    | T恤衫     |
| 000C    | 重庆      | 0004    | 打孔器    |
| 000C    | 重庆      | 0004    | 运动T恤   |
| 000C    | 重庆      | 0004    | 菜刀      |
| 000C    | 重庆      | 0004    | 砧板      |
| 000C    | 重庆      | 0004    | 扳手      |
| 000C    | 重庆      | 0006    | T恤衫     |
| 000C    | 重庆      | 0006    | 打孔器    |
| 000C    | 重庆      | 0006    | 运动T恤   |
| 000C    | 重庆      | 0006    | 菜刀      |
| 000C    | 重庆      | 0006    | 砧板      |
| 000C    | 重庆      | 0006    | 扳手      |
| 000C    | 重庆      | 0007    | T恤衫     |
| 000C    | 重庆      | 0007    | 打孔器    |
| 000C    | 重庆      | 0007    | 运动T恤   |
| 000C    | 重庆      | 0007    | 菜刀      |
| 000C    | 重庆      | 0007    | 砧板      |
| 000C    | 重庆      | 0007    | 扳手      |
| 000D    | 杭州      | 0001    | T恤衫     |
| 000D    | 杭州      | 0001    | 打孔器    |
| 000D    | 杭州      | 0001    | 运动T恤   |
| 000D    | 杭州      | 0001    | 菜刀      |
| 000D    | 杭州      | 0001    | 砧板      |
| 000D    | 杭州      | 0001    | 扳手      |
+---------+-----------+---------+-----------+

交叉联结时，无法使用ON子句，这是因为交叉联结是对两张表内全部记录进行交叉组合，因此结果中记录数通常是两张表的行数乘积。

SQL操作指南八（集合运算）
表的加减法表的加法——UNION首先创建一个名称为shop_list2的表便于代码演示，如下：接下来，将两张表...
sql 集合运算
用来做例子的两个表 tb_text1tb_text1 tb_text2tb_text2 1. UNION 两个查询...
SQL——集合运算
SQL作用在关系上的union、intersect、except运算对应数学集合论中的并、交、差运算。通用形式：...
T-SQL基础（四）之集合运算
三个运算符 T-SQL支持三个集合运算符：UNION、INTERSECT、EXCEPT。集合运算符查询的一般形式...
sql server集合运算
集合运算包含四种：1.并集运算2.交集运算3.差集运算为什么使用集合运算：1.在集合运算中比联接查询和EXIST...
第八章：集合运算
第八章：集合运算 • 集合运算：是用来把两个或多个查询的结果集做并、交、差的集合运算，包含集合运算的查询称为复合...
Oracle PL/SQL (6) - 集合运算符、FORALL
1、集合运算符的使用set操作符用于取消特定嵌套表中的重复值。 2、使用FORALL批量绑定当使用PL/SQL执行...
第三章 SQL语言元素（二）
第三章 SQL语言元素（二）算术运算符和函数 InterSystems SQL支持以下算术运算符: + 加法操作...
掌握关系代数运算
关系代数关系代数是以关系为运算对象的一组高级运算的集合。关系代数中的操作可以分为两类：传统的集合操作，并、差、交、...
用 SQL 进行集合运算
书上很多SQL内容在MYSQL上不支持，比如EXPECT1.监测两个表是不是完全相同如果结果和A表和B表的行数一...