Subqueries (Scalar and Row)

Scalar subquery: embedded as a single value in a SELECT list, WHERE condition, SET clause, etc.

SELECT column_name, (SELECT aggregate_function(...) FROM ...) AS alias
FROM table;

Using a scalar subquery in a WHERE clause.

SELECT column_name
FROM table
WHERE column_name = (SELECT statement that returns a single value);

Row subquery: compares multiple columns at once.

SELECT column_name
FROM table
WHERE (column_a, column_b) = (SELECT column_a, column_b FROM ... LIMIT 1);

The following employees table is used in the examples below.

Display each employee's salary alongside the average salary of all employees (scalar subquery).

sample_subquery_scalar.sql

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
ORDER BY salary DESC;

+--------+--------+------------+---------------+
| name   | salary | avg_salary | diff_from_avg |
+--------+--------+------------+---------------+
| user_c | 620000 | 452500.0000 |   167500.0000 |
| user_a | 480000 | 452500.0000 |    27500.0000 |
| user_e | 390000 | 452500.0000 |   -62500.0000 |
| user_b | 320000 | 452500.0000 |  -132500.0000 |
+--------+--------+------------+---------------+
4 rows in set

Retrieve the employee with the highest salary.

sample_subquery_scalar.sql

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

+--------+--------+
| name   | salary |
+--------+--------+
| user_c | 620000 |
+--------+--------+
1 row in set

Use a row subquery to find employees with the same salary and department as the highest-paid employee.

sample_subquery_scalar.sql

SELECT name, salary, department
FROM employees
WHERE (salary, department) = (
    SELECT salary, department
    FROM employees
    ORDER BY salary DESC
    LIMIT 1
);

+--------+--------+------------+
| name   | salary | department |
+--------+--------+------------+
| user_c | 620000 | Engineering  |
+--------+--------+------------+
1 row in set

Scalar subquery syntax is supported consistently across all major databases.

-- MySQL, PostgreSQL, SQL Server, Oracle, SQLite (all compatible)
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees
ORDER BY salary DESC;

The syntax for limiting a row subquery to one row differs by database. MySQL, PostgreSQL, and SQLite use LIMIT 1.

-- MySQL, PostgreSQL, SQLite
SELECT name, salary, department
FROM employees
WHERE (salary, department) = (
    SELECT salary, department FROM employees ORDER BY salary DESC LIMIT 1
);

Oracle uses FETCH FIRST 1 ROW ONLY (version 12c and later). For earlier versions, use ROWNUM to limit the result.

-- Oracle (12c and later)
SELECT name, salary, department
FROM employees
WHERE (salary, department) = (
    SELECT salary, department FROM employees ORDER BY salary DESC
    FETCH FIRST 1 ROW ONLY
);

SQL Server uses TOP 1. Note that SQL Server does not support row subqueries (comparing multiple columns simultaneously), so you must compare each column using a separate subquery.

-- SQL Server (row subqueries not supported; compare each column separately)
SELECT name, salary, department
FROM employees
WHERE salary = (SELECT TOP 1 salary FROM employees ORDER BY salary DESC)
  AND department = (SELECT TOP 1 department FROM employees ORDER BY salary DESC);

A scalar subquery must return exactly one column and one row. Returning two or more rows causes a runtime error. Aggregate functions (MAX, MIN, AVG, COUNT) naturally produce a single row, making them a natural fit for scalar subqueries.

Scalar subqueries can be used in many clauses: SELECT, WHERE, HAVING, and SET (in UPDATE statements). When placed in a SELECT clause, the subquery is evaluated for every row in the result, which can be slow on large datasets — consider rewriting with a JOIN or derived table in that case.

To compare against subqueries that return multiple rows, use IN / EXISTS or ALL / ANY. For subqueries that reference the outer query, see Correlated Subqueries.