Why Does My MySQL `NOT IN` Subquery with Multiple Columns Return 'Operand Should Contain 1 Column'?

MySQL NOT IN "Operation should contain 1 column" error caused by multiple columns in subquery

It is common to encounter the "operand should contain 1 column" error when using the NOT IN clause of a subquery that contains multiple columns. This error arises from the restriction that the NOT IN clause expects the subquery to return only a single column.

In the query provided:

<code class="language-sql">SELECT * from campaigns WHERE id not in
(
    SELECT 
        e.id_campaign, d.name, d.frequency, d.country, d.referral, d.bid, d.status, COUNT(e.id) AS countcap  
    FROM campaigns d
    LEFT JOIN served e
    ON d.id = e.id_campaign 
    WHERE 
        d.status = 'Active'
    GROUP BY e.id_campaign
    HAVING
        countcap < d.frequency
)</code>

The subquery returns eight columns (id_campaign, name, frequency, country, referral, bid, status, and countcap). However, the NOT IN clause requires a single column for comparison.

Solution:

To fix this error, we need to modify the subquery so that it only returns the id_campaign column, which is the column we want to check for non-membership in the main query:

<code class="language-sql">SELECT *
FROM campaigns 
WHERE id NOT IN (
    SELECT id_campaign
    FROM (
        SELECT e.id_campaign AS id_campaign, d.frequency, e.id
        FROM campaigns d
        LEFT JOIN served e ON d.id = e.id_campaign
        WHERE d.status = 'Active'
        GROUP BY e.id_campaign
        HAVING COUNT(e.id) < d.frequency
    ) AS subquery
);</code>

By limiting the subquery results to a single column id_campaign, we eliminate the "operand should contain 1 column" error and ensure that the NOT IN clause performs the comparison correctly. Note that we added an alias subquery to organize the query more clearly. This modified query will only return rows in the campaigns table where id is not in the subquery results.

The above is the detailed content of Why Does My MySQL `NOT IN` Subquery with Multiple Columns Return 'Operand Should Contain 1 Column'?. For more information, please follow other related articles on the PHP Chinese website!