SQL where clause for left outer join

Question

I have a problem with a view I want to create. I have two tables joined in a left outer join, say tableA and tableB, where tableB is left outer joined.

I want to select only those rows from table B where state equals 4, so I add WHERE state = 4 to my query. Now the result set is trimmed quite a bit because all rows without a matching row in tableB are removed from the result (since state isn't 4 for those rows). I also tried WHERE state = 4 OR state IS NULL, doesn't work either (since state technically isn't NULL when there is no state).

So what I need is a WHERE statement which is only evaluated when there actually is a row, does such a thing exist?

If not I see two options: join (SELECT * FROM tableB WHERE state = 4) instead of table B, or create a view with the same WHERE statement and join that instead. What's the best option performance wise?

This is SQL Server 2008 R2 by the way.

Answer 1

You put the conditions in the on clause. Example:

select a.this, b.that
from TableA a
left join TableB b on b.id = a.id and b.State = 4

Answer 2

You can add state = 4 to the join condition.

select * 
from T1
  left outer join T2
    on T1.T1ID = T2.T1ID and
       T2.state = 4