Oracle (+) outer join and constant values
I'm going to explain this by using equivalent "ANSI JOIN" syntax:
Option 1
SELECT *FROM TXNLEFT JOIN CHK ON TXN.CHK_ID = CHK.CHK_IDWHERE TXN.CURRENT = 'Y'AND CHK.CURRENT = 'Y'
Option 2
SELECT *FROM TXNLEFT 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.
.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.
Join option 1 will consider only those rows where CHK.CURRENT = 'Y'. Therefore, if the transaction has no check, CHK.CURRENT will be NULL and the row will not be in the result set.
Join option 2 will consider those rows where CHK.CURRENT, if there was a check, is 'Y'. If the transaction has no check, 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,CHKWHERETXN.CHK_ID = CHK.CHK_ID(+)and TXN.CURRENT = 'Y'and CHK.CURRENT(+) = 'Y'MINUSSelect *FROM TXN,CHKWHERETXN.CHK_ID = CHK.CHK_ID(+)and TXN.CURRENT = 'Y'and CHK.CURRENT = 'Y'