多个字段上的左连接返回不匹配的行
多个字段上的LEFT JOIN
是否可能选择具有不同值的行作为字段?考虑下面的查询:多个字段上的左连接返回不匹配的行
SELECT A.a aa, A.b ab, B.a ba, B.b bb
FROM A
LEFT JOIN B
ON A.a = B.a
AND A.b = B.b
WHERE A.a = :filter
在这里,我希望aa
和ba
都等于:filter
在任何时候(除非空值的时候)。
我目前有一个问题,我在我的结果中得到了行,我不期望,我必须将条件放在WHERE
子句中。我仍然在研究sqlFiddle来重现问题。将链接到它,一旦我有它的工作(但也许那么我会自己理解这个问题)
更改LEFT JOIN
到INNER JOIN
也解决了这个问题。但我不明白为什么,真的想知道它背后的逻辑。
有趣的是,如果我过滤B.a
是不是空(effictively转动左连接到内部连接)我得到预期的一致结果到的内部连接。但没有这个检查,我在选定的B.a
字段中有数字值。他们为什么会用IS NOT NULL
进行过滤,但会显示数值? Oracle查询优化器是否有点可疑?
添加/ * + no_query_transformation * /再次给出正确的结果。不同版本的
执行计划:
-
原始查询:
SELECT STATEMENT NESTED LOOPS OUTER TABLE ACCESS A BY INDEX ROWID INDEX A_B_FK RANGE SCAN VIEW PUSHED PREDICATE B FILTER TABLE ACCESS B BY INDEX ROWID INDEX B_C_FK RANGE SCAN
-
查询与
+no_query_transformation
:SELECT STATEMENT VIEW NESTED LOOPS OUTER VIEW A TABLE ACCESS A BY INDEX ROWID INDEX A_B_FK RANGE SCAN VIEW VIEW B TABLE ACCESS B FULL
我将不顾我的脖子:
这是Oracle优化器的bug导致优化器错误解释查询的意图在改造查询到的东西更高效的工艺(合并视图等) 。
我们可以说这是一个错误,因为no_query_transformation提示会将结果更改为预期的结果。我不熟悉任何预期会改变除执行计划之外的任何提示 - 当然不是结果。
这些类型的“错误结果”错误最常出现在具有内联性能视图的复杂查询中。
谢谢,这解释了它。不解决它(我必须改变查询使用'WHERE'子句),但解释它:) – knittl 2014-09-18 07:36:23
这种行为听起来完全是前pected:这是一个LEFT JOIN的功能,并通过将其修改为INNER JOIN来证明它。
基本上,LEFT JOIN表示(在这种情况下)返回匹配WHERE子句的所有行,以及从b匹配到所选a的所有b,但如果没有b匹配任何选定的b然后返回一个无用的值为b的值。
将其更改为INNER JOIN将不会返回没有任何b的a。你也可以通过排除where子句中的空b列来获得同样的效果。
这只是LEFT加入行为的方式。
是的 - 这正是你应该期待的。当我读到这个问题,这正是knittl期望的,但不是他所得到的;-) – 2014-09-11 14:38:00
这不是我读的,恐怕。例如'有趣的是,如果我过滤B.a不为空(有效地将左连接变为内连接),我会得到与内连接相同的结果。“这听起来真的让我觉得他没有孩子,并且没有期待他们;这就是使用内部连接修复问题的原因。 – 2014-09-11 14:40:40
引用:“在这里,我希望aa和ba都等于:在任何时候过滤(除非涉及空值)。” – 2014-09-11 14:42:00
我的简短回答是否定的。我无法复制(11g/XE)。是否有可能提供可重现的代码示例? – 2014-09-11 13:43:14
@JensKrogsboell:感谢您抽出时间。我仍然试图用sqlFiddle重现,但我无法这样做。在Oracle 10g(10.2.0.4.0)上遇到这个问题 – knittl 2014-09-11 13:45:07
一个长镜头可能会尝试提示/ * + no_query_transformation * /。如果这改变了行为,我会说它是优化器做了一个糟糕的工作,猜测你想做什么。从来没有看到,虽然简单的查询。 – 2014-09-11 13:47:14