SA0251 : Subquery used in expression not ensured to return a single value |
![]() |
Subqueries should return a single value by selecting one column and limiting the result to a single row using TOP 1 to avoid execution errors and unexpected results.

Subqueries used in SQL expressions should return a single value to maintain predictable and efficient query behavior. The subquery must select one column and be limited to a single row by utilizing the TOP 1 clause. Failing to ensure this can lead to execution errors and unexpected results in SQL Server.
For example:
1-- Example of problematic query 2SELECT (SELECT ColumnA, ColumnB FROM TableName) AS SubqueryResult;
This query is problematic because the subquery returns multiple columns. SQL Server expects the subquery in an expression to yield a single scalar result. Without a TOP 1 clause, the result set can contain more than one row, causing issues.
-
Subqueries returning multiple columns or rows lead to SQL errors, hindering query execution.
-
Optimizing subqueries with a TOP 1 clause enhances performance and reliability.

Ensure subqueries return a single value by modifying them to include a TOP (1) clause and select only one column.
Follow these steps to address the issue:
-
Identify subqueries in your T-SQL code that return multiple columns or rows. For instance, look for subqueries that select more than one column or do not limit the number of rows returned.
-
Revise the subquery to return only a single column. Ensure the SELECT clause includes only one column name.
-
Incorporate a TOP (1) clause in the subquery to ensure it retrieves only the first row of the result set.
For example:
1-- Corrected query with a single column and TOP (1) clause 2SELECT (SELECT TOP (1) ColumnA FROM TableName) AS SubqueryResult;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs


SQL
1-- Subquery might return multiple values, causing an error 2SELECT EmployeeID FROM HumanResources.Employee 3WHERE JobTitle = (SELECT JobTitle FROM HumanResources.Employee WHERE OrganizationLevel = 2); 4 5-- Using a subquery in an expression without ensuring a single result 6SELECT OrderID FROM Sales.SalesOrderHeader 7WHERE CustomerID = (SELECT CustomerID FROM Sales.Customer WHERE TerritoryID = 1); 8 9-- Incorrect use of subquery in UPDATE 10UPDATE Sales.SalesOrderHeader 11SET SalesPersonID = (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE TerritoryID = 5) 12WHERE OrderDate > '2023-01-01'; 13 14-- Subquery with GROUP BY might return multiple values 15SELECT Name FROM Production.Product 16WHERE ListPrice > (SELECT AVG(ListPrice) FROM Production.Product GROUP BY ProductSubcategoryID); 17 18-- Fix: Use TOP 1 or aggregate function 19SELECT Name FROM Production.Product 20WHERE ListPrice > (SELECT MAX(ListPrice) FROM Production.Product WHERE ProductSubcategoryID = 14); |

Message | Line | Column | |
---|---|---|---|
1 | SA0251 : Subquery used in expression not ensured to return a single value. | 3 | 18 |
2 | SA0251 : Subquery used in expression not ensured to return a single value. | 7 | 20 |
3 | SA0251 : Subquery used in expression not ensured to return a single value. | 11 | 21 |
4 | SA0251 : Subquery used in expression not ensured to return a single value. | 16 | 19 |
