mssql sqlserver中from tableNameA inner join tableNameB 与tableNameA left join tableNameB 使用区别说明


摘要:
下文讲述tableNameA inner join tableNameB 组合同 tableNameA left join tableNameB之间的使用区别,如下所示:
实验环境:sqlserver 2008 R2


   ---创建表testA,testB
   create table testA(keyId int,infoA varchar(10))
   create table testB(keyId int,infoB varchar(10))
   go
   
   insert into testA(keyId,infoA)values(1,'a'),(2,'b');
   insert into testB(keyId,infoB)values(1,'aa'),(20,'bb');
   go

   ---例:testA testB两表相连,取 keyId列相同的值
   select * from testA,testB where testA.keyId = testB.keyId  
   类似
   select * from testA inner join testB on testA.keyId = testB.keyId 
   ---输出----
   keyId,infoA,keyId,infoB
  1,a,1,aa 
    select * from testA left join testB on  testA.keyId = testB.keyId  
   ---输出----
   keyId,infoA,keyId,infoB
  1,a,1,aa 
  2,b,null,null 
    go
   drop table testA
   drop table testB


两者区别:
from tableNameA,tableNameB 类似一个内连接,将返回两张表中相同的记录
tableNameA left join tableNameB 将返回tableNamA的所有数据和tableNameB中匹配完全的数据行,tableNameB中无法匹配的数据行,采用null空值返回。