mssql sqlserver使用join代替in关键字的方法分享


摘要:
下文讲述sqlserver中使用join替换in关键字的方法分享,如下所示:
实验环境:sql server 2008 R2


  ---in 关键字的写法
   SELECT count(1)
     FROM tableNameA a
   WHERE a.keyId  not IN( SELECT keyId
                        FROM tableNameB as b
                        WHERE  b.info ='test'
                     )
  ---转换为join关键字
    SELECT count(1)
      FROM tableNameA a join tableNameB b on a.keyId = b.keyId 
    WHERE  b.info <>'test'


 ---in 关键字写法
    SELECT count(*)
      FROM tableNameA a
    WHERE keyId  IN ( SELECT keyId
                     FROM tableNameB b
                     WHERE b.info ='test'
                    )


---join 关键字转发 
  SELECT count(1)
      FROM tableNameA a join tableNameB b 
            on a.keyId = b.keyId 
     WHERE  b.info ='test'