22,209
社区成员
发帖
与我相关
我的任务
分享
create table t1(
col1 int,
col2 int
)
insert t1
select 1,2 union all
select 2,3 union all
select 3,4 union all
select 4,5 union all
select 5,6
create table t2(
col1 int,
col2 int,
col3 int
)
insert t2
select 1,2,4 union all
select 2,3,5 union all
select 5,6,7
select col1,col2,col3=0 from(
select t1.col1,t1.col2,t2.col1 as a,t2.col2 as b,t2.col3 as c from t1
inner join t2 on t1.col1=t2.col1 and t1.col2=t2.col2)m
union all
select a,b,c from(
select t1.col1,t1.col2,t2.col1 as a,t2.col2 as b,t2.col3 as c from t1
inner join t2 on t1.col1=t2.col1 and t1.col2=t2.col2
)n
/*
1 2 0
2 3 0
5 6 0
1 2 4
2 3 5
5 6 7
*/
select * from t1 where exists(select 1 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2)
select * from t2 where exists(select 1 from t1 where t1.col1=t2.col1 and t1.col2=t2.col2)