ReviseAlgo Logo

Subqueries

Scalar Subqueries

Subqueries that return a single value, usable in SELECT, WHERE, and HAVING.

Interview: Scalar subqueries are tested in interviews to evaluate understanding of when a subquery can appear (SELECT list, WHERE, HAVING), what happens when it returns more than one row, and how to rewrite scalar subqueries as JOINs for performance.

Last Updated: June 12, 2026 • 15 min read

A scalar subquery returns exactly one value — one row, one column. Because it behaves like a single constant, you can drop it almost anywhere: in SELECT lists, WHERE conditions, or HAVING clauses. Mastering scalar subqueries lets you compare individual rows against aggregate benchmarks without writing complex joins.

1. Introduction

Subqueries come in many shapes, but the scalar subquery is the simplest and most versatile. It wraps a SELECT inside parentheses and produces a single value that the outer query can use for comparison, display, or calculation. If you've ever written WHERE salary > (SELECT AVG(salary) FROM employees), you've used a scalar subquery.

2. Why It Matters

  • Works anywhere a value is expected: SELECT list, WHERE, HAVING, ORDER BY, and even inside function arguments.
  • Benchmark comparisons: Compare every row against company-wide or category-wide aggregates without a JOIN.
  • Inline lookups: Fetch a single related value (a name, a date, a count) without adding a JOIN clause.
  • Interview staple: "What happens if a scalar subquery returns zero rows? Multiple rows?" — interviewers test your understanding of edge cases.

3. Real-World Analogy

Imagine a teacher grading exams. For each student, she checks: "Is this score above the class average?" She computes the average once (the scalar subquery), then compares each student's score (the outer query) against that single number. If she compares against each student's own section average instead, that becomes a correlated subquery — but the single-value principle remains the same.

4. How It Works

  • Must return exactly one row and one column: Use aggregate functions (AVG, MAX, COUNT) or LIMIT 1 to guarantee this.
  • Non-correlated = runs once: (SELECT AVG(salary) FROM employees) is computed once and cached — very efficient.
  • Zero rows = NULL: If the subquery returns no rows, the result is NULL, not an error. This can silently break calculations (salary / NULL = NULL).
  • Multiple rows = error: PostgreSQL throws ERROR: more than one row returned by a subquery used as an expression.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Employees earning above company average with benchmark display:

Department payroll as percentage of total:

8. Common Mistakes

Subquery returns multiple rows

WHERE dept = (SELECT dept FROM employees WHERE salary > 100000) fails if multiple departments have high earners. Fix: use IN (SELECT ...) or add LIMIT 1.

Zero rows produces NULL silently

If the subquery returns no rows, you get NULL — not an error. salary / NULL = NULL, silently breaking your calculation. Use COALESCE(subquery, 0) as a safety net.

Correlated scalar subqueries in SELECT for large tables

A correlated scalar subquery in SELECT runs once per output row. For 100K rows, that's 100K executions. Rewrite as a JOIN or window function for better performance.

Interview Insight

"What happens if a scalar subquery returns zero rows?" — The result is NULL, not an error. Follow-up: "What about multiple rows?" — PostgreSQL throws an error. You must guarantee at most one row via aggregate, LIMIT 1, or a WHERE on a unique column.

9. Quick Quiz

Q1: Can you use a scalar subquery in an ORDER BY clause?

Answer: Yes. For example, ORDER BY salary / (SELECT MAX(salary) FROM employees) sorts employees by their salary as a fraction of the maximum. The subquery runs once and the result is used for sorting.

Q2: What's the difference between = (SELECT ...) and IN (SELECT ...)?

Answer: = requires exactly one row from the subquery (scalar). IN accepts multiple rows. Using = with a multi-row subquery causes an error.

10. Scenario-Based Challenge

Challenge: Salary Benchmarking Dashboard

Build a query that shows each employee with three benchmarks: company average, department average, and their percentile rank (what % of employees earn less).

Requirements:

  1. Use a non-correlated scalar subquery for the company average.
  2. Use a correlated scalar subquery for the department average.
  3. Use a correlated scalar subquery with COUNT for the percentile: (SELECT COUNT(*) FROM employees e2 WHERE e2.salary < e1.salary) * 100.0 / (SELECT COUNT(*) FROM employees).

Then rewrite the entire query using JOINs and window functions. Which version is more readable? Which is faster?

11. Debugging Exercise

Find the bug in this query:

Issues:

  1. Missing parentheses in WHERE: salary > SELECT ... is invalid. Must be salary > (SELECT ...).
  2. NULL risk in division: If no Sales employees exist, MAX(salary) returns NULL, and the ratio becomes NULL. Use COALESCE or ensure the subquery always returns a value.
  3. Integer division: If salary is integer, salary / max_salary truncates to 0 or 1. Cast to numeric: salary::numeric / max_salary.

12. Interview Questions

Q1: Where can a scalar subquery appear in a SQL statement?

A: Almost anywhere a single value is expected: SELECT list, WHERE, HAVING, ORDER BY, CASE expressions, function arguments, and even inside other subqueries. It cannot appear in FROM (that's a derived table) or in GROUP BY.

Q2: How would you rewrite a scalar subquery in SELECT as a JOIN?

A: Move the subquery into a CTE or derived table, then CROSS JOIN (for non-correlated) or JOIN (for correlated). Example: WITH avg_sal AS (SELECT AVG(salary) AS avg_s FROM employees) SELECT e.name, e.salary, a.avg_s FROM employees e CROSS JOIN avg_sal a.

Q3: Is a non-correlated scalar subquery in WHERE evaluated once or per row?

A: Once. PostgreSQL evaluates it, caches the result, and reuses it for every row. This makes non-correlated scalar subqueries very efficient — essentially the same as hard-coding the value.

13. Production Considerations

  • Prefer JOINs for large datasets: Correlated scalar subqueries in SELECT run once per row. For 100K+ rows, switch to a pre-computed JOIN or window function.
  • Use COALESCE for NULL safety: Wrap scalar subqueries that might return zero rows: COALESCE((SELECT ...), 0).
  • Check EXPLAIN plans: PostgreSQL may or may not decorrelate subqueries. Always verify with EXPLAIN ANALYZE to see actual execution strategy.
  • Index lookup columns: If the scalar subquery filters on a column (e.g., WHERE department_id = e.department_id), ensure that column is indexed.
  • Avoid deeply nested subqueries: Multiple levels of scalar subqueries become unreadable. Use CTEs to flatten the logic into named steps.

Use Cases

Benchmark comparisons — showing each row’s value relative to a company-wide or category-wide aggregate

Payroll analysis — computing each department’s share of total payroll using a scalar subquery for the denominator

Inline lookups — fetching a single related value (latest date, count, name) in the SELECT list without adding a JOIN

Filtering on aggregates — finding rows that exceed or fall below a computed threshold (above-average, below-median)

Normalization — expressing values as percentages of a total (each sale as % of total revenue)

Common Mistakes

Scalar subquery returning multiple rows — ensure the subquery returns at most one row; use aggregates (AVG, MAX) or LIMIT 1

Forgetting that zero rows returns NULL, not an error — this can silently produce NULL in calculations (salary / NULL = NULL)

Using correlated scalar subqueries in SELECT for large result sets — the subquery runs once per row; switch to a JOIN or window function for better performance

Mixing up = and IN — using = with a subquery that returns multiple rows causes an error; use IN when multiple matches are possible

Not wrapping subquery in parentheses — scalar subqueries in expressions must be parenthesized: salary > (SELECT AVG(salary) FROM employees)