I am practicing SQL and came across this issue. I am trying to find the top 3 salaries for each department. The table structure is basic with one employee table with columns (Id,name,Salary,DepartmentId) and a department Table with columns (ID,DepartmentName). In one query I use a join on the departmentId column, but this query takes longer than when I don't use a join and simply use two table as stated in the second query. My understanding was both are one and the same thing. Please shed some light about the difference between these two queries. What makes one faster than the other?

SELECT d.name as Department, e.name as Employee ,e.salary as Salary 
FROM Employee e , Department d 
where e.DepartmentId=d.Id and
    (SELECT COUNT(distinct Salary) 
     FROM Employee 
     WHERE Salary > e.Salary 
     AND DepartmentId=e.DepartmentId) < 3 

The above query works well but when I write it in the similar way it takes long time.

SELECT d.name as Department, e.name as Employee ,e.salary as Salary
FROM Employee e 
JOIN Department d 
ON e.DepartmentId=d.Id 
AND
(SELECT COUNT(distinct Salary) 
 FROM Employee
 WHERE Salary > e.Salary 
 AND DepartmentId=e.DepartmentId) < 3 

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.