Обсуждение: JOINS...
Whats the purpose of joins ?? is it the same as selecting from mulitple tables ? -- ____________________________________________ http://www.operamail.com Get OperaMail Premium today - USD 29.99/year Powered by Outblaze
Hi novice, >Whats the purpose of joins ?? is it the same as selecting from mulitple tables ? > > In a Relational Database liek Postgresql, you try the store data which as few redundancy as possible. The result is that you store the data in many tables - eg. persons and adresses will be separate (this process is called Normalisation). Now, to put the corresponding data together again, you need a join between the tables that belong together. In order to work, you need at least (numberOfTables -1) Join-Conditions, for exaple SELECT person.name, address.line1 FROM person, address WHERE ' Here comes the Join Condition address.person_id = person.person_id If you do not specify the Join Condition, you will get all possible combinations of persons and addresses, the so called cartesian Product of the 2 tables. Cheers, Dani
Hi Dani, I understand that part :) but what about the extra options you can specify in the join condition, like 'outer' , 'inner', 'full' etc i dont see a circumstance when you would use the extra options... ----- Original Message ----- From: Dani Oderbolz <oderbolz@ecologic.de> Date: Fri, 09 May 2003 10:29:51 +0200 To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] JOINS... > Hi novice, > > >Whats the purpose of joins ?? is it the same as selecting from mulitple tables ? > > > > > In a Relational Database liek Postgresql, you try the store data which > as few redundancy as possible. > The result is that you store the data in many tables - eg. persons and > adresses will be separate > (this process is called Normalisation). > Now, to put the corresponding data together again, you need a join > between the tables that belong together. > In order to work, you need at least (numberOfTables -1) Join-Conditions, > for exaple > > SELECT > person.name, > address.line1 > FROM > person, > address > WHERE > ' Here comes the Join Condition > address.person_id = person.person_id > > If you do not specify the Join Condition, you will get all > possible combinations of persons and addresses, > the so called cartesian Product of the 2 tables. > > Cheers, Dani > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- ____________________________________________ http://www.operamail.com Get OperaMail Premium today - USD 29.99/year Powered by Outblaze
> > >Hi Dani, > >I understand that part :) > >but what about the extra options you can specify in the join >condition, like 'outer' , 'inner', 'full' etc > >i dont see a circumstance when you would use the extra options... > > Hi, these options are useful when your tables are partially related. For example, you have a table products and a table color. Now some products don't have a color (like a computer Program). If you would state this join: Select product.name, color.name from product, color where product.color_id = color.color_id (This is an INNER Join, by the Way) You would NOT retreive the products whose color_id is NULL. Thats where the OUTER JOIN comes in - but this is - as far as i know- not directly supported in Postgres. You have to do this: Select product.name, color.name from product, color where product.color_id = color.color_id UNION Select product.name, 'No color' from product where color_id IS NULL; You find a nice introduction on all this here: http://spot.colorado.edu/~marangak/main.html (But they speak about Oracle, not Postgresql) I hope this gives you some clues. Cheers, Dani
On Tue, 2003-05-13 at 08:01, Dani Oderbolz wrote: > Thats where the OUTER JOIN comes in - but this is - as far as i know- > not directly supported in Postgres. > You have to do this: > > Select product.name, color.name > from > product, > color > where product.color_id = color.color_id > UNION > Select product.name, 'No color' > from > product > where > color_id IS NULL; This advice is out of date. PostgreSQL does indeed support outer joins (since 7.1, I think). SELECT product.name, COALESCE(color.name,'No color') FROM product LEFT OUTER JOIN color ON product.color_id = color.color_id; -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Watch ye and pray, lest ye enter into temptation. The spirit truly is ready, but the flesh is weak." Mark 14:38