today we faced an interesting problem regarding left outer joins. i will skip the details and come to the learning part directly. more so because it was more our misunderstanding of joins rather than the problem that made the whole experience interesting.
as we know, left outer join would fill up the non matching records with null values. So when we have filter conditions on the right hand side table (whose values are capable of being null), we should be careful in choosing whether we need those null rows as well.
for example :
select A.x, B,y
from A left outer join B on A.x = B.x
A.y = B.y and
A.y = ‘abc’ and
B.y = ‘abc’
pls note that in some cases you might have to change the last line of the query as
( B.y = ‘abc’ OR B.y is null)