create table A( id int IDENTITY(1,1) Not null primary key, name varchar(20) not null default(‘‘),)INSERT INTO [A]([name]) VALUES(‘a‘)INSERT INTO [A]([name]) VALUES(‘b‘)INSERT INTO [A]([name]) VALUES(‘c‘)INSERT INTO [A]([name]) VALUES(‘d‘)INSERT INTO [A]([name]) VALUES(‘e‘)INSERT INTO [A]([name]) VALUES(‘f‘)INSERT INTO [A]([name]) VALUES(‘g‘)create table B( id int IDENTITY(1,1) Not null primary key, name varchar(20) not null default(‘‘),)INSERT INTO [B]([name]) VALUES(‘a‘)INSERT INTO [B]([name]) VALUES(‘b‘)INSERT INTO [B]([name]) VALUES(‘c‘)INSERT INTO [B]([name]) VALUES(‘d‘)INSERT INTO [B]([name]) VALUES(‘h‘)INSERT INTO [B]([name]) VALUES(‘i‘)INSERT INTO [B]([name]) VALUES(‘j‘)

SELECT * from A union select * from B --查询A\B表的并集重复的项只显示一个SELECT * from A union all select * from B ----查询A\B表的并集重复的也显示SELECT * from A union all select * from B order by id asc ---查询A\B表的并集重复的也显示，并按照id升序SELECT * from A INTERSECT select * from B ----查询两表的交集SELECT * from A EXCEPT select * from B ----查询A表中不与B表重复的记录--查询A\B两表中所有非交集的记录(SELECT * from A EXCEPT select * from B) union (SELECT * from B EXCEPT select * from A)