Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: 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
    mb

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is really easy to explain

    your LEFT JOIN query is like this --
    Code:
      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:
    Code:
      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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    20
    Old syntax behavior: WHERE clause is applied to the tables being joined, then performed the outer join.

    _msd_

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •