Help understanding these two outputs from correlated sub-query exercise

SQL
1

Hello,

I need some help understanding the difference between these two queries.

The first query i’m not using a correlated sub-query but still a sub-query non-the-less. I believe I am asking

  • list the employees who have a salary greater than the average of all employees.

SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees)

Nocorrelated
Nocorrelated551×203 7.43 KB

Below I am using a correlated sub-query version of the above but my expectation is that the two would bring back the same number of employees. Both outputs miss out some employees with greater than avarage salaries.

SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id)

employee_id first_name last_name job_title salary reports_to office_id
37851 Sayer Matterson Statistician III 98926 37270 1
40448 Mindy Crissil Staff Scientist 94860 37270 1
56274 Keriann Alloisi VP Marketing 110150 37270 1
67009 North de Clerc VP Product Management 114257 37270 2
67370 Elladine Rising Social Worker 96767 37270 2
72540 Guthrey Iacopetti Office Assistant I 117690 37270 3
76196 Mirilla Janowski Cost Accountant 119241 37270 3
84791 Hazel Tarbert General Manager 93760 37270 4
95213 Cole Kesterton Pharmacist 86119 37270 4
98374 Estrellita Daleman Staff Accountant IV 70187 37270 5
115357 Ivy Fearey Structural Engineer 92710 37270 5

In addition, the correlated sub-query seems to return a value LESS than the average of all employees, Estrellita Daleman has a salary of 70,187 but a simple AVG(salary) FROM employees tells me that the average is 82,502. What am I not getting here?

Thank you.

2

I’ve figured it out i think with the help of co-pilot. Mosh didn’t explain this one very well.

What wasn’t clear is that the specific command “WHERE office_id = e.office_id” means that the average will be calculated based on the office_id of each employee record the query looks at. This of course saves doing a long drawn out number of sub-queries that calculate the average of each office_id.