SQL Server Error Msg 116 – Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

In this post, let’s learn about the error message “116 – Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

116 – Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Reason for the Error

You will get the error message 116 in SQL Server when you attempt to select multiple columns in the subquery without introducing it with the NOT IN or IN operator in SQL Server. The IN operator in SQL Server is used to find whether a specified value matches with any values in the specified column in the sub query.

For example, let’s have a look at the below SQL Server.

SELECT * from 
[Person].[Person]
WHERE [BusinessEntityID] 
IN (SELECT * FROM [Person].[PersonPhone])

This will result with the SQL Error Msg 116 because the subquery selects all the columns using the asterik (*) from the PersonPhone table.

Solution

There are multiple ways to fix this error message in SQL Server.

One such option is to use the single column name in the subquery instead of using *. Below is a sample query that demonstrates this.

SELECT * from 
[Person].[Person]
WHERE [BusinessEntityID] IN (SELECT BusinessEntityID FROM [Person].[PersonPhone])

In the above example, the subquery just returns one column (BusinessEntityId) instead of all the columns from the table.

The other option to fix this is to use the EXIST operator instead of IN by slightly tweaking your query and apply the WHERE clause inside the inner query as shown below.

SELECT * from 
[Person].[Person] AS P
WHERE EXISTS (SELECT * FROM [Person].[PersonPhone] AS PP WHERE PP.BusinessEntityID = P.BusinessEntityID)

You can also use INNER JOIN to fix this error as an alternate solution.