Automatic joins?

[Leif K-Brooks]

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.

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFACdpDY5Twig3Ge+YRAp2pAJoCMWcB1r3xNZA7XK8Y6X XkKJJ/PACgsEYC
WZQ9FO9KRWE4wY3qGdmbokk=
=HQeM
-----END PGP SIGNATURE-----

[Martín Marqués]

Mensaje citado por Leif K-Brooks <eu*****@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
-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html