Should I use SQL JOIN or IN Clause?

I have a question about best approach. I am not sure which approach is best when data is considered variable in size.

Consider the following 3 TABLES:

EMPLOYEE

EMPLOYEE_ID, EMP_NAME

PROJECT

PROJECT_ID, PROJ_NAME

EMP_PROJ (many to many of above two tables)

EMPLOYEE_ID, PROJECT_ID

Problem: Given an EmployeeID, find ALL the employees of ALL Projects that this Employee is associated with.

I have tried this in two way.. both approaches differ only by few milliseconds no matter what size of data is used.

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)

go

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

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!

EDIT:
Execution Plan of Approach 1

```
"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)"
"

In SQL Server, with a few assumptions like "those fields can't contain NULLs", those queries should give the almost the same plan.

But also consider the type of join you're doing. An IN clause like this is a Semi Join, not an Inner Join. An Inner Join can project onto multiple rows, thereby giving duplicates (compared to using IN or EXISTS). So you may want to consider this behaviour when choosing how you write your query.

StackExchange Database Administrators Q#7378, answer score: 13