Re: Need help understanding LEFT JOIN.

On 29 Oct 2001, "John Peterson" <johnp_at_azstarnet.com> spake and said:

> Thank you so much for the info! I gave it a try, and it
> surely appears to work! I don't quite understand it, but
> I'll surely give this a try.

In your example, since the tests against constants in the WHERE clause do not participate in the join itself -- no left join '(+)' -- the tests are effectively applied post-join.

You could also, in addition to including the constant tests in the join criteria, correct your query by changing the tests. You need to test for nullity as well as testing for the constant value:

  select t.ElementId ,

         tColA.Data  as DataColA ,
         tColB.Data  as DataColB ,
         tColC.Data  as DataColC
  from Test t     ,
       Test tColA ,
       Test tColB ,
       Test tColC
   where t.Field     = 'Primary'
     and t.ElementId = tColA.ElementId(+)
     and t.ElementId = tColB.ElementId(+)
     and t.ElementId = tColC.ElementId(+)
     and (    tColA.Field = 'ColA'     -- filter matching  rows
           OR tColA.ElementID is NULL  -- but accept unmatched.
         )
     and (    tColB.Field = 'ColB'     -- filter matching  rows
           OR tColB.ElementIDis NULL   -- but accept unmatched.
         )
     and (    tColC.Field = 'ColC'     -- filter matching  rows
           OR tColC.ElementID is NULL  -- but accept unmatched.
         )

--