Scalar Subqueries

So far every query has looked at the data in one pass. But real questions come in layers: 'show me every employee and how their pay compares to the company average.' The average is one number, yet you need it next to every row.

A scalar subquery is a complete SELECT statement tucked inside another query, and it must return exactly one row and one column — a single value. Because it hands back one number, you can drop it anywhere a single value is legal: the SELECT list, a WHERE comparison, or a HAVING filter.

The alternative is to run two separate queries and copy the average into the second by hand. Subqueries let the database do that plumbing for you.

flowchart LR
  O["Outer query<br/>SELECT ..."] --> S["Scalar subquery<br/>(SELECT AVG...)"]
  S -->|"one value"| V["AVG(salary) = 610000"]
  V -->|"injected into each row"| R["Result set"]
  style S fill:#0e7490,color:#fff
  style V fill:#1e293b,color:#fff

The outer query runs once; the scalar subquery runs once and returns one value that is slotted into every row.

The anatomy of a scalar subquery

The rules are strict. A scalar subquery must produce:

One column — two columns means the outer query would not know which value to use.
One row — zero rows turns the value into NULL; more than one row raises an error.

When those conditions hold, the database treats the subquery as a single expression. In the SELECT list it becomes a calculated column; in a WHERE clause it becomes the number you are comparing against.

The subquery is surrounded by parentheses and can be given an alias with AS so the result column has a readable name.

Every employee's salary next to the company average. The subquery runs once and its result is copied into every row.

SELECT name, salary,
  (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

Scalar subqueries in WHERE

Placing a subquery in a WHERE clause lets you filter against an aggregate without needing GROUP BY. The query below keeps only rows whose salary beats the company-wide average.

The comparison salary > (SELECT AVG(salary) FROM employees) is evaluated row by row: the subquery runs once, returns one number, and each row is checked against that same threshold.

This is much cleaner than computing the average yourself and hard-coding it into the query. It also stays correct when the data changes tomorrow.

Filter to above-average earners using a scalar subquery in WHERE.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

flowchart TD
  E[("employees")] -->|"each row"| C{"salary > subquery result?"}
  S["(SELECT AVG(salary) FROM employees)"] -->|"one value"| C
  C -->|yes| K["Kept"]
  C -->|no| D["Dropped"]
  style S fill:#0e7490,color:#fff
  style K fill:#0e7490,color:#fff
  style D fill:#b45309,color:#fff

A WHERE clause with a scalar subquery: every row is compared against the single value the inner query produced.

Scalar subqueries in HAVING

HAVING filters groups after GROUP BY has run, and it can also accept a scalar subquery. The classic use is comparing a group average to the overall average: 'which departments pay better than the company as a whole?'

The subquery sits in the HAVING clause just as it would in WHERE. It runs once, produces one number, and every group's aggregate is tested against it. The query below groups employees by department, computes each department's average salary, and keeps only those groups that beat the company average.

Departments whose average salary is above the company average.

SELECT d.name, AVG(e.salary) AS dept_avg
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees);

flowchart LR
  G["GROUP BY d.name"] --> A["AVG(e.salary) per group"]
  A --> H{"group avg > subquery?"}
  S["(SELECT AVG(salary) FROM employees)"] --> H
  H -->|"yes"| K["Kept"]
  H -->|"no"| D["Dropped"]
  style H fill:#0e7490,color:#fff
  style K fill:#0e7490,color:#fff
  style D fill:#b45309,color:#fff

HAVING filters groups after aggregation, and a scalar subquery provides the threshold.

Exercise

Return every employee's name and salary, plus the company-wide average salary as a third column named company_avg. Use a scalar subquery in the SELECT list.

SELECT name, salary
FROM employees
;

SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees;

Exercise

List the name and salary of every employee who earns more than the company average. Use a scalar subquery in the WHERE clause.

SELECT name, salary
FROM employees
WHERE salary > -- add scalar subquery here
;

SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Exercise

Show each department name and its average salary (dept_avg), but only for departments whose average is strictly greater than the company-wide average. You will need GROUP BY, JOIN, and a scalar subquery in HAVING.

SELECT d.name, AVG(e.salary) AS dept_avg
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
;

SELECT d.name, AVG(e.salary) AS dept_avg FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.name HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees);

Exercise

This query is trying to find the highest-paid employee in the Engineering department, but it crashes because the subquery returns more than one row. Repair it by turning the subquery into a proper scalar subquery.

SELECT name
FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department_id = 1)
;

SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = 1);

Exercise

Which of these is a valid place to use a scalar subquery?

Recap

A scalar subquery is a SELECT inside another query that returns exactly one row and one column.
It can sit in the SELECT list (as a computed column), in WHERE (as a comparison value), or in HAVING (as a threshold for grouped results).
If it returns zero rows the value becomes NULL; if it returns more than one row the query raises an error.
When you need to compare against a set instead of a single value, IN or EXISTS — the subjects of the next lessons — are the right tools.

Next you will learn how to test whether a value belongs to a whole set of results.

The anatomy of a scalar subquery

Scalar subqueries in WHERE

Scalar subqueries in HAVING

Recap

Checkpoint quiz

Go deeper — technical resources