Skip to content
SQLSimplified
subquery

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

Loading playground environment...

Step-by-Step Breakdown

  1. (SELECT AVG(salary) FROM employees): a scalar subquery returning the single company average; repeated (constant) on every row.
  2. salary - (…): the per-employee difference from that average.
  3. ORDER BY diff_from_avg DESC puts 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:

Loading playground environment...

Common Mistakes

  • Subquery returns multiple rows. A non-aggregated, un-correlated subquery in SELECT must 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.genre compares against the global average instead of the genre average.

Related examples

CTE vs Subquery: Same Result, Clearer Code
cte
Rewrite a nested subquery as a CTE to compute above-average-salary employees.
Derived Table: Subquery in the FROM Clause
subquery
Wrap an aggregated subquery in FROM to rank products by total revenue.
Subquery in the WHERE Clause
subquery
Filter employees whose salary exceeds the average salary of their department.

Cite this resource

SQLSimplified. "Subquery in the SELECT List Example". Available at: https://sqlsimplified.online/examples/subquery-in-select