comparing efficieny of sql query - using windows function and a subquery

Question

I want to understand if my query is correct and if it's efficient than 1st query

Write a query to display the employee id, first name and last name, SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.

SAMPLE employee table:

1st Query

SELECT  employee_id,  first_name, last_name,  salary AS SalaryDrawn,  
ROUND((salary -(SELECT AVG(salary) FROM employees)),2) AS AvgCompare,  
CASE  WHEN salary >= 
(SELECT AVG(salary) 
FROM employees) THEN 'HIGH'  
ELSE 'LOW'  
END AS SalaryStatus 
FROM employees;

My Answer using a CTE and windows function:

WITH cte AS 
(SELECT  employee_id,  first_name, last_name,  salary,
ROUND(AVG(Salary) OVER (),2) AS avgSal 
FROM employees)

Select employee_id, first_name, last_name, salary, ROUND((e.salary - avgSal ,2) AS AvgCompare,  
CASE  WHEN salary >= avgSal THEN 'HIGH'  
ELSE 'LOW'  
END AS SalaryStatus 
FROM cte

@Charlieface: Do you mind explaining why? Is it because subquery would be calculated for every row in case of first query and that's why the second is more efficient? — K_Learner
I wouldn't necessarily say it will be calculated per row, it often is though. But often the compiler will arrange it as a spool, and save that precalculated value. But you would still be scanning the table 3 times instead of once. — Charlieface

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2021-01-07T23:10:35

I want to understand if my query is correct

Create some sample data, and the expected results. Run the query and compare.

and if it's efficient than 1st query

Look at the query plan and the resource utilization.

SET STATISTICS TIME ON

SET STATISTICS IO ON

Are an easy ways to see the query resource utilization.

comparing efficieny of sql query - using windows function and a subquery

1 Answers