An employee who has a higher salary than the AVERAGE from his department is optimized
We only have a table named EMPLOYEESALARY in our database with the following three columns:
Employee_ID, Employee_Salary, Department_ID
Now I have to select every employee with a higher salary than the AVERAGE one from his department. How to do it?
I know this is a duplicate question, but the best solution I found was this:
SELECT * from employee join (SELECT AVG(employee_salary) as sal, department_ID
FROM employee GROUP BY Department_ID) as t1
ON employee.department_ID = t1.department_ID
where employee.employee_salary > t1.sal
Can we optimize it further and do it without a subquery?
Link:
CHOOSE each employee with a higher salary than the AVERAGE one from their department
Employees with higher salaries than their average?
Find schema here to check: SQL Fiddle
source to share
Is it possible to do this without a subquery?
Not that I can think. If the condition was >=
, then it would work
SELECT TOP 1 WITH TIES *
FROM employee
ORDER BY CASE
WHEN employee_salary >= AVG(employee_salary)
OVER (
PARTITION BY Department_ID) THEN 0
ELSE 1
END
But this is not an optimization , and it will not work correctly for a condition >
if no employee has a salary that exceeds the average (all employees in a department have the same salary)
Can we optimize it further?
You can shorten the syntax a bit with
WITH T AS
(
SELECT *,
AVG(employee_salary) OVER (PARTITION BY Department_ID) AS sal
FROM employee
)
SELECT *
FROM T
WHERE employee_salary > sal
but he still has to do the same job.
Assuming there are already valid indexes on the base table, the only way to avoid some extra work in SELECT
would be to pre-compute the grouped SUM
and COUNT_BIG
in the indexed view grouped by Department_ID
(so the average is cheap).
source to share
A more optimal form might be:
select e.*
from (select e.*, avg(employee_salary) over (partition by department_id) as avgs
from employee e
) e
where employee_salary > avgs;
This (like other versions) can use an index on employee(department_id, employee_salary)
. The final where
probably shouldn't use an index because it selects a lot of rows.
source to share