select * from t1 where a in (select a from t3) and b in (select b from t3) and a not in (select a from t2) and b not in (select b from t2) and c not in (select c from t2);
select t1.a,t1.b,t1.c,t1.d from t1,t3 where concat(a,b,c)d c_1 not in (select concat(a,b,c)d c_2 from t2) and t1.a=t3.a and t1.b= t3.b
select t1.* from table_1 t1 where exists ( select 1 from table_2 t2 where t1.a=t2.a and t1.b=t2.b) and not exists( select 1 from table_3 t3 where t1.a=t3.a and t1.b=t3.b and t1.c=t3.c and t1.d=t3.d) 用exists 代替 IN 用not exists 代替 NOT IN 这样效率高
where
concat(a,b,c)d c_1 not in
(select concat(a,b,c)d c_2 from t2)
and t1.a=t3.a and t1.b= t3.b
select 1 from table_2 t2 where t1.a=t2.a and t1.b=t2.b) and not exists(
select 1 from table_3 t3 where t1.a=t3.a and t1.b=t3.b and t1.c=t3.c
and t1.d=t3.d)
用exists 代替 IN
用not exists 代替 NOT IN
这样效率高