title: Should I use SQL JOIN or IN Clause?
body: <p>I have a question about best approach. I am not sure which approach is best when data is considered variable in size.</p>
  
  <p>Consider the following 3 TABLES:</p>
  
  <p><strong>EMPLOYEE</strong> </p>
  
  <p>EMPLOYEE_ID, EMP_NAME</p>
  
  <p><strong>PROJECT</strong></p>
  
  <p>PROJECT_ID, PROJ_NAME</p>
  
  <p><strong>EMP_PROJ</strong> (many to many of above two tables)</p>
  
  <p>EMPLOYEE_ID, PROJECT_ID</p>
  
  <p><strong>Problem</strong>:  Given an EmployeeID,  find ALL the employees of ALL Projects that this Employee is associated with.</p>
  
  <p>I have tried this in two way.. both approaches differ only by few milliseconds no matter what size of data is used.</p>
  
  <pre><code>SELECT EMP_NAME FROM EMPLOYEE
  WHERE EMPLOYEE_ID IN (
      SELECT EMPLOYEE_ID FROM EMP_PROJ    
      WHERE PROJECT_ID IN (
          SELECT PROJECT_ID FROM EMP_PROJ p, EMPLOYEE e
          WHERE p.EMPLOYEE_ID = E.EMPLOYEE_ID 
          AND  E.EMPLOYEE_ID = 123)
  </code></pre>
  
  <p>go</p>
  
  <pre><code>select c.EMP_NAME FROM
  (SELECT PROJECT_ID FROM EMP_PROJ 
    WHERE EMPLOYEE_ID = 123) a
  JOIN 
  EMP_PROJ b
  ON a.PROJECT_ID = b.PROJECT_ID
  JOIN 
  EMPLOYEE c
  ON b.EMPLOYEE_ID = c.EMPLOYEE_ID
  </code></pre>
  
  <p>As of now, I expect around 5000 Employees and Projects each.. but have no idea about what kinda many-many relationship exists.
  Which approach would u recommend?
  thanks!</p>
  
  <p>EDIT:
  Execution Plan of Approach 1</p>
  
  <pre><code>"Hash Join  (cost=86.55..106.11 rows=200 width=98)"
  "  Hash Cond: (employee.employee_id = emp_proj.employee_id)"
  "  -&gt;  Seq Scan on employee  (cost=0.00..16.10 rows=610 width=102)"
  "  -&gt;  Hash  (cost=85.07..85.07 rows=118 width=4)"
  "        -&gt;  HashAggregate  (cost=83.89..85.07 rows=118 width=4)"
  "              -&gt;  Hash Semi Join  (cost=45.27..83.60 rows=118 width=4)"
  "                    Hash Cond: (emp_proj.project_id = p.project_id)"
  "                    -&gt;  Seq Scan on emp_proj  (cost=0.00..31.40 rows=2140 width=8)"
  "                    -&gt;  Hash  (cost=45.13..45.13 rows=11 width=4)"
  "                          -&gt;  Nested Loop  (cost=0.00..45.13 rows=11 width=4)"
  "                                -&gt;  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
  "                                      Index Cond: (employee_id = 123)"
  "                                -&gt;  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
  "                                      Filter: (p.employee_id = 123)"
  </code></pre>
  
  <p>Execution Plan of Approach 2:</p>
  
  <pre><code>"Nested Loop  (cost=60.61..112.29 rows=118 width=98)"
  "  -&gt;  Index Scan using employee_pkey on employee e  (cost=0.00..8.27 rows=1 width=4)"
  "        Index Cond: (employee_id = 123)"
  "  -&gt;  Hash Join  (cost=60.61..102.84 rows=118 width=102)"
  "        Hash Cond: (b.employee_id = c.employee_id)"
  "        -&gt;  Hash Join  (cost=36.89..77.49 rows=118 width=8)"
  "              Hash Cond: (b.project_id = p.project_id)"
  "              -&gt;  Seq Scan on emp_proj b  (cost=0.00..31.40 rows=2140 width=8)"
  "              -&gt;  Hash  (cost=36.75..36.75 rows=11 width=8)"
  "                    -&gt;  Seq Scan on emp_proj p  (cost=0.00..36.75 rows=11 width=8)"
  "                          Filter: (employee_id = 123)"
  "        -&gt;  Hash  (cost=16.10..16.10 rows=610 width=102)"
  "              -&gt;  Seq Scan on employee c  (cost=0.00..16.10 rows=610 width=102)"
  </code></pre>
  
  <p>So looks like the Execution plan of Approach 2 is slightly better, because the 'cost' is 60 as opposed to 85 of approach 1. Is that the right way to analyze this?</p>
  
  <p>How does one know it will hold true even for all sorts of many-many combinations?</p>