| Subject | Re: Left Outer Join |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2006-04-19T14:12:03Z |
--- In firebird-support@yahoogroups.com, Friedrich Remmert wrote:
which rows should be returned - the where clause kind of 'overrides'
the join clauses - in many cases effectively turning an outer join
into an inner join. Excepting for the cases where you're looking for
rows that are in one table, but not in another, you should never put
anything relating to the right table of a left join in the where clause.
HTH,
Set
> Thank's Svein Erling,No, they're different. Anything put in the where clause determines
>
> I would expect the same results from
>
> > from materialart ma
> > left outer join entsorgungskopf ek
> > on ma.abfallschluessel = ek.abfallschluessel
> > and ma.unterschluessel = ek.unterschluessel
> > left outer join entsorgungsposition ep
> > on ek.lfd_ent_kop_nr = ep.lfd_ent_kop_nr
> > where
> > ep.datum is null
> > or ep.datum between '01.04.2006' and '19.04.2006'
>
> and
>
> > from materialart ma
> > left outer join entsorgungskopf ek
> > on ma.abfallschluessel = ek.abfallschluessel
> > and ma.unterschluessel = ek.unterschluessel
> > left outer join entsorgungsposition ep
> > on ek.lfd_ent_kop_nr = ep.lfd_ent_kop_nr
> > and ep.datum between '01.04.2006' and '19.04.2006'
>
> But that's not right. It look's as if the reference to ep within the
> where clause excludes those records without matches.
>
> Thank You.
> fr.
which rows should be returned - the where clause kind of 'overrides'
the join clauses - in many cases effectively turning an outer join
into an inner join. Excepting for the cases where you're looking for
rows that are in one table, but not in another, you should never put
anything relating to the right table of a left join in the where clause.
HTH,
Set