VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > MongoDB >

  • SQL UNION（并集）、INTERSECT（交集）、EXCEPT（差集）

一、什么是集合运算

本文将会和大家一起学习集合运算操作。集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

截至目前，我们已经学习了从表中读取数据以及插入数据的方法。所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

KEYWORD

  • 集合运算

  • 集合

  • 记录的集合

  • 集合运算符

本文将会为大家介绍表的加减法，下一篇（SQL 联结）将会和大家一起学习进行“表联结”的集合运算符及其使用方法。

二、表的加法——UNION

首先为大家介绍的集合运算符是进行记录加法运算的 UNION（并集）。

KEYWORD

  • UNION（并集）

在学习具体的使用方法之前，我们首先添加一张表，该表的结构与之前我们使用的 Product（商品）表相同，只是表名变为 Product2（商品 2）（代码清单 1）。

代码清单 1　创建表 Product2（商品 2）




	
		

			
				

					 

					
			
				CREATE TABLE Product2
		

		

			
				

					 

					
			
				(product_id CHAR(4) NOT NULL,
		

		

			
				

					 

					
			
				product_name VARCHAR(100) NOT NULL,
		

		

			
				

					 

					
			
				product_type VARCHAR(32) NOT NULL,
		

		

			
				

					 

					
			
				sale_price INTEGER ,
		

		

			
				

					 

					
			
				purchase_price INTEGER ,
		

		

			
				

					 

					
			
				regist_date DATE ,
		

		

			
				

					 

					
			
				PRIMARY KEY (product_id));
		

	




	接下来，我们将代码清单 2 中的 5 条记录插入到 Product2 表中。商品编号（product_id）为“0001”~“0003”的商品与之前 Product 表中的商品相同，而编号为“0009”的“手套”和“0010”的“水壶”是 Product 表中没有的商品。



	代码清单 2　将数据插入到表 Product2（商品 2）中



	SQL Server PostgreSQL





	
		

			
				

					 

					
			
				BEGIN TRANSACTION; ---------①
		

		

			
				

					 

					
			
				INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2009-09-20');
		

		

			
				

					 

					
			
				INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
		

		

			
				

					 

					
			
				INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
		

		

			
				

					 

					
			
				INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
		

		

			
				

					 

					
			
				INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
		

		

			
				

					 

					
			
				COMMIT;
		

	




	

		特定的 SQL

	

		不同的 DBMS 的事务处理的语法也不尽相同。代码清单 2 中的 DML 语句在 MySQL 中执行时，需要将 ① 部分更改为“START TRANSACTION;”。在 Oracle 和 DB2 中执行时，无需用到 ① 的部分（请删除）。

	

		详细内容请大家参考 事务 中的“创建事务”。





	这样我们的准备工作就完成了。接下来，就让我们对上述两张表进行“Product 表 + Product2 表”的加法计算吧。语法请参考代码清单 3。



	代码清单 3　使用 UNION 对表进行加法运算





	
		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product
		

		

			
				

					 

					
			
				UNION
		

		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product2;
		

	




	执行结果





	
		

			
				

					 

					
			
				product_id | product_name
		

		

			
				

					 

					
			
				-----------+-------------
		

		

			
				

					 

					
			
				0001 | T恤衫
		

		

			
				

					 

					
			
				0002 | 打孔器
		

		

			
				

					 

					
			
				0003 | 运动T恤
		

		

			
				

					 

					
			
				0004 | 菜刀
		

		

			
				

					 

					
			
				0005 | 高压锅
		

		

			
				

					 

					
			
				0006 | 叉子
		

		

			
				

					 

					
			
				0007 | 擦菜板
		

		

			
				

					 

					
			
				0008 | 圆珠笔
		

		

			
				

					 

					
			
				0009 | 手套
		

		

			
				

					 

					
			
				0010 | 水壶
		

	




	上述结果包含了两张表中的全部商品。可能有些读者会发现，这就是我们在学校学过的集合中的并集运算，通过文氏图会看得更清晰（图 1）。



	使用 UNION 对表进行加法（并集）运算的图示

	

		图 1　使用 UNION 对表进行加法（并集）运算的图示

	



	商品编号为“0001”~“0003”的 3 条记录在两个表中都存在，因此大家可能会认为结果中会出现重复的记录，但是 UNION 等集合运算符通常都会除去重复的记录。



	

		法则 1

	

		集合运算符会除去重复的记录。





	三、集合运算的注意事项



	其实结果中也可以包含重复的记录，在介绍该方法之前，还是让我们先来学习一下使用集合运算符时的注意事项吧。不仅限于 UNION，之后将要学习的所有运算符都要遵守这些注意事项。


    
	
  • 
		
    
			注意事项 ① ——作为运算对象的记录的列数必须相同
    
		
    
			例如，像下面这样，一部分记录包含 2 列，另一部分记录包含 3 列时会发生错误，无法进行加法运算。
    
		
    
		
    
			
				
    
					
						
    
							 
    
					    		
					
						-- 列数不一致时会发生错误
				
    
				
    
					
						
    
							 
    
					    		
					
						SELECT product_id, product_name
				
    
				
    
					
						
    
							 
    
					    		
					
						FROM Product
				
    
				
    
					
						
    
							 
    
					    		
					
						UNION
				
    
				
    
					
						
    
							 
    
					    		
					
						SELECT product_id, product_name, sale_price
				
    
				
    
					
						
    
							 
    
					    		
					
						FROM Product2;
				
    
			    
		
    
	
    • 
	
  • 
		
    
			注意事项 ②——作为运算对象的记录中列的类型必须一致
    
		
    
			从左侧开始，相同位置上的列必须是同一数据类型。例如下面的 SQL 语句，虽然列数相同，但是第 2 列的数据类型并不一致（一个是数值类型，一个是日期类型），因此会发生错误 [1]。
    
		
    
		
    
			
				
    
					
						
    
							 
    
					    		
					
						-- 数据类型不一致时会发生错误
				
    
				
    
					
						
    
							 
    
					    		
					
						SELECT product_id, sale_price
				
    
				
    
					
						
    
							 
    
					    		
					
						FROM Product
				
    
				
    
					
						
    
							 
    
					    		
					
						UNION
				
    
				
    
					
						
    
							 
    
					    		
					
						SELECT product_id, regist_date
				
    
				
    
					
						
    
							 
    
					    		
					
						FROM Product2;
				
    
			    
		
    
		
    
			一定要使用不同数据类型的列时，可以使用 各种各样的函数 中的类型转换函数 CAST。
    
	
    • 
	
  • 
		
    
			注意事项 ③——可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次
    
		
    
			通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句，之前学过的 WHERE、GROUP BY、HAVING 等子句都可以使用。但是 ORDER BY 只能在最后使用一次（代码清单 4）。
    
		
    
			代码清单 4　ORDER BY 子句只在最后使用一次
    
		
    
		
    
			
				
    
					
						
    
							 
    
					    		
					
						SELECT product_id, product_name
				
    
				
    
					
						
    
							 
    
					    		
					
						FROM Product
				
    
				
    
					
						
    
							 
    
					    		
					
						WHERE product_type = '厨房用具'
				
    
				
    
					
						
    
							 
    
					    		
					
						UNION
				
    
				
    
					
						
    
							 
    
					    		
					
						SELECT product_id, product_name
				
    
				
    
					
						
    
							 
    
					    		
					
						FROM Product2
				
    
				
    
					
						
    
							 
    
					    		
					
						WHERE product_type = '厨房用具'
				
    
				
    
					
						
    
							 
    
					    		
					
						ORDER BY product_id;
				
    
			    
		
    
		
    
			执行结果
    
		
    
		
    
			
				
    
					
						
    
							 
    
					    		
					
						product_id | product_name
				
    
				
    
					
						
    
							 
    
					    		
					
						-----------+--------------
				
    
				
    
					
						
    
							 
    
					    		
					
						0004 | 菜刀
				
    
				
    
					
						
    
							 
    
					    		
					
						0005 | 高压锅
				
    
				
    
					
						
    
							 
    
					    		
					
						0006 | 叉子
				
    
				
    
					
						
    
							 
    
					    		
					
						0007 | 擦菜板
				
    
				
    
					
						
    
							 
    
					    		
					
						0010 | 水壶
				
    
			    
		
    
	
    • 




	四、包含重复行的集合运算——ALL 选项



	接下来给大家介绍在 UNION 的结果中保留重复行的语法。其实非常简单，只需要在 UNION 后面添加 ALL 关键字就可以了。这里的 ALL 选项，在 UNION 之外的集合运算符中同样可以使用（代码清单 5）。



	

		KEYWORD

	
    
		
  • 
			ALL 选项
    • 
	





	代码清单 5　保留重复行





	
		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product
		

		

			
				

					 

					
			
				UNION ALL
		

		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product2;
		

	




	执行结果



	保留重复行



	

		法则 2

	

		在集合运算符中使用 ALL 选项，可以保留重复行。





	五、选取表中公共部分——INTERSECT



	下面将要介绍的集合运算符在数的四则运算中并不存在，不过也不难理解，那就是选取两个记录集合中公共部分的 INTERSECT（交集）[2]。



	

		KEYWORD

	
    
		
  • 
			INTERSECT（交集）
    • 
	





	让我们赶快来看一下吧。其语法和 UNION 完全一样（代码清单 6）。



	代码清单 6　使用 INTERSECT 选取出表中公共部分



	Oracle SQL Server DB2 PostgreSQL





	
		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product
		

		

			
				

					 

					
			
				INTERSECT
		

		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product2
		

		

			
				

					 

					
			
				ORDER BY product_id;
		

	




	执行结果





	
		

			
				

					 

					
			
				product_id | product_name
		

		

			
				

					 

					
			
				------------+--------------
		

		

			
				

					 

					
			
				0001 | T恤衫
		

		

			
				

					 

					
			
				0002 | 打孔器
		

		

			
				

					 

					
			
				0003 | 运动T恤
		

	




	大家可以看到，结果中只包含两张表中记录的公共部分。该运算的文氏图如下所示（图 2）。



	使用 INTERSECT 选取出表中公共部分的图示

	

		图 2　使用 INTERSECT 选取出表中公共部分的图示

	



	与使用 AND 可以选取出一张表中满足多个条件的公共部分不同，INTERSECT 应用于两张表，选取出它们当中的公共记录。



	其注意事项与 UNION 相同，我们在“集合运算的注意事项”和“保留重复行的集合运算”中已经介绍过了。希望保留重复行时同样需要使用 INTERSECT ALL。



	六、记录的减法——EXCEPT



	最后要给大家介绍的集合运算符就是进行减法运算的 EXCEPT（差集）[3]，其语法也与 UNION 相同（代码清单 7）。



	

		KEYWORD

	
    
		
  • 
			EXCEPT（差集）
    • 
	





	代码清单 7　使用 EXCEPT 对记录进行减法运算



	SQL Server DB2 PostgreSQL





	
		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product
		

		

			
				

					 

					
			
				EXCEPT
		

		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product2
		

		

			
				

					 

					
			
				ORDER BY product_id;
		

	




	

		特定的 SQL

	

		在 Oracle 中执行代码清单 7 或者代码清单 8 中的 SQL 时，请将 EXCEPT 改为 MINUS。

	

	

		
			

				
					

						 

						
				
					-- Oracle中使用MINUS而不是EXCEPT
			

			

				
					

						 

						
				
					SELECT …
			

			

				
					

						 

						
				
					FROM …
			

			

				
					

						 

						
				
					MINUS
			

			

				
					

						 

						
				
					SELECT …
			

			

				
					

						 

						
				
					FROM …;
			

		
	





	执行结果





	
		

			
				

					 

					
			
				product_id | product_name
		

		

			
				

					 

					
			
				-----------+--------------
		

		

			
				

					 

					
			
				0004 | 菜刀
		

		

			
				

					 

					
			
				0005 | 高压锅
		

		

			
				

					 

					
			
				0006 | 叉子
		

		

			
				

					 

					
			
				0007 | 擦菜板
		

		

			
				

					 

					
			
				0008 | 圆珠笔
		

	




	大家可以看到，结果中只包含 Product 表中记录除去 Product2 表中记录之后的剩余部分。该运算的文氏图如图 3 所示。



	使用 EXCEPT 对记录进行减法运算的图示

	

		图 3　使用 EXCEPT 对记录进行减法运算的图示

	



	EXCEPT 有一点与 UNION 和 INTERSECT 不同，需要注意一下。那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。4 + 2 和 2 + 4 的结果相同，但是 4 - 2 和 2 - 4 的结果却不一样。因此，我们将之前 SQL 中的 Product 和 Product2 互换，就能得到代码清单 8 中的结果。



	代码清单 8　被减数和减数位置不同，得到的结果也不同



	SQL Server DB2 PostgreSQL





	
		

			
				

					 

					
			
				-- 从Product2的记录中除去Product中的记录
		

		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product2
		

		

			
				

					 

					
			
				EXCEPT
		

		

			
				

					 

					
			
				SELECT product_id, product_name
		

		

			
				

					 

					
			
				FROM Product
		

		

			
				

					 

					
			
				ORDER BY product_id;
		

	




	执行结果





	
		

			
				

					 

					
			
				product_id | product_name
		

		

			
				

					 

					
			
				------------+--------------
		

		

			
				

					 

					
			
				0009 | 手套
		

		

			
				

					 

					
			
				0010 | 水壶
		

	




	上述运算的文氏图如图 4 所示。



	使用 EXCEPT 对记录进行减法运算的图示（从 Product2 中除去 Product 中的记录）

	

		图 4　使用 EXCEPT 对记录进行减法运算的图示（从 Product2 中除去 Product 中的记录）

	



	到此，对 SQL 提供的集合运算符的学习已经结束了。可能有些读者会想“唉？怎么没有乘法和除法呢？”关于乘法的相关内容，我们将在 SQL 联结 中详细介绍。此外，SQL 中虽然也存在除法，但由于除法是比较难理解的运算，属于中级内容，因此我们会在 SQL 联结 末尾的专栏中进行一些简单的介绍，感兴趣的读者请参考专栏“关系除法”。

	

	原文：https://www.cnblogs.com/vin-c/p/15607602.html


      



  

  
  

   
    
    
   

  

  
 
 

 
 
  
 
 
 
 






 






相关教程