Re: Odd query behavior...

On Tue, 2001-11-20 at 23:51, Gregory Brauer wrote:

> I have a question about the behavior of SELECT with multiple
> tables.  Take this example... 4 tables, 2 related by 1 many-to-many
> table, and 1 unrelated table:
...
> CREATE TABLE TGroup (
>      id SERIAL PRIMARY KEY,
>      name VARCHAR(16) UNIQUE );
>
> CREATE TABLE TUser (
>      id SERIAL PRIMARY KEY,
>      username VARCHAR(16) UNIQUE,
>      password VARCHAR(16) );
>
> CREATE TABLE TUnrelated (
>      id SERIAL PRIMARY KEY,
>      something VARCHAR(16) UNIQUE );
...
> INSERT INTO TUser (username, password) VALUES ('bob', 'god');
> INSERT INTO TUser (username, password) VALUES ('fred', 'sex');
> INSERT INTO TGroup (name) VALUES ('user');
> INSERT INTO TGroup (name) VALUES ('luser');
...
> test=# select TUser.username from TUser, TGroup;
>   username
> ----------
>   bob
>   bob
>   fred
>   fred
> (4 rows)

The select is a cross join (or whatever the correct term is), so this
result is (almost) expected. But should not two of them be NULL?

...
> test=# select TUser.username from TUser, TUnrelated;

*I think* that should have resulted in two rows (its still is a cross
join).

// Jarmo