外连接替换not in

select t1.* from test1 t1 where t1.tel not in (select t2.tel from test2 t2 where t1.tel=t2.tel)
替换为
select t.id, t.phone from (select t1.id,t1.tel phone,t2.tel from test1 t1 left join test2 t2 on t1.tel=t2.tel) t where t.tel is null
另外 not in注意null为空的情况 如果为空可能查询不出来了
sql1
select t1.* from test1 t1 where t1.tel not in (select t2.tel from test2 t2 where t1.tel=t2.tel)
与
sql2
select t1.* from test1 t1 where t1.tel not in (select t2.tel from test2 t2 )  的区别 第二个sql有可能出错
因为 not in （）中如果有一项为null则查询结果为空
上面sql2正确写法
select t1.* from test1 t1 where t1.tel not in (select t2.tel from test2 t2 where t2.tel is not null)


另sql连接字符串
concat链接字符串
select t.id, t.phone
  from (select t1.id, t1.tel phone, t2.tel
          from test1 t1
          left join test2 t2
            on t1.tel = t2.tel or t1.tel =CONCAT('010',t2.tel)) t
where t.tel is null