title: Oracle (+) outer join and constant values
body: <p>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:</p>
  
  <p>CHECK and TRANSACTION. A check can have multiple transactions, but a transaction doesn't necessarily have a corresponding check.</p>
  
  <p>Both tables have indicators identifying current records called CURRENT that is either a 'Y' or 'N'.</p>
  
  <p>Join option 1:</p>
  
  <pre><code>Select *
  FROM TXN,CHK
  WHERE
  TXN.CHK_ID = CHK.CHK_ID(+)
  and TXN.CURRENT = 'Y'
  and CHK.CURRENT = 'Y'
  </code></pre>
  
  <p>Join option 2:</p>
  
  <pre><code>Select *
  FROM TXN,CHK
  WHERE
  TXN.CHK_ID = CHK.CHK_ID(+)
  and TXN.CURRENT = 'Y'
  and CHK.CURRENT(+) = 'Y'
  </code></pre>
  
  <p>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?</p>