Sql Performance join Vs co-related Subquery

TABLE : employee (id,mid,join_date)

Question – Find all employees who joined before their managers

Query 1:

Select E1.id
From Employee E1 JOIN Employee E2 ON E1.mid=E2.id
Where E1.join_date < E2.join_date

Query 2:

Select E1.id
From Employee E1
Where E1.join_date < (Select E2.join_date From Employee E2 where E2.id=E1.mid)

Which of them is correct? If both correct, then how is the performance?

Answer

They are both correct.

Performance questions need to be tested on your data on your system. However, with a primary key on employee(id), I would expect both to have very similar performance.

You can check the execution plans to see if there is any difference.