SQL SERVER: 合并相关操作(Union,Except,Intersect)

SQL SERVER: 合并相关操作(Union,Except,Intersect)

use tempdb

create table tempTable1 (id int primary key identity, price int)
create table tempTable2 (id int primary key identity, price int)
insert into tempTable1 select 3 union all select 1 union all select 2 union all select 3 
insert into tempTable2 select 3 union all select 4 union all select 1 union all select 2

select * from temptable1
select * from temptable2

--union会删除重复值，也就是说A和B中重复的行，最终只会出现一次，而union all则会保留重复行。
select * from temptable1
union
select * from temptable2

select * from temptable1
union all
select * from temptable2

--差异（Except）
--就是两个集中不重复的部分。例如
SELECT * from temptable1
except
select * from temptable2

--交集（intersect)
--就是两个集中共同的部分。例如
select * from temptable1
INTERSECT
select * from temptable2

DROP TABLE temptable1
DROP TABLE temptable2