Re: Trouble with an outer join

honestly, i don't even know why your version results in rows having "null"
as d.RealmName as these would not match the clause (t.TagName=d.TagName AND
d.RealmName='Horror')

when doing left joins, i'll always stick to these rules:

in the ON clause, put the fields that link the tables together (i.e. foreign
keys). this will result in a "virtual" result table where the left fields
are coming from table1 and the right fields from table2, containing the
values if there is an corresponding entry or else containing null.

then in the WHERE clauses, i filter this "virtual" result table as if it is
a real existing table with null-able fields. of course what the query
optimizer does in the background and how the results are really put together
is beyond my knowledge. also it *might* be faster to include some of the
clauses in one place or another...

cheers,
thomas

----- Original Message -----
From: "Martin Foster" <martin(at)ethereal-realms(dot)org>
To: "Thomas" <me(at)alternize(dot)com>; "PostgreSQL Novice List"
<pgsql-novice(at)postgresql(dot)org>
Sent: Monday, September 26, 2005 2:40 AM
Subject: Re: [NOVICE] Trouble with an outer join

> me(at)alternize(dot)com wrote:
>> this should work just fine:
>>
>> 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' or d.RealmName IS NULL)
>> ORDER BY t.TagName;
>>
>> cheers,
>> thomas
>>
>
> What's the difference versus yours above and the one I just corrected?
> Anything unexpected that I should expect from mine?
>
> 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;
>
> Martin Foster
> martin(at)ethereal-realms(dot)org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>