How to Correctly Use 'NOT IN' with a List of Values in MySQL?

Proper Format for "NOT IN" List of Values in MySQL

When attempting to populate a table variable with a comma-separated list of values and utilize it as a "NOT IN" filter, common roadblocks arise. Understanding the correct syntax and limitations of this operation is crucial.

The Issue

As the original query illustrates, setting a variable to a comma-separated list of values:

SET @idcamposexcluidos='817,803,495';

and subsequently trying to use it in a "WHERE NOT IN" condition:

WHERE id_campo not in (@idcamposexcluidos)

often results in errors or unintended results.

The Solution

The crux of the issue lies in the IN clause's expectation of distinct values. However, using a comma-separated string as the parameter effectively compiles to a single string in the IN clause, leading to incorrect comparisons.

To overcome this, one must either use dynamic SQL or employ MySQL's FIND_IN_SET() function:

SET @idcamposexcluidos='817,803,495';
...
WHERE FIND_IN_SET(id_campo, @idcamposexcluidos) = 0

While FIND_IN_SET() offers a workaround, it comes with its limitations. Unlike using indexes, it incurs a performance hit by requiring a linear search through the list of values.

The above is the detailed content of How to Correctly Use 'NOT IN' with a List of Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!