SQL where clause for left outer join

Question

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

I want to select only those rows from table B where the state is 4, so I am adding WHERE state = 4 to my query. Now the result set is truncated quite a bit, because all rows without the corresponding row in tableB are removed from the result (since the state is not 4 for these rows). I also tried WHERE state = 4 OR state IS NULL does not work either (since state technically not NULL when there is no state).

So, I need a WHERE statement, which is evaluated only when there really is a string, 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 attach it. What is the best performance version?

This, by the way, is SQL Server 2008 R2.

Answer 1

You put 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 

Answer 3

Even simpler than a subquery, extends the on clause, for example:

 select * from TableA a left join TableB b on a.b_id = b.id and b.state = 4 

All rows from Table A will appear, and only those from TableB that indicate state 4.

SQL Server is likely to execute the view extended on and the subquery in exactly the same way. Therefore, performance should be small.

Answer 4

Alternative approach: (1) an internal join with table B, where the state is 4, (2) antijoin in table B to find rows that do not exist, (3) combine the results:

 SELECT A1.ID, A1.colA, B1.ColB FROM tableA AS A1 INNER JOIN tableB AS B1 ON A1.ID = B1.ID AND B1.state = 4 UNION SELECT A1.ID, A1.colA, '{{MISSING}}' AS ColB FROM tableA AS A1 WHERE NOT EXISTS ( SELECT * FROM tableB AS B1 WHERE A1.ID = B1.ID ); 

As an alternative:

 SELECT A1.ID, A1.colA, B1.ColB FROM tableA AS A1 JOIN tableB AS B1 ON A1.ID = B1.ID AND B1.state = 4 UNION SELECT ID, colA, '{{NA}}' AS ColB FROM tableA WHERE ID IN ( SELECT ID FROM tableA EXCEPT SELECT ID FROM tableB );