Re: more information on JOINs

On Sat, 22 Sep 2001, postgresql wrote:

>
> Thanks for this reference, I have been here. I guess my problem is
> that I don't understand a need for a 'join' that is returning NULLs. If I
> understand <left> <right> <outer> JOINs correctly, they are returning
> columns that contain NULLs.
>
> An example please where I would want this result. I have created a
> job tracking system that includes invoicing, collections reporting,
> aging. When I first learned to do the INNER JOIN ON, I replaced the
> processing that I was doing in my client app and let Postgres do it.
> So now I am examining and studying the other joins. I just can not
> figure out why I would EVER want one. Which leads me to think that I
> just don't understand them.

Okay, the reason for left/right joins are for cases like where you have a
details table that does not include a row for every parent and you want
to get a list of all parents and get detail information for those that
have it.

Say you have a customer table, and a table with comments about customers.
Not every customer has a comment. Now you want to get every customer
and their comment if they have one.

If you did a query like:
select customer.name, comment.text from customer inner join comment
using (id);
you'd only get those customers that had comments because there
is no comment row for the rest of the customers.

If you do:
select customer.name, comment.text from customer left outer join
comment using (id);
you'd get all customers, with either their comment or a NULL for
the comment text.