intermediate

Scalar Subqueries

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

Overview

A Scalar Subquery is a nested query that returns exactly one row and one column (a single value), allowing it to be used anywhere a scalar value or literal is expected.

Learning Objectives

  • Define and identify scalar subqueries.
  • Embed scalar subqueries in SELECT, WHERE, and HAVING clauses.
  • Handle runtime errors caused by subqueries returning multiple rows.

Detailed Concept Explanation

Because a scalar subquery evaluates to a single cell value, SQL treats it like a constant value. You can compare column values against it using standard operators like =, >, <, etc.

If a scalar subquery accidentally returns multiple rows or multiple columns at runtime, the database engine throws a execution error: "Subquery returned more than 1 row".


Code Examples

SQL

sql
-- Scalar subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Scalar subquery in SELECT projection
SELECT 
  product_name,
  price,
  (SELECT MAX(price) FROM products) AS highest_price,
  (SELECT MAX(price) FROM products) - price AS price_difference
FROM products;

Best Practices

  • Ensure Single Output: Always use aggregate functions (AVG, MAX, COUNT) or LIMIT 1 inside a scalar subquery to guarantee it never returns more than one row.

Interview Perspective

Note

Interview Question: Where can a Scalar Subquery be used in a SQL query?

Virtually anywhere a single value is valid: in SELECT expressions, WHERE conditions, HAVING filters, or even as input parameters to functions.


Interactive Challenges

Challenge 1: Filter Above Average

Select 'name' and 'salary' from 'employees' where salary is greater than the scalar subquery calculating (SELECT AVG(salary) FROM employees).


Summary

Scalar subqueries yield a single cell result and can be embedded inside SELECT projections or WHERE filters.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.