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

标签: sql sql join | 发表时间:2014-12-05 22:11 | 作者:dacoolbaby
出处:http://www.iteye.com

写了那么多年的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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [sql sql join] 推荐:

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

- - Oracle - 数据库 - ITeye博客
写了那么多年的SQL,居然发现自己写的是错的,实在是惭愧不已. 于是,决定痛定思痛,纠正自己对SQL的错误认识. 目的是,在所有的v_FullCollectionMembership中,寻找CollectionID是SMS00004的电脑. 且看这些电脑是否安装了outlook.exe这个软件. 我们期望看到的数据是这样的:.

PL/SQL动态SQL(原创)

- - ITeye博客
使用动态SQL是在编写PL/SQL过程时经常使用的方法之一. 很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成. 再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只能针对某几个特定的表来形成分页.

Derby SQL 分页

- - ITeye博客
    之前在网上看到有人问 Derby SQL 分页实现的问题,网上有人给出这样的解决方案,SQL 如下:. 其实,这样的分页查询,性能不理想,我试过在 300W 数据量中采用这种分页方式,需要 20~30秒之久;其实 Derby 10.6 以上版本有更好的分页支持,直接给出 SQL 实现如下:.

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

MySql动态SQL

- - SQL - 编程语言 - ITeye博客
13.7. 用于预处理语句的SQL语法. MySQL 5.1对服务器一方的预制语句提供支持. 如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势. 候选界面包括MySQL C API客户端库(用于C程序)、MySQL Connector/J(用于Java程序)和MySQL Connector/NET.

sql优化

- - 数据库 - ITeye博客
是对数据库(数据)进行操作的惟一途径;. 消耗了70%~90%的数据库资源;独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;. 可以有不同的写法;易学,难精通. 固定的SQL书写习惯,相同的查询尽量保持相同,存储过程的效率较高. 应该编写与其格式一致的语句,包括字母的大小写、标点符号、换行的位置等都要一致.

birt动态SQL

- - ITeye博客
birt动态SQL实现有三种方式:拼接SQL、绑定变量和让应用程序拼接,birt得到返回结果集方式. 在数据集中写SQL,如下:. 选中数据集,点script方式,在beforeOpen事件中写如下SQL:. 然后就可以了,当然,也可以不写第一步,直接所有的SQL都在beforeOpen中拼接. 但是,拼接SQL方式不仅复杂容易错,还会导致SQL注入风险.

SQL Server 面试

- - SQL - 编程语言 - ITeye博客
在SQL语言中,一个SELECT…FROM…WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询. 子查询分为嵌套子查询和相关子查询两种. 嵌套子查询的求解方法是由里向外处理,即每个子查询在其上一级查询处理之前求解,子查询的结果作为其父查询的查询条件. 子查询只执行一次,且可以单独执行;.

MongoDB sql操作

- - 数据库 - ITeye博客
1.  基本查询:. 下面的示例等同于SQL语句的where name = "stephen" and age = 35.      --返回指定的文档键值对. 下面的示例将只是返回name和age键值对.      --指定不返回的文档键值对. 下面的示例将返回除name之外的所有键值对.

sql 优化

- - SQL - 编程语言 - ITeye博客
转:数据库SQL优化大总结之 百万级数据库优化方案. 2014-07-18 09:33 雲霏霏 雲霏霏的博客 字号:. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 网上关于SQL优化的教程很多,但是比较杂乱. 近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充.