External and external Oracle values ​​(+)

Question

I am having a problem with which I cannot figure out how to properly configure the connection. I use reporting software that uses indicators (+) in the WHERE clause for our Oracle database. I have two tables:

CHECK AND OPERATION. A check can have multiple transactions, but a transaction does not have to have a check.

Both tables have indicators identifying the current records, called CURRENT, which are either "Y" or "N".

Join option 1:

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

Attach option 2:

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

These joins lead to different results, and I cannot understand what effect the external indicator of an external join has when applied to the CHK.CURRENT field. A query with an additional indicator gives a larger set of results. Can someone explain what is going on here?

Answer 1

I will explain this using the equivalent ANSI JOIN syntax:

Option 1

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

Option 2

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

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

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

How does LEFT JOIN ?

The idea behind the LEFT JOIN is that it will return all the rows from the LEFT side of the JOIN expression, regardless of whether the corresponding line exists on the other side, given the join predicate. So, in option 2, regardless of whether you find the string CHK with CURRENT = 'Y' for the string in TXN , the string in TXN still returns. This is why you get more lines in option 2.

In addition, this example should explain why you should prefer the ANSI JOIN syntax. In terms of service / readability, it’s much more clear what your request is doing.

Answer 2

. The (+) operator tells Oracle that the predicate is part of an external join, not a filter prefix that can be applied after the join. Using the SQL 99 outer join syntax, the first query is equivalent

 SELECT * FROM txn left outer join chk on( txn.chk_id = chk.chk_id ) WHERE txn.current = 'Y' AND chk.current = 'Y' 

and the second is equivalent

 SELECT * FROM txn left outer join chk on( txn.chk_id = chk.chk_id AND chk.current = 'Y') WHERE txn.current = 'Y' 

Logically, in the first case, you do an outer join, but then all the lines where chk.current were NULL are filtered out. In the second case, the condition chk.current = 'Y' does not filter out any lines, it just controls whether the corresponding line is in chk or if an external connection is performed on the left.

Answer 3

Merge Option 1 will only consider lines where CHK.CURRENT = 'Y'. Therefore, if the transaction does not have a check, CHK.CURRENT will be NULL, and the row will not be in the result set.

The join function 2 will examine those lines where CHK.CURRENT, if there was a check, is "Y". If the transaction is not verified, this test will not be applied and the row will be in the result set.

You can see the difference with this comparison:

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

Answer 4

Compound 1 is equivalent:

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

However, since any rows that are in the result set will have CHK.CURRENT = 'Y' (nonzero value), then these lines must match CHK_ID , and the query is actually equivalent:

 SELECT * FROM TXN INNER JOIN CHK ON ( TXN.CHK_ID = CHK.CHK_ID ) WHERE TXN.CURRENT = 'Y' AND CHK.CURRENT = 'Y' 

Compound 2 is equivalent:

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

You can make merge option 1 equivalent to option 2 using:

 SELECT * FROM TXN LEFT OUTER JOIN CHK ON ( TXN.CHK_ID = CHK.CHK_ID ) WHERE TXN.CURRENT = 'Y' AND ( CHK.CURRENT = 'Y' OR CHK.CHK_ID IS NULL )