Обсуждение: 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

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
-------------------------------------------------------