一句话结论:4、针对左连接(右连接)的左(右)表进行条件限定等价于转换为内连接

浏览: 2681

环境:BIEE11.1.1.7... MSSQL....ORACLE

背景:

某天和同事针对BIEE中设置左连接,然后又在前端限定了左表的查询条件后,左连接功能失效的问题进行讨论验证。

发现确实如标题所说。

忍不住让人感慨,原来又是记忆出了错,看来好久不用的功能,就会慢慢模糊直至遗忘。

之间一直没有时间抽空整理。今天翻箱倒柜找到以前学习MSSQL分享的文章,再次学习下,贴出来与大家共享。

PS: 以下内容可以直接下载附件查看;以下select过程从MSSQL的角度所写,仅适用于MSSQL2000+,虽然和oralce的原理类似,不要生搬硬套至oracle。


LEFTJOIN引发的SELECT过程探究

 

前置条件:

create table a(a1 int,a2 varchar(10)) 
go
create table b(b1 int,b2 varchar(10))
go
insert into a(a1,a2) values(1,'a')
insert into a(a1,a2) values(1,'aa')
insert into a(a1,a2) values(1,'aaa')
insert into a(a1,a2) values(2,'aaaaa')
insert into a(a1,a2) values(3,'aaaa')
insert into b(b1,b2) values(1,'b')
insert into b(b1,b2) values(1,'bb')
insert into b(b1,b2) values(2,'bbb')
go
select * from a
select * from b

Clipboard Image.png

问题:

select a.a1,a.a2,b.b2
from a left join b on a.a1=b.b1 and a.a1=1

select a.a1,a.a2,b.b2
from a left join b on a.a1=b.b1
where a.a1=1

的查询结果相同么?

Clipboard Image.png

答案:不同

疑问:不是都应该是A表的a1字段等于1的才会显示在结果集么?

探究:SELECT逻辑查询处理的各个阶段:

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>)(5-1) <select_list>
(1) FROM (1-J) <left_table><join_type> JOIN <right_table> ON <on_predicate>
         |(1-A)<left_table> <apply_type> APPLY <right_table_expression> AS<alias>
         |(1-P)<left_table> PIVOT (<pivot_specification>) AS <alias>
         |(1-U)<left_talbe> UNPIVOT (<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;

 

PS: 这里图片复制不过来,需要的TX自己下附件吧

 

(1) FROM          FROM阶段标识出查询的来源表,处理表运算符。每个表运算符也会应用一系列子阶段。例如,在联接运算中涉及的阶段是(1-J1)笛卡尔积、(1-J2)ON筛选器和(1-J3)添加外部行。FROM阶段生成虚拟表VT1。

(1-J1)笛卡尔积           这个阶段对表运算符涉及的两个表执行笛卡尔积(交叉联接),生成虚拟表VT1-J1。

(1-J2)ON筛选器                  这个阶段对VT1-J1中的行根据ON子句(<on_predicate>)中出些的谓词进行筛选。只有让该谓词取值为TRUE的行,才能插入VT1-J2中。

(1-J3)添加外部行                如果指定了OUTERJOIN(相对于CROSS  JOIN 或INNER  JOIN),则将保留表(preservedtable)中没有找到匹配的行,作为外部行添加到VT1-J2中,生成VT1-J3。

(2) WHERE                 这个阶段根据在WHERE子句中出些的谓词(<where_predicate>)对VT1中的行进行筛选。只有让谓词计算结果为TRUE的行,才会插入VT2。

(3) GROUP BY           按照GROUP BY 子句中指定的列名列表,将VT2中的行进行分组,生成VT3。最终,每个分组只有一个结果行。

(4) HAVING                根据HAVING 子句中出现的谓词(<having_predicate>)对VT3中的分组进行筛选。只有让谓词计算结果为TRUE的组,才会插入VT4。

(5) SELECT                  处理SELECT 子句中的元素,产生VT5。

(5-1)计算表达式               计算SELECT列表中的表达式,生成VT5-1。

(5-2) DISTINCT                   删除VT5-1中的重复行,生成VT5-2。

(5-3) TOP           根据ORDER BY子句定义的逻辑排序,从VT5-2中选择前面指定数量或百分比的行,生成表VT5-3。

(6) ORDER BY            根据ORDER BY 子句中指定的列名列表,对VT5-3中的行进行排序,生成游标VC6。

 

从以上select逻辑过程可以看到前面两个查询的区别是前者虽然在“(1-J2)ON筛选器”中筛选掉了a表a1字段不等于1的,但在“(1-J3)添加外部行”又被添加回来,而后者虽然在“(1-J2)ON筛选器”中未筛选掉a表a1字段不等于1的纪录,但在后面的“(2) WHERE”阶段中筛选调了a表a1字段不等于1的纪录。故前者的查询有包含a表a1字段不等于1的纪录,而后者的查询不包含a表a1字段不等于1的纪录。

 

实例解析:

初始化数据:

create table TmpVendor(nVendorID int,sDeptNO varchar(20))
go
create table TmpOrder(nVendorID int,sPaperNO varchar(50))
go
insert into TmpVendor(nVendorID,sDeptNO) values(1,'20')
insert into TmpVendor(nVendorID,sDeptNO) values(2,'20')
insert into TmpVendor(nVendorID,sDeptNO) values(3,'30')
insert into TmpVendor(nVendorID,sDeptNO) values(4,'20')
insert into TmpOrder(nVendorID,sPaperNO) values(1,'aa001')
insert into TmpOrder(nVendorID,sPaperNO) values(1,'aa002')
insert into TmpOrder(nVendorID,sPaperNO) values(1,'aa003')
insert into TmpOrder(nVendorID,sPaperNO) values(1,'aa004')
insert into TmpOrder(nVendorID,sPaperNO) values(1,'aa005')
insert into TmpOrder(nVendorID,sPaperNO) values(2,'aa006')
insert into TmpOrder(nVendorID,sPaperNO) values(2,'aa007')
insert into TmpOrder(nVendorID,sPaperNO) values(2,'aa008')
insert into TmpOrder(nVendorID,sPaperNO) values(3,'aa009')
GO

Clipboard Image.png

select TOP 10 v.nVendorID,COUNT(o.sPaperNO) as papernum
from TmpVendor as v
left join TmpOrder as o on v.nVendorID=o.nVendorID
where v.sDeptNO='20'
group by v.nVendorID having COUNT(o.sPaperNO)<5
order by papernum

解析SELECT过程:

1、 from阶段

1-J1笛卡尔积(执行交叉联接)

from TmpVendor as v

left join TmpOrder as o

结果将得到4×9行的VT1-JI

    v.nVendorID      v.sDeptNO      o.nVendorID      o.sPaperNO   
1 20 1 aa001
1 20 1 aa002
1 20 1 aa003
1 20 1 aa004
1 20 1 aa005
1 20 2 aa006
1 20 2 aa007
1 20 2 aa008
1 20 3 aa009
2 20 1 aa001
2 20 1 aa002
2 20 1 aa003
2 20 1 aa004
2 20 1 aa005
2 20 2 aa006
2 20 2 aa007
2 20 2 aa008
2 20 3 aa009
3 30 1 aa001
3 30 1 aa002
3 30 1 aa003
3 30 1 aa004
3 30 1 aa005
3 30 2 aa006
3 30 2 aa007
3 30 2 aa008
3 30 3 aa009
4 20 1 aa001
4 20 1 aa002
4 20 1 aa003
4 20 1 aa004
4 20 1 aa005
4 20 2 aa006
4 20 2 aa007
4 20 2 aa008
4 20 3 aa009

1-J2应用ON筛选器(联接条件)

on v.nVendorID=o.nVendorID的逻辑值

    逻辑值      v.nVendorID      v.sDeptNO      o.nVendorID      o.sPaperNO   
TRUE 1 20 1 aa001
TRUE 1 20 1 aa002
TRUE 1 20 1 aa003
TRUE 1 20 1 aa004
TRUE 1 20 1 aa005
FALSE 1 20 2 aa006
FALSE 1 20 2 aa007
FALSE 1 20 2 aa008
FALSE 1 20 3 aa009
FALSE 2 20 1 aa001
FALSE 2 20 1 aa002
FALSE 2 20 1 aa003
FALSE 2 20 1 aa004
FALSE 2 20 1 aa005
TRUE 2 20 2 aa006
TRUE 2 20 2 aa007
TRUE 2 20 2 aa008
FALSE 2 20 3 aa009
FALSE 3 30 1 aa001
FALSE 3 30 1 aa002
FALSE 3 30 1 aa003
FALSE 3 30 1 aa004
FALSE 3 30 1 aa005
FALSE 3 30 2 aa006
FALSE 3 30 2 aa007
FALSE 3 30 2 aa008
TRUE 3 30 3 aa009
FALSE 4 20 1 aa001
FALSE 4 20 1 aa002
FALSE 4 20 1 aa003
FALSE 4 20 1 aa004
FALSE 4 20 1 aa005
FALSE 4 20 2 aa006
FALSE 4 20 2 aa007
FALSE 4 20 2 aa008
FALSE 4 20 3 aa009
只有让<on_predicate>为TRUE的那些行,才会插入到VT1-J2中,如下示:
逻辑值 v.nVendorID v.sDeptNO o.nVendorID o.sPaperNO
TRUE 1 20 1 aa001
TRUE 1 20 1 aa002
TRUE 1 20 1 aa003
TRUE 1 20 1 aa004
TRUE 1 20 1 aa005
TRUE 2 20 2 aa006
TRUE 2 20 2 aa007
TRUE 2 20 2 aa008
TRUE 3 30 3 aa009

1-J3添加外部行

这一步只在外联接(outer join)中才会发生,本例中把左表TmpVendor在VT1-J2中被过滤掉的行及VT1-J2中的行生成VT1-J3(即VT1),如下示:

    v.nVendorID      v.sDeptNO      o.nVendorID      o.sPaperNO   
1 20 1 aa001
1 20 1 aa002
1 20 1 aa003
1 20 1 aa004
1 20 1 aa005
2 20 2 aa006
2 20 2 aa007
2 20 2 aa008
3 30 3 aa009
4 20 NULL NULL

2、WHERE阶段

对上一步返回的虚拟表中的所有行应用where筛选器,只有让<where_predicate>逻辑条件为TRUE的行,才会组成这一步要返回的虚拟表(VT2)

本例中应用以下过滤条件

where v.sDeptNO='20'

这一步返回的虚拟表VT2如下示:

    v.nVendorID      v.sDeptNO      o.nVendorID      o.sPaperNO   
1 20 1 aa001
1 20 1 aa002
1 20 1 aa003
1 20 1 aa004
1 20 1 aa005
2 20 2 aa006
2 20 2 aa007
2 20 2 aa008
4 20 NULL NULL

3、GROUP BY 阶段

在本阶段,根据<group_by_specification>指定的列表,将上一步返回的虚拟表中的行分组,本例中应用以下的分组条件

group by v.nVendorID

得到的虚拟表VT3如下示:

    分组      v.nVendorID      v.sDeptNO      o.nVendorID      o.sPaperNO   
1 1 20 1 aa001
1 20 1 aa002
1 20 1 aa003
1 20 1 aa004
1 20 1 aa005
2 2 20 2 aa006
2 20 2 aa007
2 20 2 aa008
4 4 20 NULL NULL

4、HAVING阶段

HAVING筛选器对上一步返回的虚拟表中的组进行筛选。只有使<having_predicate>逻辑条件取值为TRUE的组,才会组成这一步返回的虚拟表(VT4)的一部分。

在本例中应用以下筛选条件

having COUNT(o.sPaperNO)<5

分组1因为包含5张订单被删除。生成的虚拟表VT4如下示:

    分组      v.nVendorID      v.sDeptNO      o.nVendorID      o.sPaperNO   
2 2 20 2 aa006
2 20 2 aa007
2 20 2 aa008
4 4 20 NULL NULL

PS:在这里指定的是COUNT(o.sPaperNO)而不是count(*) ,这一点很重要。因为本例中联接是外联接,没有订单的供应商也会作为外部行被添加到结果集。count(*)会把外部行也统计在内,错误的将供应商编号为4的订单数统计为1。而COUNT(o.sPaperNO)则可以准确的统计每个供应商的订单数,为供应商编号为4生成期望的统计值0。和其他聚合函数一样,count(<expression>)将忽略NULL值。

PS:子查询不能作为聚合函数的输入。例如,不能使用Having sum((select……))>5

5、SELECT阶段

5-1计算表达式

本例中将应用以下语句:

select v.nVendorID,COUNT(*) as papernum

将得到虚拟表VT5-1表如下所示:

v.nVendorID papernum
2 3
4 0

5-2应用DISTINCT子句

本例中将忽略步骤5-2,因为在查询中没有指定DISTINCT子句。即使指定了,它也不会删除任何行(虚拟表VT5-1中本身没有重复行)

5-3应用TOP选项

TOP选项是T-SQL特有的一项功能,循序指定要返回的行数或者百分比(向上取整)。根据查询的ORDER BY子句来选择指定数量的行。最终生成虚拟表VT5-3(VT5)如下示:

v.nVendorID papernum
4 0
2 3

6、ORDER BY 排序阶段

这一步按ORDER BY子句中的列名列表对上一步返回的行进行排序,返回游标VC6。ORDER BY子句是以为可以重用SELECT列表中创建的列别名的步骤。

如果指定了DISTINCT,则ORDER BY子句中的表达式只能访问上一步返回的虚拟表(VT5)。如果没有指定DISTINCT,则ORDER BY子句中的表达式可以访问SELECT阶段的输入和输出虚拟表。也就是说,可以在ORDER BY子句中指定任何可以在SELECT子句使用的表达式。即,可以按不在最后返回的结果集中的表达式来进行排序。

本例中应用了以下排序条件:

order by papernum

直接访问VT5,生成游标VC6如下示:

    v.nVendorID      papernum   
4 0
2 3

 

 

输出得到如下结果集

    nVendorID      papernum   
4 0
2 3

 

 

推荐 4
本文由 百分百 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

1 个评论

代码可以放到代码格式里面,看起来好看。。

要回复文章请先登录注册