Oracle (+) outer join and constant values

Question

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.

Both tables have indicators identifying current records called CURRENT that is either a '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'

Join option 2:

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

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?

Answer 1

I'm going to explain this by using 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 LEFT JOIN table expression is specified, i.e. on the result of the LEFT JOIN.

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

How does LEFT JOIN work?

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.

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.

Answer 2

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

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

while the second is equivalent to

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 the outer join but then all the rows where chk.current was NULL get filtered out. In the second case, the chk.current = 'Y' condition doesn't filter out any rows, it just controls whether a matching row is found in chk or whether a left outer join is performed.