On Tue, Sep 21, 2010 at 4:30 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ogden <lists@xxxxxxxxxxxxxx> writes:Seeing that tr.id is a primary key, I think you might be a lot better
> SELECT tr.id, tr.sid
> FROM
> test_registration tr,
> INNER JOIN test_registration_result r on (tr.id = r.test_registration_id)
> WHERE.
> tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid
> GROUP BY tr.id, tr.sid
off if you avoided the inner join and group by. I think what you really
want here is something like
WHEREAND EXISTS(SELECT 1 FROM test_registration_result r
tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid
WHERE tr.id = r.test_registration_id)
regards, tom lane
Could you explain the logic behind why this structure is better than the other? Is it always the case that one should just always use the 'exists(select 1 from x...)' structure when trying to strip rows that don't join or is it just the case when you know that the rows which do join are a fairly limited subset? Does the same advantage exist if filtering rows in the joined table on some criteria, or is it better at that point to use an inner join and add a where clause to filter the joined rows.
select table1.columns
from table1, table2
where table1.column = 'some_value'
AND table2.column = 'some_other_value'
versus
select table1.columns
from table1
where table1.column = 'some_value'
and table2.column ='some_other_value')
