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. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly