Re: Trouble with an outer join

On Sun, 25 Sep 2005, Martin Foster wrote:

> Stephan Szabo wrote:
>
> >> From the output its pretty clear that the first 10 should have been
> >>omitted for more then one reason.   However they appear every time and
> >>in order to compensate for this, I have the script skip through unneeded
> >>entries manually.
> >>
> >>So what exactly am I doing wrong?
> >
> >
> > AFAIK, conditions like t.TagType='template' in the ON condition of an
> > outer join are not going to constrain the rows from t that are created but
> > instead constrain whether or not a row from d in considered as valid (ie,
> > you're saying to extend with NULLs for TagTypes other than 'template').
> >
> > I think some of those conditions you want in a WHERE clause, possibly all
> > the ones that refer only to t.
> >
>
> SELECT
>      t.TagName       AS "TagName",
>      t.TagType       AS "TagType",
>      d.RealmName     AS "RealmName"
>   FROM ethereal.Tag t
>   LEFT OUTER JOIN ethereal.RealmDesign d
>     ON (t.TagName=d.TagName)
>   WHERE t.TagType='template'
>   AND (t.TagName LIKE 'Realm%'
>     OR  t.TagName LIKE 'Offline%')
>   AND d.RealmName='Horror'
>   ORDER BY t.TagName;
>
> Let's try that change which oddly enough does not do an outer join at
> all.   Here is the sample output:

In the case where the LEFT OUTER JOIN has no row in d to match to a row in
t, the t row is extended by NULLs for the d columns. It does an outer
join, it's just that after that the set is constrained down such that
those rows for which a NULL extended row would not be part of the output.

> Now let's try a variation:
>
> SELECT
>      t.TagName       AS "TagName",
>      t.TagType       AS "TagType",
>      d.RealmName     AS "RealmName"
>   FROM ethereal.Tag t
>   LEFT OUTER JOIN ethereal.RealmDesign d
>     ON (t.TagName=d.TagName AND d.RealmName='Horror')
>   WHERE t.TagType='template'
>   AND (t.TagName LIKE 'Realm%'
>     OR  t.TagName LIKE 'Offline%')
>   ORDER BY t.TagName;
>
> Which allows us to get what we need.   Which gets rather confusing as to
> how to get a join to work exactly like people expect it too.

> Anyone know good documentation on how to determine exactly where to cram
> thing as necesssary?

Unfortunately, I don't know of good documentation that's particularly
detailed and understandable (but admittedly I've not looked carefully).
The spec is fairly precise but mostly incomprehensible.

----

Roughly speaking,
t1 Left outer join t2 on (condition) is defined as:
 select * from tn
  union all
 select * from xn1
where
 tn is the multiset of rows of the cartesian product of t1 and t2 for
which condition is true
 xn1 is the set of rows in t1 that have no row in tn extended with NULLs
to the right (ie, rows in t1 for which no joining to a row in t2 on
condition returned true).

Conditions in WHERE would be then applied to the output of the above.

----

 Conditions in the on clause control whether a row from t1 matches to a
row of t2 and is part of tn or is extended by NULLs and is part of xn1.
 Conditions in the where clause then apply and only allow through rows
that meet the criteria.