Who earns more than the average employee?
NOVA’s finance lead corners Harry near the coffee machine with a question that sounds simple right up until you try to answer it.
Who earns more than the average employee?
Good question to actually think through, not just answer. What do you need to know before you can compare anyone to ‘average’?
I already know AVG(). So can't I just say WHERE salary > AVG(salary)?
Let's ask the database.
Running…
The database refuses. It’s not being difficult — WHERE decides which individual rows survive, one row at a time, before SQL has grouped anything together. AVG() needs a pile of rows to average over. At the point WHERE is asking “should you stay?”, there is no pile yet — just one row, being asked one question. There’s nothing for AVG() to work with.
So the average has to be figured out somewhere else, first, as its own small answer — and then handed to WHERE as something it can compare against. That’s what a subquery is: a query nested inside another query, whose result becomes an input to the outer one.
Answer the small question first. Then use the answer.
The small question, on its own, is nothing new:
Running…
One row, one number. Now drop that exact query inside the parentheses where AVG(salary) failed a moment ago — this is called a scalar subquery, because it returns exactly one value, which means it’s allowed anywhere a single value is expected:
SELECT name, department, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);Running…
Eight employees clear the company-wide average of $112,812.50. Worth noticing: Sam Okafor and Priya Nair are tied at $128,000, and Tom Becker and Ken Watanabe are tied at $121,000 — the subquery ran once, produced one number, and every single row got compared against that same number. That’s the whole trick. The inner query never re-runs per employee here; it just answers its own question once, and the outer query reuses the answer.
Try it: What happens if the subquery matches nothing at all?
Run your query to see results here.
NOVA doesn’t have a Legal department, so the inner query has zero rows to average — and AVG() over zero rows is NULL, not 0. Every employee’s salary then gets compared against NULL, which (remember WHERE phone = NULL?) is never true. The query runs fine and quietly returns nothing. No error, no crash — just an empty result, for a very specific and findable reason.
Finance is back with a follow-up: not which employees earn above the company average, but which departments do — departments where the department’s own average salary is higher than the average salary across all of NOVA. Return each qualifying department with its average salary.
Hint 1
You need one number that represents 'the whole company', calculated once, to compare every department's own average against. Where would you get that number?
Hint 2
GROUP BY department to get one average per department, then filter the groups — not the rows — with HAVING. A subquery can sit inside HAVING just as easily as it sits inside WHERE.
Hint 3
SELECT department, ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
Solution
Running…
Engineering, Product, and Sales all pay above the company average; Support and Marketing don’t. Same idea as the main example, just one level up: the subquery still runs once and produces one number, it’s just being compared against a group’s average instead of a row’s salary. Subqueries aren’t only a WHERE thing — they can sit inside HAVING, inside SELECT, even inside FROM. That flexibility is wonderful right up until you nest a few of them and have to reread your own query — which is exactly the problem the next chapter solves.
A subquery answers a smaller question first, and its result becomes something the outer query can use — a single value in place of WHERE or HAVING needing a raw aggregate it can’t compute row-by-row.