Knowledgebase

If you do a left outer join and you loose records of the “left” table, this might be caused by the where clause:

This query will result in all records of table Production.HourGroup, only Production.HourGroup rows that have a correspoding Production.SortPerHour and on Production.SortPerHour.Sortday ‘2009-04-21’ are counted.

select
    u.HourGroup   ,
    sum(isnull(s.Amount,0))
from
    Production.HourGroup u
left outer join
    Production.SortPerHour s on u.HourGroup = s.HourGroup and s.Sortday =  '2009-04-21'
group by u.HourGroup
order by u.HourGroup

But the query below will not result in all records of table Production.HourGroup, because of the where clause.

The where clause is run on the left outer join result. So only the records in table Production.HourGroup with a corresponding SortPerHour on Sortday ‘2009-01-21’ will be listed and counted.

select
    u.HourGroup   ,
    sum(isnull(s.Amount,0))
from
    Production.HourGroup u
left outer join
    Production.SortPerHour s on u.HourGroup = s.HourGroup
where
    s.Sortday = '2009-04-21'
group by u.HourGroup
order by u.HourGroup