你真的会SQL吗？关于SQL的join和on之间的区别

写了那么多年的SQL，居然发现自己写的是错的，实在是惭愧不已。还好没出什么问题。

于是，决定痛定思痛，纠正自己对SQL的错误认识。

 

我们有一个SQL：

SELECT sys.Netbios_Name0 as [Computer Name],
       sf.fileName
FROM dbo.v_R_System as sys
     INNER JOIN dbo.v_FullCollectionMembership as fcm
         ON fcm.ResourceID = sys.ResourceID
     LEFT JOIN dbo.v_GS_SoftwareFile as sf
         ON sf.resourceID = sys.resourceID
WHERE fcm.CollectionID = 'SMS00004'
  AND sf.fileName = 'outlook.exe'

 

目的是，在所有的v_FullCollectionMembership中，寻找CollectionID是SMS00004的电脑。

且看这些电脑是否安装了outlook.exe这个软件。

 

我们期望看到的数据是这样的：

Computer Name            FileName
COMP1                    OUTLOOK.EXE
COMP2                     
COMP3                    OUTLOOK.EXE
COMP4     
COMP5                    OUTLOOK.EXE 

 

结果出现的数据是这样的：

Computer Name            FileName
COMP1                    OUTLOOK.EXE
COMP3                    OUTLOOK.EXE
COMP5                    OUTLOOK.EXE 

 

为什么变成inner join了呢？？

解释：

    当你使用where的时候，就表示你告诉SQL去返回满足where条件的记录。无论你是用inner join还是outer join。因此，尽管你选择了left join，而实际上会被执行计划重写成inner join。

    换句话而言，就是where条件是保证最终的结果集必须满足的条件。

 

因此，我们得到了上面不愿意见到的结果。

 

那应该怎么办？改写一下：

SELECT sys.Netbios_Name0 as [Computer Name],
       sf.fileName
FROM dbo.v_R_System as sys
     INNER JOIN dbo.v_FullCollectionMembership as fcm
         ON fcm.ResourceID = sys.ResourceID
     LEFT JOIN dbo.v_GS_SoftwareFile as sf
         ON sf.resourceID = sys.resourceID
         AND sf.fileName = 'outlook.exe' --<Check it out>
WHERE fcm.CollectionID = 'SMS00004'

 把条件写到on里面就可以了。

 

那是不是所有的情况，都可以用on来解决呢？不，不是的。

看下面的例子：

on的执行条件相当于if

两个record比较的时候，条件为true，则返回数据，条件为false则返回null

 

对于on的情况，我们可以看到，on是完全不会过滤数据的。

当使用OUTER JOIN (包括LEFT , RIGHT, FULL)

都会尽可能低保持数据的原状，因此对于一些数据量特别大的表，还是会有困扰的。

 

那么where条件和on条件如何选择？？

在过滤数据的时候，使用where。

因为where一般会通过执行计划优化，缩小查询的范围，使得结果集足够小。

而在需要保留全面信息的时候，就要把一部分的条件，写到on里面去。

 

总结：

where用于缩小结果集，但是会强行进行inner join

on用于保持完整数据，但是会判断所有的数据

 

 参考内容：

http://www.tech-recipes.com/rx/47637/inner-and-left-outer-join-with-where-clause-vs-on-clause/

http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108359.aspx