Comparing two SQL queries when using IN

Question

I have two tables, for example the Employee and Project tables:

Employee (id, dept, joining_date)
Project (emp_id, project)

With Project having foreign key from Employee table. I have to query on project and dept and return Employee in the order of their joining_date. Which query will work faster on big data set on the queries below?

select * from Employee where id in (select p.emp_id from Project p join Employee e on p.emp_id = e.id where p.project = 'project1' and e.dept = 'dept1') order by joining_date

select * from Employee where id in (select p.emp_id from Project p join Employee e on p.emp_id = e.id where p.project = 'project1' and e.dept = 'dept1') and dept = 'dept1' order by joining_date

Or is there any better and simpler way to do so?

Answer 1

The outer query using the IN() expression serves no purpose is entirely unnecessary. This will produce the output you need:

select e.*
from Project p 
inner join Employee e on p.emp_id = e.id 
where p.project = 'project1' and e.dept = 'dept1'
order by joining_date