CODEWITHSUNDEEP
mysqljoin
Leonard Peng
Leonard Peng

Reputation: 55

Is Inner Join needed when operating on one table?

There is a table Employee as below:

enter image description here

I need finds out employees who earn more than their managers and I figure out two methods:

  1. SELECT a.Name AS Employee FROM Employee a, Employee b WHERE a.Salary > b.Salary AND a.ManagerId = b.Id;
  2. SELECT a.Name AS Employee FROM Employee a INNER JOIN Employee b ON a.Salary > b.Salary AND a.ManagerId = b.Id;

Both of them work well and have close speed.

What's the difference between them? Thanks.

Upvotes: 0

Views: 33

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Those queries are equivalent. But you should use the join syntax instead of commas in the from clause. INNER JOIN ON vs WHERE clause

Here's an alternative option which might have a better performance using exists:

select e.Name AS Employee
from employee e
where exists (
   select 1
   from employee e2
   where e.managerid = e2.id and e.salary > e2.salary
)

Upvotes: 1

Related Questions