MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference
MySQL :: MySQL 8.0 Reference Manual :: 12.4.2 Comparison Functions and Operators
- expr IN (value,...)Returns
1 (true) if expr is equal to any of the values in the IN() list, else returns 0 (false).
Type conversion takes place according to the rules described inSection 12.3, “Type Conversion in Expression Evaluation”, applied to all the arguments. If no type conversion is needed for the values in the IN() list, they are all non-JSON constants of the same type, and expr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for expr is done using a binary search, which makes the IN() operation very quick.
mysql> SELECT 2 IN (0,3,5,7);
-> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1
IN() can be used to compare row constructors:
mysql> SELECT (3,4) IN ((1,2), (3,4));
-> 1
mysql> SELECT (3,4) IN ((1,2), (3,5));
-> 0
You should never mix quoted and unquoted values in an IN() list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN() expression like this:
SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
Instead, write it like this:
SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
Implicit type conversion may produce nonintuitive results:
mysql> SELECT 'a' IN (0), 0 IN ('b');
-> 1, 1
In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).
The number of values in the IN() list is only limited by the max_allowed_packet value.
To comply with the SQL standard, IN() returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.
IN() syntax can also be used to write certain types of subqueries. See Section 13.2.11.3, “Subqueries with ANY, IN, or SOME”.
- expr NOT IN (value,...)This is the same as
NOT (expr IN (value,...)).
