写了那么多年的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
相关推荐
left_join_on_and与left_join_on_where的区别
本文简单描述了sql语句join中on和where的区别以及其原理
Join on/inner join on/full join on/full outer join on/left join on/right join on/cross join on; 在使用jion时,on和where条件的区别;
1、 on条件是在成临时表时使的条件,它不管on中的条件是否为真,都 2、where条件是在临时表成好后,再对临时表进过滤的条件 1、中间表 2、再对中间表过滤
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!
非常不错使用join on实现数据库字段的连接输出效果。
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。这篇文章主要介绍了sql中的left join以及on、where关键字的区别,需要的朋友可以参考下
SQL LEFT JOIN 关键字 SQL LEFT JOIN 关键字 LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。 SQL LEFT JOIN 语法 SELECT column_name(s) FROM...
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句2.txt,欢迎下载!
LINQ to SQL语句之Join和Order By部分代码 语句描述:这个例子在From子句中使用外键导航筛选在西雅图的雇员,同时列出其所在地区。这条生成SQL语句为: SELECT [t0].[FirstName], [t0].[LastName], [t2]....
大多数人一般写多表查询会这样写select * from tbA ,tbB 没有用到JOIN关键字,太Low了,官网标准建议是用JOIN明确表间的关系,下面具体来讲。 连接类型: 交叉联接 得到所连接表的所有组合 (笛卡儿集)cross ...
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 说明:两张关联表...
SQL Prompt 为Microsoft SQL Server 编辑器提供一种智能感知形式的自动完成功能,当你正在写你自己的SQL命令时,它也会告诉你应该使用正确的格式,帮助你快速地写出格式良好的SQL语句。SQL Prompt会提升你创建任何...
在基本的逻辑句法之外,还有一些关于连接和简便数据参考方面的概念,例如别名,自连接和ON子句。 别名不只是做连接 别名是一个SQL功能,他允许你在一个查询中为表格或专栏创建一个速记符号,他在处理自连接时也可以...
当你写代码,SQL Prompt建议适当的关键字和查询对象。它甚至可以完成INSERT,ALTER和JOIN声明。为了减少重复输入,SQL Prompt下有一个广泛的,可定制的片段。 编辑您的SQL时,为了避免并发症,可以使用智能重命名,...
其中包括LEFT JOIN、ON条件过滤、笛卡尔积等概念和操作。本文适合对MySQL数据库和JOIN操作感兴趣的读者进行学习和参考。 使用人群:MySQL开发人员、数据库管理员、对JOIN操作和表关联有需求的读者 用途:帮助读者...
当你写代码,SQL Prompt建议适当的关键字和查询对象。它甚至可以完成INSERT,ALTER和JOIN声明。为了减少重复输入,SQL Prompt下有一个广泛的,可定制的片段。 编辑您的SQL时,为了避免并发症,可以使用智能重命名,...
当你写代码,SQL Prompt建议适当的关键字和查询对象。它甚至可以完成INSERT,ALTER和JOIN声明。为了减少重复输入,SQL Prompt下有一个广泛的,可定制的片段。 编辑您的SQL时,为了避免并发症,可以使用智能重命名,...
鼎捷易飞ERP体检SQL,如:产品类别订单准交率,月度接单和销货金额,工单平均生产周期,工单准时完工率,车间工单准时开工率,组装工单齐套率,采购平均交付天数,采购准交率,每月库存金额等常用sql --1【产品类别...