1: select * from 表1 where exists(select 1 form 表2 where 表1.列1=表2.列1 and 表1.列2=表2.列2) 2: select a.* from 表1 a, 表2 b where a.列1=b.列1 and a.列2=b.列2
并运算 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 drop table #1 drop table #2
select * from 表1
where exists(select 1 form 表2 where 表1.列1=表2.列1 and 表1.列2=表2.列2)
2:
select a.* from 表1 a, 表2 b
where a.列1=b.列1 and a.列2=b.列2
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
drop table #1
drop table #2