StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POShould I use SQL JOIN or IN Clause?
text
Body
copied!<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)" " -> Seq Scan on employee (cost=0.00..16.10 rows=610 width=102)" " -> Hash (cost=85.07..85.07 rows=118 width=4)" " -> HashAggregate (cost=83.89..85.07 rows=118 width=4)" " -> Hash Semi Join (cost=45.27..83.60 rows=118 width=4)" " Hash Cond: (emp_proj.project_id = p.project_id)" " -> Seq Scan on emp_proj (cost=0.00..31.40 rows=2140 width=8)" " -> Hash (cost=45.13..45.13 rows=11 width=4)" " -> Nested Loop (cost=0.00..45.13 rows=11 width=4)" " -> Index Scan using employee_pkey on employee e (cost=0.00..8.27 rows=1 width=4)" " Index Cond: (employee_id = 123)" " -> 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)" " -> Index Scan using employee_pkey on employee e (cost=0.00..8.27 rows=1 width=4)" " Index Cond: (employee_id = 123)" " -> Hash Join (cost=60.61..102.84 rows=118 width=102)" " Hash Cond: (b.employee_id = c.employee_id)" " -> Hash Join (cost=36.89..77.49 rows=118 width=8)" " Hash Cond: (b.project_id = p.project_id)" " -> Seq Scan on emp_proj b (cost=0.00..31.40 rows=2140 width=8)" " -> Hash (cost=36.75..36.75 rows=11 width=8)" " -> Seq Scan on emp_proj p (cost=0.00..36.75 rows=11 width=8)" " Filter: (employee_id = 123)" " -> Hash (cost=16.10..16.10 rows=610 width=102)" " -> 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload