How to Use MySQL's 'NOT IN' Clause with a List of Values?

MySQL Syntax for "NOT IN" Lists of Values

Question:

How do you define a MySQL variable with a list of values for use in a "NOT IN" clause, effectively excluding multiple values from a query?

Answer:

When constructing a "NOT IN" clause with a list of values, it's essential to avoid using the entire list as a single string within the clause. MySQL compiles it as a unified string, while the "NOT IN" clause requires individual values. Consider the following example:

WHERE id_campo NOT IN (@idcamposexcluidos)

This compiles to:

WHERE id_campo NOT IN ('817,803,495')

However, the correct syntax would be:

WHERE id_campo NOT IN ('817', '803', '495')

To overcome this, there are two common approaches:

• Dynamic SQL: Construct the query string with the correct syntax, ensuring that individual values are specified within the "NOT IN" clause.
• FIND_IN_SET: MySQL provides the FIND_IN_SET() function, which can be used as follows:

WHERE FIND_IN_SET(id_campo, @idcamposexcluidos) = 0

It's important to note that using the FIND_IN_SET() function may impact performance if indexes are employed on the 'id_campo' column.

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