Subquery in the SELECT List
Add a per-row subquery column showing each employee's distance from the company average.
Overview
A scalar subquery (one that returns a single value) can appear in the SELECT
list and is evaluated for each row. Here we append a column showing how far each
employee's salary is above or below the company average.
The Query
Employees & Departments
Loading database engine...
Step-by-Step Breakdown
(SELECT AVG(salary) FROM employees)— a scalar subquery returning the single company average; repeated (constant) on every row.salary - (…)— the per-employee difference from that average.ORDER BY diff_from_avg DESCputs the highest earners relative to average first.
Scalar subqueries must return one row
If the subquery returns more than one row or column, the query errors. Use
LIMIT 1 or an aggregate to guarantee a single value.
Variations
Show each movie's budget vs the genre average:
Movies (Movies/Actors/Ratings)
Loading database engine...
Common Mistakes
- Subquery returns multiple rows. A non-aggregated, un-correlated subquery
in
SELECTmust yield exactly one value. - Recomputing per row inefficiently. A constant subquery is re-run per row in naive plans; a CTE is often clearer.
- Correlation errors. In the genre example, forgetting
m2.genre = m.genrecompares against the global average instead of the genre average.