Subqueries By result shape single value · SELECT · CASE · UPDATE

Scalar Subquery

A subquery that returns exactly one value. Use it anywhere SQL accepts an expression — in SELECT, WHERE, ORDER BY, CASE, UPDATE — and rely on it running just once when it's not correlated.

What a scalar subquery is

A scalar subquery is the strictest kind: it must return exactly one row with exactly one column — a single value. That single value can be used wherever SQL accepts an expression: in the SELECT list, inside a WHERE clause, in CASE expressions, in UPDATE SET clauses, and so on.

Single-row subqueries (the previous page) are scalar subqueries when they return a single column. The label "scalar" emphasises the "one value" property — useful when the value will be embedded in expressions.

⚠ Two strict rules: if the subquery returns more than one row, the database raises an error. If it returns zero rows, the value is NULL and the surrounding expression usually evaluates to NULL too.

Where scalar subqueries can sit

The SELECT list — return one extra column per row.
The WHERE clause — compare a column to the value.
The HAVING clause — same idea, applied to groups.
An ORDER BY expression.
The SET clause of an UPDATE.
The VALUES clause of an INSERT.
Inside CASE branches and other expressions.

Demo dataset — employees

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    email          VARCHAR(100),
    phone_number   VARCHAR(20),
    hire_date      DATE,
    job_id         INT,
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(100, 'Steven',    'King',      'steven.king@x.org',     '515.123.4567', '1987-06-17',  4, 24000.00, NULL,  9),
(101, 'Neena',     'Kochhar',   'neena.kochhar@x.org',   '515.123.4568', '1989-09-21',  5, 17000.00,  100,  9),
(115, 'Alexander', 'Khoo',      'alexander.khoo@x.org',  '515.127.4562', '1995-05-18', 13,  3100.00,  114,  3),
(116, 'Shelli',    'Baida',     'shelli.baida@x.org',    '515.127.4563', '1997-12-24', 13,  2900.00,  114,  3),
(200, 'Jennifer',  'Whalen',    'jennifer.whalen@x.org', '515.123.4444', '1987-09-17',  3,  4400.00,  101,  1),
(201, 'Michael',   'Hartstein', 'michael.h@x.org',       '515.123.5555', '1996-02-17', 10, 13000.00,  100,  2),
(202, 'Pat',       'Fay',       'pat.fay@x.org',         '603.123.6666', '1997-08-17', 11,  6000.00,  201,  2),
(203, 'Susan',     'Mavris',    'susan.mavris@x.org',    '515.123.7777', '1994-06-07',  8,  6500.00,  101,  4);

Example 1 — scalar subquery in SELECT

Append the company-wide average salary as a column on every row, for context.

SQL

SELECT first_name,
       salary,
       (SELECT ROUND(AVG(salary), 2) FROM employees) AS company_avg
FROM   employees
ORDER  BY salary DESC;

The subquery runs once. Its single value gets joined alongside every output row — efficient and clear.

Example 2 — scalar subquery in WHERE

SQL

-- Employees who joined before the most senior person did
SELECT first_name, hire_date
FROM   employees
WHERE  hire_date < (
    SELECT MIN(hire_date)
    FROM   employees
    WHERE  job_id = 4   -- only managers count as "senior" here
);

Example 3 — scalar subquery inside CASE

Tag each employee as above or below the company average.

SQL

SELECT first_name,
       salary,
       CASE
         WHEN salary >= (SELECT AVG(salary) FROM employees) THEN 'above avg'
         ELSE                                                    'below avg'
       END AS bracket
FROM   employees;

Example 4 — scalar subquery in UPDATE

SQL

-- Set everyone's salary to the maximum currently in the table
UPDATE employees
SET    salary = (SELECT MAX(salary) FROM employees);

Example 5 — scalar subquery in ORDER BY

Sort by how far each salary is from the company average — biggest deviations first.

SQL

SELECT first_name, salary
FROM   employees
ORDER  BY ABS(salary - (SELECT AVG(salary) FROM employees)) DESC;

Performance note

A non-correlated scalar subquery (one that doesn't reference outer columns) is computed once per query, not once per row. The optimizer treats it as a constant. That's why repeating (SELECT AVG(salary) FROM employees) in several places is fine — the database evaluates it once and reuses the result.

A correlated scalar subquery, however, runs per row. If the table is large, that can be slow.

Recap

A scalar subquery returns exactly one value (one row × one column).
Returning more rows is an error; returning zero rows yields NULL.
Useful anywhere an expression is allowed: SELECT, WHERE, HAVING, ORDER BY, UPDATE, CASE, etc.
Non-correlated scalar subqueries run once and are cheap; correlated ones run per row and need attention.