Why Does My SQL Subquery Return 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS'?

SQL subquery select list restrictions

When you encounter the error "When the subquery does not start with EXISTS, only one expression can be specified in the select list", it means there is a problem with the return value of the subquery. In a subquery nested within a WHERE IN clause, it is important to ensure that only one column is returned for comparison to the other side of the IN.

The query provided initially contains a subquery that returns A_ID and COUNT(DISTINCT dNum) as ud. However, in the WHERE IN clause, the subquery can only return a single column, which will be compared to the A_ID in the main query. The reason this limitation exists is that the function of the subquery is to return a list of values ​​to check if the A_ID in the main query matches any value in the subquery result.

To solve this problem, modify the subquery to return only the A_ID column, which will be compared to the A_ID in the WHERE IN clause of the main query. Additionally, sorting can be used in the ORDER clause to select rows from a subquery without returning COUNT as a column.

Corrected query example:

<code class="language-sql">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
)</code>

By implementing these changes, queries can correctly return the desired results while adhering to the rule of returning a single column for WHERE IN comparisons.

The above is the detailed content of Why Does My SQL Subquery Return 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS'?. For more information, please follow other related articles on the PHP Chinese website!