Thread: more information on JOINs
Where can I get more information and examples on using JOINs. I have been reading over the Chapter 2 on from the interactive user docs but I need a little more in the way of examples. I am having a problem understanding when I would need a LEFT or RIGHT JOIN. Please, a 'point' in the right (no pun intended) direction would be appreciated. Ted
On Sat, Sep 22, 2001 at 08:11:03AM -0400, postgresql wrote: > Where can I get more information and examples on using JOINs. I > have been reading over the Chapter 2 on from the interactive user > docs but I need a little more in the way of examples. I am having a > problem understanding when I would need a LEFT or RIGHT JOIN. > > Please, a 'point' in the right (no pun intended) direction would be > appreciated. > > Ted > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > end of the original message Take a look at the reference manual, part I "SQL Commands": http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-select.html Francesco Casadei
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. Please, if someone has a good example...... Ted tedpet@symcom.com -----Original Message----- From: Francesco Casadei <f_casadei@libero.it> To: postgresql <pgsql@symcom.com> Date: Sat, 22 Sep 2001 17:40:57 +0200 Subject: Re: [SQL] more information on JOINs > On Sat, Sep 22, 2001 at 08:11:03AM -0400, postgresql wrote: > > Where can I get more information and examples on using JOINs. I > > have been reading over the Chapter 2 on from the interactive user > > docs but I need a little more in the way of examples. I am having a > > problem understanding when I would need a LEFT or RIGHT JOIN. > > > > Please, a 'point' in the right (no pun intended) direction would be > > appreciated. > > > > Ted > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > end of the original message > > Take a look at the reference manual, part I "SQL Commands": > > http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-s elect > .html > > Francesco Casadei
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.