SQLSERVER数据集合的交、并、差集运算(intersect,union,except)

A:1,2,3,4  
B:1,2,5  
WITH A AS  
(SELECT ‘1‘ tno  
UNION ALL SELECT  ‘2‘ UNION ALL SELECT  ‘3‘ UNION ALL SELECT  ‘4‘   
),  
B AS(SELECT ‘1‘ tno  
UNION ALL SELECT  ‘2‘ UNION ALL SELECT  ‘5‘)  

--1 Union 取合集并过滤重复数据  
--结果显示: 1,2,3,4,5  
SELECT * FROM A  
UNION     
SELECT * FROM B;  

--2 Union all 取合集不过滤重复数据  
--结果显示:1,2,3,4,1,2,5  
SELECT * FROM A  
UNION  all  
SELECT * FROM B;  

--3 Intersect 取交集  
--结果显示:1,2  
SELECT * FROM A  
Intersect    
SELECT * FROM B;  

--4 except 取差集  
--结果显示:3,4  
SELECT * FROM A  
except    
SELECT * FROM B;