深入解析on where谓词的区别

 

对于包含一个外连接的查询子句有一个令人困惑的地方，那就是我们应该把一个逻辑表达式放在on过滤器中还是where过滤器中。二者最大的区别在于，on过滤器是在添加外部行前执行（1计算笛卡尔积2过滤3添加外部行），而where过滤器是在添加外部行后执行。

通过on过滤器对于保留表中的一行的剔除并不是最终的因为添加外部行时仍然会把数据加回来。相反，通过where过滤器对一行的剔除是最终的。明白这一点可以帮助你做出正确的选择。

 

例如：

select c.customerid,c.city,o.orderid

from dbo.customers as c

left join

dbo.orders as o

on c.customerid=o.customerid

and

c.city=N'Madrid'

 

运行这段代码得到如下结果：

 

图1-1

 

请注意第七条数据，city=’Zion’,通常我们会疑惑 我们不是已经在on条件中进行了顾虑了吗

（只取city为Madrid马德里的数据）？这条数据在on阶段已经被过滤掉了，但是在添加外部行这个阶段时又被添加进来了(这个查询用的是左外连接)，orderid为null,就是说通过on条件过滤后在orders表未发现相匹配的记录。

 

 

 

请比较：

select c.customerid,c.city,o.orderid

from dbo.customers as c

left join

dbo.orders as o

on c.customerid=o.customerid

 

图1-2

请注意图1-1中第七条数据和图1-2图中第七条数据的区别。

 

请比较：

select c.customerid,c.city,o.orderid

from dbo.customers as c

 join

dbo.orders as o

on c.customerid=o.customerid

and

c.city=N'Madrid'

 

图1-3

两个外部行在On阶段都被过滤掉。由于该连接不是外连接只有两个处理阶段(1计算笛卡尔积 2 过滤)，没有添加外部行这个阶段，所以没有下过订单以及city<>N’Madrid’的客户被过滤掉了，由此可以清晰看出on条件的起作用的阶段，帮助我们设计出我们需要的谓词

过滤(on   where   having)