Why Does My SQL Subquery in a WHERE IN Clause Return 'Only One Expression Can Be Specified in the Select List'?

SQL subquery selection in WHERE IN clause: Understanding error "When the subquery does not start with EXISTS, only one expression can be specified in the select list"

When trying to add a subquery to a WHERE IN clause, always ensure that the subquery returns a single column for comparison. The error "Only one expression can be specified in the select list when the subquery does not begin with EXISTS" means that your subquery returns multiple columns.

To correct this, let us examine the given query:

select count(distinct dNum)
from myDB.dbo.AQ
where A_ID in
(SELECT DISTINCT TOP (0.1) PERCENT A_ID,
            COUNT(DISTINCT dNum) AS ud
FROM myDB.dbo.AQ
WHERE M > 1 and B = 0
GROUP BY A_ID ORDER BY ud DESC)

The subquery inside the brackets returns two columns: A_ID and ud (count of distinct dNum values ​​for each A_ID). However, the WHERE IN clause expects a single column to compare to A_ID. Therefore, we need to modify the subquery to only return the A_ID column:

select count(distinct dNum)
from myDB.dbo.AQ
where A_ID in
(SELECT DISTINCT TOP (0.1) PERCENT A_ID
FROM myDB.dbo.AQ
WHERE M > 1 and B = 0
GROUP BY A_ID
ORDER BY COUNT(DISTINCT dNum) DESC)

The subquery now returns a single column, A_ID, which can be used for comparison in the WHERE IN clause. Additionally, we can remove the COUNT(DISTINCT dNum) AS ud clause since it no longer requires selecting only the top 0.1% of A_ID values.

This modified query should resolve the specified error and allow you to retrieve the desired results. Remember, when using a subquery in a WHERE IN clause, make sure that the subquery returns only the columns required for the comparison.

The above is the detailed content of Why Does My SQL Subquery in a WHERE IN Clause Return 'Only One Expression Can Be Specified in the Select List'?. For more information, please follow other related articles on the PHP Chinese website!