title: How can I compare against a possibly null value in oracle?
body: <p>I see a number of variations on this question but they all seem to talk about performance and indexes when asking about comparing null values.</p>
  
  <p>My issue is that I have a nested SELECT that can return null that I am comparing against based on a date parameter.</p>
  
  <pre><code>SELECT a.* 
  FROM Table_One a,
       Table_Two b
  WHERE a.Fieldc IN (SELECT CompareValue from Table_Three cv WHERE inDate between cv.Date_ and SYSDATE)
  AND a.Fielda = b.Fieldb(+)
  </code></pre>
  
  <p>it seems like when the nested select here returns null then the left join messes up.</p>
  
  <p>I'll be happy to include some fake data if it's needed but maybe I'm just missing something.
   What I need to have occur is that the Fieldc comparison only occurs within that date specification.</p>
  
  <p>Thanks for any insight.
  Thanks.</p>
  
  <p>Further information:</p>
  
  <p>Table_One Data: all fields are varchar2</p>
  
  <pre><code>Fielda | Fieldb | Fieldc|
  'aVal' | 'bVal' | 'cVal'|
  'dVal' | 'eVal  | 'fVal'|
  'dVal' | 'fVal  | 'eVal'|
  </code></pre>
  
  <p>Table_Two Data: all fields are varchar2</p>
  
  <pre><code>Fielda | Fieldb | Fieldc|
  'aVal' | 'bVal' | 'cVal'|
  'dVal' | 'fVal  | 'gVal'|
  'dVal' | 'fVal  | 'cVal'|
  </code></pre>
  
  <p>Table_Three Data: CompareValue is varchar2, date_ is date</p>
  
  <pre><code>CompareValue | date_      |
  'fval'       | 2012-09-10 |
  </code></pre>
  
  <p>So if the parameter were today the select returns 'fval' and we get left join correctly. however, when the date parameter is before '2012-09-10' then the nested select is comparing a null and the left join does not happen properly. This is a bit of a simplified version of the original.</p>
  
  <p>Thanks</p>