On Tue, Sep 21, 2010 at 4:30 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Ogden <
lists@darkstatic.com> writes:
> 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
SELECT
tr.id, tr.sid
FROM
test_registration tr
WHERE
tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid
AND EXISTS(SELECT 1 FROM test_registration_result r
WHERE tr.id = r.test_registration_id)
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')
