Leonard Peng Leonard Peng - 4 months ago 10
MySQL Question

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.

Answer

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
)