title: SQL where clause for left outer join
body: <p>I have a problem with a view I want to create. I have two tables joined in a left outer join, say <code>tableA</code> and <code>tableB</code>, where <code>tableB</code> is left outer joined.</p>
  
  <p>I want to select only those rows from table B where state equals 4, so I add <code>WHERE state = 4</code> to my query. Now the result set is trimmed quite a bit because all rows without a matching row in <code>tableB</code> are removed from the result (since state isn't 4 for those rows). I also tried <code>WHERE state = 4 OR state IS NULL</code>, doesn't work either (since <code>state</code> technically isn't <code>NULL</code> when there is no state). </p>
  
  <p>So what I need is a <code>WHERE</code> statement which is only evaluated when there actually is a row, does such a thing exist? </p>
  
  <p>If not I see two options: <code>join (SELECT * FROM tableB WHERE state = 4)</code> instead of table B, or create a view with the same <code>WHERE</code> statement and join that instead. What's the best option performance wise? </p>
  
  <p>This is SQL Server 2008 R2 by the way.</p>