SQL Server Interview Question #71

What is a scalar subquery?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

A scalar subquery is expected to return a single value: one column from at most one row. It can be used anywhere a scalar expression is valid.

If a scalar subquery returns more than one row, SQL Server raises an error. Aggregate functions such as MAX, MIN, or AVG are often used when a single result is required.

Code Example

SELECT p.Id,
       p.Name,
       (SELECT MAX(Price) FROM dbo.Products) AS HighestPrice
FROM dbo.Products AS p;