你需要明白的数据集之间的运算(UNION,INTERSECT,EXCEPT)

1. UNION  A∪B的逻辑,当遇到数据集中重复的行时，紧保留一个

2. UNION ALL A∪B的逻辑，但与UNION不同的是，当遇到两个数据集中重复的行时，全部保留

3. INTERSECT A∩B的关系,不支持INTERSECT ALL

4.EXCEPT 实现A-B的关系,不支持 EXCEPT ALL

测试代码：

SELECT  1 AS Value1, 1 AS Value2 INTO #T1
UNION ALL
SELECT  1 AS Value1, 1 AS Value2
UNION ALL
SELECT  2 AS Value1, 2 AS Value2
UNION ALL
SELECT  2 AS Value1, 4 AS Value2
UNION ALL
SELECT  3 AS Value1, 3 AS Value2

 

SELECT  1 AS Value3, 1 AS Value4 INTO #T2
UNION ALL
SELECT  1 AS Value3, 1 AS Value4
UNION ALL
SELECT  2 AS Value3, 2 AS Value4
UNION ALL
SELECT  2 AS Value3, 4 AS Value4
UNION ALL
SELECT  5 AS Value3, 5 AS Value4


SELECT Value1,Value2 FROM #T1
INTERSECT 
SELECT Value3,Value4 FROM #T2


SELECT Value1,Value2 FROM #T1
EXCEPT 
SELECT Value3,Value4 FROM #T2


DROP TABLE #T1
DROP TABLE #T2