Tuning up a SQL query, (query optimization)

Question

I am trying to tune a SQL query which have IN clause in the query.

I tried replacing IN with Join and looked at the query plans.Both are looking similar in execution times, but the result is different.Can someone help me regarding this? I am using a shop database in pgadmin III. Thanks in advance. ORIGINAL QUERY:

SELECT person.id
FROM   SHOP.person
WHERE  person.id IN (SELECT personid
                     FROM   SHOP.contactperson
                     WHERE  companyid = 5); 

to

SELECT person.id
FROM   SHOP.person
       JOIN SHOP.contactperson
         ON person.id = contactperson.id
WHERE  contactperson.companyid = 5; 

EDITED: NOW THIS QUERY RETURNS CORRECT RESULTS:

SELECT person.id
FROM   SHOP.person
       JOIN SHOP.contactperson
         ON person.id = contactperson.personid
WHERE  contactperson.companyid = 5;

I was using contactperson.id instead of contactperson.id, and when I change it to the correct query it gave me correct results.

Answer 1

SELECT pe.id
FROM   SHOP.person pe
WHERE  EXISTS (
   SELECT *     
   FROM   SHOP.contactperson cp
   WHERE cp.personid = pe.id
   AND  cp.companyid = 5
   ); 

Answer 2

Your join clause is not using the same fields as your original query. You should use personid from the contactperson table.

SELECT person.id
FROM SHOP.person 
Join SHOP.contactperson
ON person.id = contactperson.personid 
WHERE contactperson.companyid = 5;