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;