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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | postgresql.org | 2005-05-16 15:27:28 | Re: problems with postgresql.msi (installing 8.0.2) |
Previous Message | Stephan Szabo | 2005-05-16 13:34:49 | Re: concepts? |