title: SQL "IN subquery" when subquery can be NULL
body: <p>I have a query that needs to return results that are NOT matched in a sub query.  The sub query can return an empty result, so I need to set a  default value (say 0) if the sub query returns an empty set to prevent <code>IN (NULL)</code> which always returns another NULL.</p>
  
  <p>For example</p>
  
  <pre><code>SELECT * FROM example_table WHERE id NOT IN (subquery_that_selects_ids)
  </code></pre>
  
  <p><code>subquery_that_selects_ids</code> can return a set of integers, i.e. (1,2,5,6)
  or an empty set if subquery finds no matching results.</p>
  
  <p><code>COALESCE</code> doesn't work here, since the sub query will likely return more than one result.</p>
  
  <p>Solutions need to work in SQLite or postgresql.
  How can I prevent the sub query from returning an empty set?</p>
  
  <hr>
  
  <p>Everyone is telling me that the query should work as written.  And you are all correct.  The query is being built by Rails3's AREL, as I was about to post the full query here I noticed that AREL was putting NULL in for an empty set when using array conditions.</p>
  
  <p>I.E.  My query in rails looked like:</p>
  
  <pre><code>Object.where("id NOT IN (?)", Object.where(other_conditions).select(:id))
  </code></pre>
  
  <p>when <code>Object.where(other_conditions)</code> evaluated to <code>[]</code> the <code>?</code> was being replaced with <code>NULL</code></p>
  
  <p>So I re-write the query to look like:</p>
  
  <pre><code>Object.where("id NOT IN (" + Object.where(other_conditions).select(:id).to_sql + ")")
  </code></pre>
  
  <p>Problem solved.</p>
  
  <p>I'm giving credit to @Michael Buen, but also upvoting anyone who told me the query would work as written, since they are correct.  Thanks to @OMG Ponies and @Ted Elliott especially!</p>