From: | Martin Foster <martin(at)ethereal-realms(dot)org> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Trouble with an outer join |
Date: | 2005-09-26 01:06:44 |
Message-ID: | 433749A4.6030205@ethereal-realms.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Stephan Szabo wrote:
>
>
> 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.
I learned how to do joins on an Oracle 7 server. So the use of *= or =*
was the way an outer join was done. Needless to say, the JOIN clause is
a bit different and at times seems a little less then obvious.
However from what you said, this would explain why the addition of AND
d.RealmName='Horror' works as expected. It limits which rows are
joined from the RealmDesign table.
Thanks!
Martin Foster
martin(at)ethereal-realms(dot)org
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-09-26 03:40:39 | Re: View |
Previous Message | Stephan Szabo | 2005-09-26 00:53:38 | Re: Trouble with an outer join |