Scalar Subquery

Source data — Pattern 8: Subqueries

All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces scalar, correlated, and multiple-row subqueries. Topics 47 and 48 demonstrate the EXISTS and NOT EXISTS operators, while Topics 49 and 50 introduce the ANY (SOME) and ALL comparison operators.

---

What it does

A scalar subquery returns exactly one value. It can be used anywhere a single value or expression is expected, such as in the SELECT, WHERE, or HAVING clause.

Problem

Find employees earning more than the average salary.

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

Result