Oracle(+)外部联接和常量值 [英] Oracle (+) outer join and constant values

问题描述

我遇到了一个问题，无法解决如何正确配置联接的问题.我使用的报表软件利用了Oracle数据库的WHERE子句中的(+)指示符.我有两个桌子:

I'm running into an issue with which I can't figure out how to correctly configure a join. I'm using reporting software that utilizes the (+) indicators in the WHERE clause for our Oracle database. I have two tables:

检查和交易.一张支票可以有多个交易，但是一个交易不一定要有一个对应的支票.

CHECK and TRANSACTION. A check can have multiple transactions, but a transaction doesn't necessarily have a corresponding check.

两个表均具有指示符，用于标识称为CURRENT的当前记录，该记录为"Y"或"N".

Both tables have indicators identifying current records called CURRENT that is either a 'Y' or 'N'.

加入选项1:

Select *
FROM TXN,CHK
WHERE
TXN.CHK_ID = CHK.CHK_ID(+)
and TXN.CURRENT = 'Y'
and CHK.CURRENT = 'Y'

加入选项2:

Select *
FROM TXN,CHK
WHERE
TXN.CHK_ID = CHK.CHK_ID(+)
and TXN.CURRENT = 'Y'
and CHK.CURRENT(+) = 'Y'

这些联接产生不同的结果，我似乎无法弄清楚将额外的外部联接指示符应用于CHK.CURRENT字段会产生什么影响.带有额外指示符的查询会产生更大的结果集.有人可以帮忙解释一下这是怎么回事吗?

These joins produce different results, and I can't seem to figure out what effect the extra outer join indicator is having when applied to the CHK.CURRENT field. The query with the extra indicator produces a larger result set. Can someone help explain what's going on here?

推荐答案

我将通过使用等效的"ANSI JOIN"语法对此进行解释:

I'm going to explain this by using equivalent "ANSI JOIN" syntax:

SELECT *
FROM TXN
LEFT JOIN CHK 
  ON TXN.CHK_ID = CHK.CHK_ID
WHERE TXN.CURRENT = 'Y'
AND CHK.CURRENT = 'Y'

选项2

SELECT *
FROM TXN
LEFT JOIN CHK 
  ON TXN.CHK_ID = CHK.CHK_ID 
  AND CHK.CURRENT = 'Y'
WHERE TXN.CURRENT = 'Y'

如您所见，在选项1中，在指定LEFT JOIN表表达式之后，即在LEFT JOIN的结果之后，在 之后应用了常量谓词.

As you can see, in option 1, your constant predicates are applied after the LEFT JOIN table expression is specified, i.e. on the result of the LEFT JOIN.

在选项2中，您的常量谓词之一是LEFT JOIN表达式的一部分.

In option 2, one of your constant predicates is part of the LEFT JOIN expression.

LEFT JOIN的想法是，它将返回JOIN表达式的 LEFT 侧的所有行，而不管另一侧是否存在匹配的行(在进行了连接的情况下)谓词.因此，在选项2中，无论是否在CHK中的行中找到CURRENT = 'Y'中的行，都仍返回TXN中的行.这就是为什么您在选项2中获得更多行的原因.

The idea of a LEFT JOIN is that it will return all rows from the LEFT side of the JOIN expression, regardless if there is a matching row on the other side, given the join predicate. So, in option 2, regardless if you find a row in CHK with CURRENT = 'Y' for a row in TXN, the row in TXN is still returned. This is why you get more rows in option 2.

此外，此示例还应解释为什么您应该首选"ANSI JOIN"语法.从维护/可读性的角度来看，您的查询正在做的事情更加清楚.

Also, this example should explain why you should prefer the "ANSI JOIN" syntax. From a maintenance / readability perspective, it is much more clear what your query is doing.

这篇关于Oracle(+)外部联接和常量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！