Thread: joins?
Hello.
I am a bit new to databases. just still learning.
I havent got it right, but wht is the difference between a joint and a where clause?
specially "join on ".
--
ICQ: 15605359 Bicho
=^..^=
First, they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Gandhi.
-------------------------------気検体の一致------------------------------------
暑さ寒さも彼岸まで。
恋にししょうなし。恋はしあんの他。
アン アン アン とっても大好き
On Wed, 6 Mar 2002 11:57:57 -0500 "Gregory Wood" <gregw@com-stock.com> wrote: i do joins on a foreign key. i.e. from (table1 left join table2 on table1.id=table2.fk_table1_id) left join table3 on table1.id=table2.fk_table1_id... do you know if postgres takes any advantage of that? or if there is an easier way to write it? (i.e. tell it to join on foreign keys constraints) > Explicit joins allow you to order the joins any way you want. When join > conditions are in the WHERE clause, you don't have any control over the > order in which the tables are joined. > > Additionally, when you do an explicit join, you can perform other types of > joins, such as an inner join, outer join, etc. > > Personally I prefer doing explicit joins because it makes my SQL more > readable to me; all the glue that joins the tables together is in the FROM, > and the WHERE clause just limits the rows that I want returned. > > Greg > > ----- Original Message ----- > From: "David Eduardo Gomez Noguera" <davidgn@servidor.unam.mx> > To: "postgres" <pgsql-general@postgresql.org> > Sent: Wednesday, March 06, 2002 10:40 AM > Subject: [GENERAL] joins? > > > > Hello. > > I am a bit new to databases. just still learning. > > I havent got it right, but wht is the difference between a joint and a > where clause? > > specially "join on ". > > > > -- > > ICQ: 15605359 Bicho > > =^..^= > > First, they ignore you. Then they laugh at you. Then they fight you. Then > you win. Mahatma Gandhi. > > -------------------------------気検体の一 > 致------------------------------------ > > 暑さ寒さも彼岸まで。 > > 恋にししょうなし。恋はしあんの他。 > > アン アン アン とっても大好き > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > -- ICQ: 15605359 Bicho =^..^= First, they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Gandhi. -------------------------------気検体の一致------------------------------------ 暑さ寒さも彼岸まで。 恋にししょうなし。恋はしあんの他。 アン アン アン とっても大好き
Explicit joins allow you to order the joins any way you want. When join conditions are in the WHERE clause, you don't have any control over the order in which the tables are joined. Additionally, when you do an explicit join, you can perform other types of joins, such as an inner join, outer join, etc. Personally I prefer doing explicit joins because it makes my SQL more readable to me; all the glue that joins the tables together is in the FROM, and the WHERE clause just limits the rows that I want returned. Greg ----- Original Message ----- From: "David Eduardo Gomez Noguera" <davidgn@servidor.unam.mx> To: "postgres" <pgsql-general@postgresql.org> Sent: Wednesday, March 06, 2002 10:40 AM Subject: [GENERAL] joins? > Hello. > I am a bit new to databases. just still learning. > I havent got it right, but wht is the difference between a joint and a where clause? > specially "join on ". > > -- > ICQ: 15605359 Bicho > =^..^= > First, they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Gandhi. > -------------------------------気検体の一 致------------------------------------ > 暑さ寒さも彼岸まで。 > 恋にししょうなし。恋はしあんの他。 > アン アン アン とっても大好き > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
> i do joins on a foreign key. > i.e. > from (table1 left join table2 on table1.id=table2.fk_table1_id) left join table3 on table1.id=table2.fk_table1_id... You don't have to repeat the "ON" conditions from one join to the next. Simply join tables to whatever tables have already been joined: from (table1 left join table2 on table1.id=table2.fk_table1_id) left join table3 on table2.id=table3.fk_table2_id... > do you know if postgres takes any advantage of that? or if there is an easier way to write it? > (i.e. tell it to join on foreign keys constraints) I'm not sure exactly what you mean by take advantage of it. As far as an easier way to write it, there is no syntax to say "I want you to join across foreign keys"*. Which is good, because you can have multiple foreign keys referencing a table, which would really confuse things. You'll just have to tell it what keys to join across. * There is a way to perform a "natural" join, *if* both the primary and foreign key have the same field name. I'm afraid I don't have time to confirm the syntax (I never use this feature), but I believe it is: table1 natural join table2 natural join table3 I'm not sure if you can combine natural and inner joins... perhaps someone who uses natural joins could care to elaborate? Greg
"Gregory Wood" <gregw@com-stock.com> writes:
> I'm not sure if you can combine natural and inner joins... perhaps someone
> who uses natural joins could care to elaborate?
Sure you can. NATURAL JOIN is just a shorthand for an inner join on
matching column names.
regards, tom lane