This chapter uses the university database.

Which instructors earn more than the average? Two steps by hand: find the average, then filter.

SELECT AVG(salary) FROM instructor;

The result is 78000. Now put the first query inside the second, in parentheses:

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

A subquery is a query inside another query. This one returns one row with one column: a single value. That is a scalar subquery. The database computes it, then uses the value as if you had typed it.

• A scalar subquery must return at most one row and one column. More rows give an error:

ERROR: more than one row returned by a subquery used as an expression

• If it returns no rows, its value is NULL.
• Run the subquery alone first. Check that it returns one value.

In the SELECT list

A scalar subquery can also stand in the SELECT list. It gives the same value on every row:

SELECT name, salary, salary - (SELECT AVG(salary) FROM instructor) AS above_average
FROM instructor;