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

What it means

A subquery used after IN, after a comparison (=, >, <>…), or in place of a single value (as a column in the select list, or in SET x = (SELECT …)) stands for one column: a list of values or one value. Yours selects two or more columns, or *, so SQL Server can’t tell which one to compare. It rejects the statement before running it.

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

EXISTS is the exception the message mentions: it only asks whether any row exists, so its select list doesn’t matter. When the subquery has one column but returns several rows where one value is needed, the error is a different one, 512.

Common causes

1. IN (SELECT *, …) or IN (SELECT id, name …): an extra column left in, often after copying the subquery from a query that showed more.
2. Two values from one correlated subquery: (SELECT COUNT(*), SUM(total) FROM orders WHERE …) as a column, to get both numbers at once.
3. Matching on two columns at once. WHERE (id, name) IN (SELECT …) works in PostgreSQL and MySQL; T-SQL has no row values, so it fails, with 4145; moving the second column into the subquery alone (WHERE id IN (SELECT customer_id, status …)) gives 116.
4. = (SELECT TOP (1) a, b …) to compare against the first row.

How to fix it

Select only the column you compare

SELECT id, name
FROM dbo.customers
WHERE id IN (SELECT customer_id FROM dbo.orders WHERE status = 'paid');

Use EXISTS to match on several columns

EXISTS with a correlated WHERE can compare as many columns as you need:

SELECT c.id, c.name
FROM dbo.customers AS c
WHERE EXISTS (SELECT 1 FROM dbo.orders AS o
              WHERE o.customer_id = c.id AND o.status = 'paid');

NOT EXISTS does the opposite, and unlike NOT IN it isn’t upset by a NULL in the subquery.

Get several values per row with APPLY

OUTER APPLY runs the subquery once per outer row and can return any number of columns; CROSS APPLY does the same but drops rows with no result:

SELECT c.name, x.orders, x.spent
FROM dbo.customers AS c
OUTER APPLY (SELECT COUNT(*) AS orders, SUM(total) AS spent
             FROM dbo.orders AS o
             WHERE o.customer_id = c.id) AS x;

A join to a grouped subquery is the other common way:

SELECT c.name, t.orders, t.spent
FROM dbo.customers AS c
LEFT JOIN (SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
           FROM dbo.orders GROUP BY customer_id) AS t ON t.customer_id = c.id;

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, each statement in its own batch:

SELECT id, name FROM seo_err_mssql.customers
  WHERE id IN (SELECT customer_id, total FROM seo_err_mssql.orders);
SELECT id, name FROM seo_err_mssql.customers
  WHERE id IN (SELECT * FROM seo_err_mssql.orders);
SELECT c.name, (SELECT COUNT(*), SUM(total) FROM seo_err_mssql.orders o WHERE o.customer_id = c.id)
  FROM seo_err_mssql.customers c;
SELECT id FROM seo_err_mssql.customers
  WHERE id = (SELECT TOP 1 customer_id, total FROM seo_err_mssql.orders ORDER BY total DESC);

each failed with the same message:

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

WHERE (id, name) IN (SELECT customer_id, status …) gave Msg 4145 … near ','. instead. EXISTS (SELECT customer_id, total …) with two columns worked and returned customers 1 and 2. The OUTER APPLY above returned Ada with 2 orders and 42.50, Lin with 1 and 99.90, and Grace with 0 and NULL. Against a list holding 1 and NULL, NOT IN returned no customers at all, and NOT EXISTS returned the other two. SQL Server 2019 and 2025 printed the same messages.

In Inlet

When SQL Server rejects the statement, Inlet shows its message with SQL Server error 116, state 1, severity 16 and links to this page. Query tabs show every result set a batch returns, so you can run the subquery on its own next to the full query to see its columns. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix a failed statement; it sends the schema and the SQL, never rows.