05.12 sql写法之集合运算

use tempdb;

create table tA(AID int,AName varchar(20));

create table tB(BID int,BName varchar(20));

insert into tA(AID,AName)values(1,'ab0001');

insert into tA(AID,AName)values(2,'ab0002');

insert into tA(AID,AName)values(3,'ab0003');

insert into tA(AID,AName)values(4,'ab0004');

insert into tA(AID,AName)values(5,'ab0005');

insert into tA(AID,AName)values(null,'ab(null)');

insert into tA(AID,AName)values(9,'ab0009');

insert into tA(AID,AName)values(9,'ab0009');

insert into tB(BID,BName)values(1,'ab0001');

insert into tB(BID,BName)values(2,'ab0002');

insert into tB(BID,BName)values(3,'ab0003');

insert into tB(BID,BName)values(6,'ab0006');

insert into tB(BID,BName)values(7,'ab0007');

insert into tB(BID,BName)values(8,'ab0008');

insert into tB(BID,BName)values(null,'ab(null)');

insert into tB(BID,BName)values(9,'ab0009');

/*****Sql Server******/

/*并集*/

/*union all*/

select * from tA

union all

select * from tB;

/*union*/

select * from tA

union

select * from tB;

/*差集*/

/*except*/

select * from tA

except

select * from tB;

/*基本等价于not exists，null除外，且不会去除重复行*/

select * from tA

where not exists(select 1 from tB where tA.AID = tB.BID and tA.AName = tB.BName);

/*交集 intersect*/

select * from tA

intersect

select * from tB;

/*基本等价于exists，null除外，且不会去除重复行*/

select * from tA

where exists(select 1 from tB where tA.AID = tB.BID and tA.AName = tB.BName);

/*****Oracle******/

/*并集*/

/*union all*/

select * from tA

union all

select * from tB;

/*union*/

select * from tA

union

select * from tB;

/*差集*/

/*except*/

select * from tA

minus

select * from tB;

/*基本等价于not exists，null除外，且不会去除重复行*/

select * from tA

where not exists(select 1 from tB where tA.AID = tB.BID and tA.AName = tB.BName);

/*交集 intersect*/

select * from tA

intersect

select * from tB;

/*基本等价于exists，null除外，且不会去除重复行*/

select * from tA

where exists(select 1 from tB where tA.AID = tB.BID and tA.AName = tB.BName);

/*清理数据*/

drop table tA;

drop table tB;