Search code examples
sqlsql-serverdatabasesqlperformance

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?

Solution

  • 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.