MySQL IN operator

In this article, we describes how to use MySQL IN operator to test whether a value is included in a list.

If you want to test whether a value is included in a list, you can use the IN operator.

The IN operator returns 1 if the specified list includes the specified value, otherwise it returns 0.

MySQL IN syntax

IN is a binary operator, it requires 2 operands. The following is the syntax of the IN operator:

expression IN (value1, value2,...)

Here:

  • expression can be a column name, a value, or an expression (such as function calls, arithmetic operations, etc.).
  • (value1, value2,...) is comma-separated list of values to match in the parentheses.
  • There is at least one value in (value1, value2,...).

MySQL IN algorithm

If the operand on the left side of the IN operator is one item of the list on the right, the IN operator returns 1. Otherwise, it returns 0.

The IN operator is a simplified version of multiple OR operators. For example, the following IN statement:

name IN ('Alice', 'Tim', 'Jack')

Equivalent to the following OR statement:

name = 'Alice' OR name = 'Tim' OR name = 'Jack'

The algorithm of the IN operator is as follows:

  • If both operands are not NULL and the right side list contains the left side value, the IN operator returns 1. Otherwise it returns 0.

    SELECT
  1 IN (1, 2),
  3 IN (1, 2),
  'A' IN ('A', 'B'),
  'C' IN ('A', 'B');

    +-------------+-------------+-------------------+-------------------+
| 1 IN (1, 2) | 3 IN (1, 2) | 'A' IN ('A', 'B') | 'C' IN ('A', 'B') |
+-------------+-------------+-------------------+-------------------+
|           1 |           0 |                 1 |                 0 |
+-------------+-------------+-------------------+-------------------+

  • When the left operand is NULL, the IN operator returns NULL.

    SELECT
  NULL IN (1, 2),
  NULL IN (1, 2, NULL);

    +----------------+----------------------+
| NULL IN (1, 2) | NULL IN (1, 2, NULL) |
+----------------+----------------------+
|           NULL |                 NULL |
+----------------+----------------------+

  • When the right list contains a NULL value, if the list contains the non-null value on the left, the IN operator returns 1. Otherwise it returns NULL.

    SELECT 1 IN (1, NULL), 2 IN (1, NULL);

    +----------------+----------------+
| 1 IN (1, NULL) | 2 IN (1, NULL) |
+----------------+----------------+
|              1 |           NULL |
+----------------+----------------+

MySQL IN examples

In the following example, we use the actor table from Sakila sample database as a demonstration.

The following statement will return all of actors whose last names is ALLEN or DAVIS. The result of this statement is exactly the same as the example in our MySQL OR tutorial.

SELECT *
FROM actor
WHERE last_name IN ('ALLEN', 'DAVIS');

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      118 | CUBA       | ALLEN     | 2006-02-15 04:34:33 |
|      145 | KIM        | ALLEN     | 2006-02-15 04:34:33 |
|      194 | MERYL      | ALLEN     | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS     | 2006-02-15 04:34:33 |
|      101 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
|      110 | SUSAN      | DAVIS     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Conclusion

In this article, we leared how to use MySQL IN operator to test whether a value is included in a list. The main points of the IN operator are as follows:

  • IN is a binary operator and requires 2 operands.
  • The left operand of IN operator is a column name or value, and the right operand is the value list or the result of a subquery.
  • When the operand on the left side of the IN operator is included in the lists on the right side, the IN operator returns 1. Otherwise, it returns 0.
  • A combination of multiple OR can be replaced by IN.
  • The negation operation of the IN operator is NOT IN.