Home/Learn/SQL/Scalar Subquery

Scalar Subquery

Intermediate

Subqueries & CTEs

A scalar subquery returns exactly one row and one column, and can appear anywhere a single value is expected — SELECT list, WHERE, HAVING, or ORDER BY.

Overview

A scalar subquery is a subquery wrapped in parentheses that returns a single value. It can appear in the SELECT list to compute derived columns, in WHERE to compare a value, or in HAVING to filter groups. A correlated scalar subquery references columns from the outer query and re-executes once per outer row — this is the SQL equivalent of an N+1 query problem and can be catastrophically slow on large tables. A non-correlated scalar subquery executes once and is cached. When a scalar subquery returns no rows the result is NULL, not an error. Rewriting correlated scalar subqueries as JOINs or window functions is a key performance optimisation.

Scalar Subquery in SELECT — Correlated vs Non-Correlated

A non-correlated scalar subquery computes one value for the entire query. A correlated scalar subquery references the outer row and fires once per row — dangerous at scale.

SQL — correlated scalar subquery N+1 and JOIN rewrite

-- Non-correlated: computes once, reused for every row
SELECT
    e.name,
    e.salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary,
    e.salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees e;

-- Correlated scalar subquery: re-executes for EVERY employee row (N+1!)
SELECT
    e.name,
    e.salary,
    (
        SELECT AVG(salary)
        FROM employees dept_avg
        WHERE dept_avg.department_id = e.department_id  -- references outer row
    ) AS dept_avg_salary
FROM employees e;
-- On 10 000 employees this runs the subquery 10 000 times.

-- Performance fix: rewrite as a JOIN to a pre-aggregated subquery
SELECT
    e.name,
    e.salary,
    da.dept_avg_salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS dept_avg_salary
    FROM employees
    GROUP BY department_id
) da ON da.department_id = e.department_id;
-- Single scan of employees; O(n) instead of O(n²).

NULL Behaviour and Scalar Subquery in WHERE / HAVING

When a scalar subquery returns no rows, its value is NULL. Comparisons against NULL always evaluate to UNKNOWN, which can silently filter out rows.

SQL — NULL from empty scalar subquery, HAVING usage

-- Scalar subquery returning NULL when no rows match
SELECT name
FROM employees
WHERE salary > (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = 999   -- department does not exist → returns NULL
);
-- salary > NULL evaluates to UNKNOWN → zero rows returned (silent!)

-- Safe pattern: use COALESCE to provide a default
WHERE salary > COALESCE(
    (SELECT MAX(salary) FROM employees WHERE department_id = 999),
    0
);

-- Scalar subquery in HAVING: find departments whose avg salary exceeds company avg
SELECT department_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Key Points to Remember

1A scalar subquery must return exactly one row and one column — a runtime error occurs if it returns more than one row.
2When a scalar subquery returns no rows, the result is NULL — comparisons with NULL silently filter rows.
3A correlated scalar subquery re-executes once per outer row; on large tables this is an N+1 performance problem.
4Rewrite correlated scalar subqueries in SELECT as JOINs to pre-aggregated subqueries or as window functions.
5Non-correlated scalar subqueries execute once and are cached — safe to use for global constants like company-wide averages.

Interview Questions

What happens when a scalar subquery returns no rows? How do you handle it safely?

MediumAmazon

Rewrite a correlated scalar subquery that computes department average salary as a performant JOIN.

MediumMicrosoft

What is the difference between a correlated and a non-correlated scalar subquery in terms of execution?

EasyFlipkart

Where can a scalar subquery appear in a SQL statement? Give an example for each location.

EasyAtlassian

Ask Aria about Scalar Subquery

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

COUNT(DISTINCT col) & Approximate Counting

Correlated Subquery