Scalar Subqueries

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

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

Interactive Challenges

Summary

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