在第二个表上具有过滤条件的Oracle外连接 [英] Oracle outer join with filter condition on the second table
问题描述
是否有条件使结果集与以下两个语句不同?
Is there any condition under which the result sets will be different from the following two statements?
select * from a,b where a.id = b.id and b.name = 'XYZ'
select * from a,b where a.id =b.id(+) and b.name = 'XYZ'
我认为在这两种情况下,它都会从b.name = 'XYZ'
的a和b处引入公共行.所以a.id = b.id(+)
没有任何意义.
I think in both cases it will bring the common rows from a and b where b.name = 'XYZ'
. So a.id = b.id(+)
has no meaning.
推荐答案
否,没有条件下结果集会有所不同.
No, there is no condition under which the result sets will be different.
但是您的假设"a.id = b.id(+)
没有意义"不是100%正确的.它具有含义,因为它定义了连接,否则将是a和b的笛卡尔积,其中所有行都来自a和b.name='XYZ'.
But your assumption "a.id = b.id(+)
has no meaning" is not 100% correct. It has a meaning, because it defines the join, otherwise this would be a cartesian product of a and b with all rows from a and b.name = 'XYZ'.
(+)
无效,因为该语句在语义上"是错误的.在id上进行外部联接在名称上进行联接是没有意义的.
What has no effect is the (+)
, because the statement is "semantically" wrong. It makes no sense to outer join on id but to join on name.
通常需要这样的东西:
select * from a,b where a.id =b.id(+) and b.name(+) = 'XYZ';
在 http://www.sqlfiddle.com/#!4/d19b4上的简短示例/15
这篇关于在第二个表上具有过滤条件的Oracle外连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!