Is Inner Join needed when operating on one table?

Question

There is a table Employee as below:

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

SELECT a.Name AS Employee FROM Employee a, Employee b WHERE a.Salary > b.Salary AND a.ManagerId = b.Id;
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.

Borh queries are identical, but the first one uses the now deprecated pre ANSI-92 join sintax (I call it sintax rather than syntax, because many gurus on this site consider it a sin to put join conditions in the `WHERE` clause). — Tim Biegeleisen, Aug 05 '16 at 03:57
But i think , both are correct but only for simple quires, For big queries Join will be better, Join not only uses for filter but also for joining to table in efficient way , But ' where ' only uses for filters. — Amitesh Kumar, Aug 05 '16 at 04:00
They are same. However the most preferred Syntax is: SELECT staff.Name FROM Employee AS staff INNER JOIN Empolyee AS manager ON staff.ManagerId = manager.Id WHERE staff.Salary > manager.Salary — SIDU, Aug 05 '16 at 04:01

score 1 · Accepted Answer · edited May 23 '17 at 12:00

1

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
)

edited May 23 '17 at 12:00

Community

answered Aug 05 '16 at 03:56

sgeddes

1 Answers1