Thread: Automatic joins?
According to the Wikipedia article on PostgreSQL (http://en.wikipedia.org/wiki/PostgreSQL), it has a feature for automatic joins: "SELECT u.* FROM user u, address a WHERE a.city='New York' and a.user_name=u.user_name In Postgres the relationship between users and addresses can be explicity defined. Once defined the address becomes a property of the user, so the search above can be simplified greatly to: SELECT * FROM user WHERE address.city='New York'" Is the a load of nonsense, or am I doing something wrong? I did define a foreign key, but it doesn't work.
I think what is being referred to is that other types can be embedded directly into the columns of a table. So in the second query the "address" referred to is a column of the user table, not a table in itself. I think Postgres may have supported this but PostgreSQL certainly hasn't for a very long time, if at all. Although apparently it wouldn't be that difficult to resurrect, just that nobody has done it. Just think about how it would be returned through the libpq interface if you did a SELECT * on the table. Good luck, On Sat, Jan 17, 2004 at 11:47:18PM +0000, Leif K-Brooks wrote: > According to the Wikipedia article on PostgreSQL > (http://en.wikipedia.org/wiki/PostgreSQL), it has a feature for > automatic joins: > > "SELECT u.* FROM user u, address a WHERE a.city='New York' and > a.user_name=u.user_name > > In Postgres the relationship between users and addresses can be > explicity defined. Once defined the address becomes a property of the > user, so the search above can be simplified greatly to: > > SELECT * FROM user WHERE address.city='New York'" > > Is the a load of nonsense, or am I doing something wrong? I did define a > foreign key, but it doesn't work. > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
Mensaje citado por Leif K-Brooks <eurleif@ecritters.biz>: > According to the Wikipedia article on PostgreSQL > (http://en.wikipedia.org/wiki/PostgreSQL), it has a feature for > automatic joins: > > "SELECT u.* FROM user u, address a WHERE a.city='New York' and > a.user_name=u.user_name > > In Postgres the relationship between users and addresses can be > explicity defined. Once defined the address becomes a property of the > user, so the search above can be simplified greatly to: > > SELECT * FROM user WHERE address.city='New York'" I find this totally untrue (un less I missundertud it). Check this out: siprebi=> select version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 7.3.4 on sparc-unknown-linux-gnu, compiled by GCC sparc-linux-gcc (GCC) 3.3.2 (Debian) (1 row) siprebi=> select a.nombre from admin a, biblioteca b where b.codigo = 3 and b.codigo = a.biblioteca; nombre -------- (0 rows) siprebi=> select a.nombre from admin a, biblioteca b where b.codigo = 3; nombre ------------------- Andres C. Roman Martin Marques Mariano Markowsky (3 rows) siprebi=> select a.nombre from admin a where biblioteca.codigo = 3; NOTICE: Adding missing FROM-clause entry for table "biblioteca" nombre ------------------- Andres C. Roman Martin Marques Mariano Markowsky (3 rows) siprebi=> select a.nombre from admin a; nombre ------------------- Andres C. Roman Martin Marques Mariano Markowsky (3 rows) As you see, no automatic join was performed. Who wrote that article? I see that example, at least poorly written, if not inaccurate. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; ------------------------------------------------------- Martín Marqués | Programador, DBA Centro de Telemática | Administrador Universidad Nacional del Litoral -------------------------------------------------------