Skip site navigation (1) Skip section navigation (2)

Re: concepts?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "D(dot)C(dot)" <coughlandesmond(at)yahoo(dot)fr>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: concepts?
Date: 2005-05-16 14:22:40
Message-ID: 17374.1116253360@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"D.C." <coughlandesmond(at)yahoo(dot)fr> writes:
> it is this: if I do ..
> test=> select *  from people, job;
> .. why does every entry get displayed seven times (there are seven 
> 'people' in each table) ?

> In other words, why do I *need* to do this ..
> test=> select * from people, job where people.id = job.id ;
> ... in order for every name to be displayed just once ?

The conceptual model of SQL is that "FROM t1, t2" generates the
Cartesian product (cross product) of the two tables --- that is,
you get a join row for every possible combination of rows from
the inputs.  Then the WHERE clause selects out just the rows
you want from the join table.

Of course, a great deal of work goes into making the actual
implementation more efficient than that ;-).  But that's the
theoretical basis.  If you don't write any WHERE then you
get the whole join table.

> test=> select DISTINCT people.nom,people.prenom,job.boite, 
> secteur.description from people, job, secteur where job.secteur_id = 
> secteur.sector_id;

Same problem here: you have an underconstrained join to "people".
Not knowing anything about your data model, I'm not sure if
people.id = job.id is the thing to add or not.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: postgresql.orgDate: 2005-05-16 15:27:28
Subject: Re: problems with postgresql.msi (installing 8.0.2)
Previous:From: Stephan SzaboDate: 2005-05-16 13:34:49
Subject: Re: concepts?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group