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