Odd query behavior... - Mailing list pgsql-general

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

CREATE TABLE TGroupTUserLink (
     tgroup_id int4 NOT NULL,
     tuser_id int4 NOT NULL,
     UNIQUE (tgroup_id, tuser_id),
     UNIQUE (tuser_id, tgroup_id),
     FOREIGN KEY (tgroup_id) REFERENCES TGroup(id) ON DELETE CASCADE,
     FOREIGN KEY (tuser_id) REFERENCES TUser(id) ON DELETE CASCADE
);

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');


If I do a select from the TUser table, I get what is expected:

test=# select TUser.username from TUser;
  username
----------
  bob
  fred
(2 rows)



However if I do a select from both the TUser and TGroup tables,
I get a product, even though I have expressed no joins:

test=# select TUser.username from TUser, TGroup;
  username
----------
  bob
  bob
  fred
  fred
(4 rows)



And finally, if I do a select from the TUser and TUnrelated tables,
I get nothing, even though I have specified no joins:

test=# select TUser.username from TUser, TUnrelated;
  username
----------
(0 rows)



What is going on here?  Why am I getting "products"?  It appears
to have something to do with how tables are related, but I don't
understand the full reasoning behind it.  I have done futher tests
with a table that is related to a table that is related to yet
another table that I am querying, and I no longer get a product,
just the results I expect.

Any help would be appreciated...

Thanks.

Greg Brauer
greg@wildbrain.com