Problem with SQL Server Left Outer Join: Help needed

I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?

SQL with Left Outer join operator:

select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#)
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#

SQL with *= operator
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat , CurrentWeekFinMetrics
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)

For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.

Thanks and Regards
mbthis is really easy to explain

your LEFT JOIN query is like this -- from WeeklyMetricsFormat
LEFT
JOIN CurrentWeekFinMetrics
on WeeklyMetricsFormat.Line#
= CurrentWeekFinMetrics.Line#
where CurrentWeekFinMetrics.WeekEndingDate
= '10/09/04'
and CurrentWeekFinMetrics.[Hub+]
= 'Amstelveen'
what happens is, the optimizer first performs the left outer join according to the ON clause, then applies the WHERE conditions to the result

if there are any left rows which have no matching right rows, then all the columns from the right table will be null

therefore the WHERE conditions applied to columns of the right table effectively eliminate those unmatched rows (since nothing equals null)

what you need to do is move the conditions on the right table up into the ON clause: from WeeklyMetricsFormat
LEFT
JOIN CurrentWeekFinMetrics
on WeeklyMetricsFormat.Line#
= CurrentWeekFinMetrics.Line#
and CurrentWeekFinMetrics.WeekEndingDate
= '10/09/04'
and CurrentWeekFinMetrics.[Hub+]
= 'Amstelveen' the reason it works in the older syntax, where you have the asterisk on only one of the conditions, not all of them, is: i have no idea|||Old syntax behavior: WHERE clause is applied to the tables being joined, then performed the outer join.

_msd_