Blog

In this guide, we will explain how to use the MySQL IN condition with syntax and examples.

Description

The MySQL IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE or DELETE statement.

Syntax

The syntax for the IN condition in MySQL is:

expression IN (value1, value2, .... value_n);

OR

expression IN (subquery);

Parameters or Arguments

expression value to test.value1, value2, … or value_nThese are the values to test against expression. If any of these values match the expression, then the IN condition will evaluate as true. This is a quick method to test if any one of the values matches the expression.subqueryThis is a SELECT statement whose result set will be tested against expression. If any of these values match the expression, then the IN condition will evaluate as true.

Note

• The IN condition will return the records where expression is value1, value2…, or value_n.
• The IN condition is also called the MySQL IN operator.

Example – With Character

Let’s look at a MySQL IN condition example using character values.

The following is a MySQL SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM contacts
WHERE last_name IN ('Johnson', 'Anderson', 'Smith');

This IN condition example would return all rows from the contacts table where the last_name is either Johnson, Anderson or Smith. Because the * is used in the SELECT, all fields from the contacts table would appear in the result set.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM contacts
WHERE last_name = 'Johnson'
OR last_name = 'Anderson'
OR last_name = 'Smith';

As you can see, using the IN condition makes the statement easier to read and more efficient.

Example – With Numeric

Next, let’s look at a MySQL IN condition example using numeric values.

For example:

SELECT *
FROM suppliers
WHERE supplier_id IN (200, 201, 203, 300);

This IN condition example would return all suppliers where the supplier_id is either 200, 201, 203, or 300.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id = 200
OR supplier_id = 201
OR supplier_id = 203
OR supplier_id = 300;

Example – Using NOT operator

Finally, let’s look at an IN condition example using the NOT operator.

For example:

SELECT *
FROM contacts
WHERE last_name NOT IN ('Johnson', 'Anderson', 'Smith');

This IN condition example would return all rows from the contacts table where the last_name is not Johnson, Anderson, or Smith. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Next Topic : Click Here