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 commentusing (id);
you'd only get those customers that had comments because thereis no comment row for the rest of the customers.

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