Left Outer join and an additional where clause

Question

I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause on a field from the right-hand table, but.... I still want a row from the left-hand table to be returned for each record in the left-hand table even if the condition in the where clause isn't met. Is there a way of doing this?

Answer 1

Yes, put the condition (called a predicate) in the join conditions

   Select [stuff]    From TableA a        Left Join TableB b            On b.Pk = a.Pk                -- [Put your condition here, like this]                And b.Column = somevalue 

The reason this works is because the query processor applies conditions in a where clause after all joins are completed, and the final result set has been constructed. So, at that point, a column from the a table on the outer side of a join that has null in a a column you have established a predicate on will be excluded.

Predicates in a join clause are applied before the two result sets are "joined". At this point all the rows on both sides of the join are still there, so the predicate is effective.

Answer 2

You just need to put the predicate into the JOIN condition. Putting it into the WHERE clause would effectively convert your query to an inner join.

For Example:

... From a Left Join b on a.id = b.id and b.condition = 'x'