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);

Type	Description
Scalar subquery	A subquery that returns a single value (one column, one row). Can be used in SELECT lists, WHERE clauses, SET clauses, and more.
Row subquery	A subquery that returns one row with multiple columns. Used with row value expressions to compare multiple columns at once.

-- Display each employee's salary alongside the average salary of all employees (scalar subquery).
SELECT
    employee_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;

-- Retrieve the employee with the highest salary.
SELECT employee_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Use a row subquery to find orders with the same customer ID and product ID as the most recent order.
SELECT order_id, customer_id, product_id, order_date
FROM orders
WHERE (customer_id, product_id) = (
    SELECT customer_id, product_id
    FROM orders
    ORDER BY order_date DESC
    LIMIT 1
);

-- Comparison with the average salary.
employee_name | salary  | avg_salary | diff_from_avg
--------------+---------+------------+--------------
Hanako Suzuki | 620000  | 480000     | 140000
Taro Yamada   | 540000  | 480000     | 60000
Jiro Sato     | 420000  | 480000     | -60000
Megumi Tanaka | 340000  | 480000     | -140000

-- Employee with the highest salary.
employee_name | salary
--------------+---------
Hanako Suzuki | 620000

Scalar subquery syntax is supported consistently across all major databases.

-- MySQL, PostgreSQL, SQL Server, Oracle, SQLite (all compatible)
SELECT employee_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 order_id, customer_id, product_id
FROM orders
WHERE (customer_id, product_id) = (
    SELECT customer_id, product_id FROM orders ORDER BY order_date 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 order_id, customer_id, product_id
FROM orders
WHERE (customer_id, product_id) = (
    SELECT customer_id, product_id FROM orders ORDER BY order_date 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 order_id, customer_id, product_id
FROM orders
WHERE customer_id = (SELECT TOP 1 customer_id FROM orders ORDER BY order_date DESC)
  AND product_id = (SELECT TOP 1 product_id FROM orders ORDER BY order_date 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.