title: Query with a UNION sub-query takes a very long time
body: <p>I've been having an odd problem with some queries that depend on a sub query. They run lightning fast, until I use a UNION statement in the sub query. Then they run endlessly, I've given after 10 minutes. The scenario I'm describing now isn't the original one I started with, but I think it cuts out a lot of possible problems yet yields the same problem. So even though it's a pointless query, bear with me!</p>
  
  <p>I have a table: </p>
  
  <pre><code>tblUser - 100,000 rows
  tblFavourites - 200,000 rows
  </code></pre>
  
  <p>If I execute:</p>
  
  <pre><code>SELECT COUNT(*) 
  FROM tblFavourites 
  WHERE userID NOT IN (SELECT uid FROM tblUser);  
  </code></pre>
  
  <p>… then it runs in under a second. However, if I modify it so that the sub query has a UNION, it will run for at least 10 minutes (before I give up!)</p>
  
  <pre><code>SELECT COUNT(*) 
  FROM tblFavourites 
  WHERE userID NOT IN (SELECT uid FROM tblUser UNION SELECT uid FROM tblUser);  
  </code></pre>
  
  <p>A pointless change, but it should yield the same result and I don't see why it should take any longer? </p>
  
  <p>Putting the sub-query into a view and calling that instead has the same effect.</p>
  
  <p>Any ideas why this would be? I'm using SQL Azure.</p>
  
  <hr>
  
  <p>Problem solved. See my answer below.</p>
  
  <hr>