In SQL Server, error message 116 occurs when you try to select multiple columns in a subquery without introducing it with the EXISTS operator.

The full error looks like this:

Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Example

Here’s an example of a query that produces this error.

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE c.CatName IN (SELECT * FROM Dogs);

Result:

Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

The problem with this query is that the subquery uses an asterisk (*) to select all columns from the Dogs table. That table has multiple columns, and so the error is produced.

How to Fix the Error

We can fix the above error in a couple of ways.

One way to fix it is to replace the asterisk (*) with a single column name in the subquery:

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE c.CatName IN (SELECT DogName FROM Dogs);

This means that the subquery now returns just one column instead of all columns in the table.

Another way to fix it, is to do what the error message suggests and use the EXISTS operator instead of IN.

Doing this requires a slight variation in the construction of the query:

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE EXISTS (SELECT * FROM Dogs d
WHERE c.CatName = d.DogName);

As you may expect, the EXISTS operator will also work even if you do only explicitly state one column:

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE EXISTS (SELECT DogName FROM Dogs d
WHERE c.CatName = d.DogName);

But in contrast with the IN operator, EXISTS will also work if you explicitly select multiple column names in the subquery:

SELECT 
    CatId,
    CatName
FROM Cats c 
WHERE EXISTS (SELECT DogId, DogName FROM Dogs d
WHERE c.CatName = d.DogName);