表1-表2:select * from 表1 where not exists(select 1 from 表2 where 表1.id=表2.id )表2-表1select * from 表2 where not exists(select 1 from 表1 where 表1.id=表2.id )
关系运算:并运算 select c1,c2 from t1 union all select c1,c2 from t2差: c1-c2:select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)c2-c1:select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)交: select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2) 除: create table #1(A char(1),B char(1),C char(1),D char(1)) insert #1 values('a','b','c','d') insert #1 values('a','b','e','f') insert #1 values('b','c','e','f') insert #1 values('e','d','c','d') insert #1 values('e','d','e','f') insert #1 values('a','b','d','e')create table #2 (A char(1),B char(1)) insert #2 values('c','d') insert #2 values('e','f')select a,b from #1 bb where exists(select 1 from (select distinct #1.a,#1.b,#2.a c,#2.b d from #1,#2) aa where aa.a=bb.a and aa.b=bb.b and aa.c=bb.c and aa.d=bb.d) group by a,b having count(*)>1
select * from 表1 where id not in (select id from 表2)
select * from 表1 where id not in (select id from 表2)
表1-表2:select * from 表1 where not exists(select 1 from 表2 where 表1.id=表2.id )表2-表1select * from 表2 where not exists(select 1 from 表1 where 表1.id=表2.id )
关系运算:并运算
select c1,c2 from t1
union all
select c1,c2 from t2差:
c1-c2:select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)c2-c1:select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)交:
select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
除:
create table #1(A char(1),B char(1),C char(1),D char(1))
insert #1 values('a','b','c','d')
insert #1 values('a','b','e','f')
insert #1 values('b','c','e','f')
insert #1 values('e','d','c','d')
insert #1 values('e','d','e','f')
insert #1 values('a','b','d','e')create table #2 (A char(1),B char(1))
insert #2 values('c','d')
insert #2 values('e','f')select a,b from #1 bb where exists(select 1 from (select distinct #1.a,#1.b,#2.a c,#2.b d from #1,#2) aa where aa.a=bb.a and aa.b=bb.b and aa.c=bb.c and aa.d=bb.d) group by a,b having count(*)>1
where id not in (select id from 表2)