6.3 Using IN() to Test Membership in a List of Values

It's sometimes necessary to determine whether a value is equal to any of several specific values. One way to accomplish this is to combine several equality tests into a single expression with the OR logical operator:

... WHERE id = 13 OR id = 45 OR id = 97 OR id = 142

... WHERE name = 'Tom' OR name = 'Dick' OR name = 'Harry'

However, MySQL provides an IN() operator that performs the same kind of comparison and that is more concise and easier to read. To use it, provide the comparison values as a comma-separated list of arguments to IN():

... WHERE id IN(13,45,97,142)

... WHERE name IN('Tom','Dick','Harry')

Using IN() is equivalent to writing a list of comparisons with OR, but IN() is much more efficient.

Arguments to IN() may be of any type (numeric, string, or temporal), although generally all values given within a list are all the same type.

Elements in a list may be given as expressions that are evaluated to produce a value. If the expression references a column name, the column is evaluated for each row.

IN() always returns NULL when used to test NULL. That is, NULL IN(list) is NULL for any list of values, even if NULL is included in the list. This occurs because NULL IN(NULL) is equivalent to NULL = NULL, which evaluates to NULL.

IN() tests membership within a set of individual values. If you're searching for a range of values, a range test might be more suitable. The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them:

... WHERE id BETWEEN 5 AND 10

The comparison is inclusive, so the preceding expression is equivalent to this one:

... WHERE id >= 5 AND id <= 10