MySQL IN Clause

By Aastha Tyagi Last updated Jan 24, 2019 0

Introduction to MySQL IN Clause

This MySQL tutorial explains how to use the MySQL IN Clause with syntax and examples. The IN operator allows you to specify multiple values in a WHERE clause.

IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

To understand MySQL IN Clause, consider an “customers” table, which is having the following records:

SELECT * FROM customers;

CustomerID	CustomerName	Age	City	Country
1	Christina	40	London	United Kingdom
2	Maria Anders	56	Berlin	Germany
3	Matthew	34	Luleå	Sweden
4	Alen	51	Berlin	Germany
5	Jacob	28	México D.F	Mexico
6	Aastha	26	Chandigarh	India
7	Joshua	21	México D.F	Mexico

IN Operator Examples

Examples 1:

Now suppose based on the above table you want to selects all customers that are located in “London”, “Berlin” and “México D.F” city:

SELECT * FROM customers
WHERE City IN ('London', 'Berlin', 'México D.F');

Examples 2:

In other example the following MySQL statement selects all customers that are NOT located in “London”, “Berlin” and “México D.F” city:

SELECT * FROM customers
WHERE City NOT IN ('London', 'Berlin', 'México D.F');

CustomerID	CustomerName	Age	City	Country
3	Matthew	34	Luleå	Sweden
6	Aastha	26	Chandigarh	India

Examples 3:

Below is a selection from the “orders” table, which is having the following records:

SELECT * FROM orders;

OrderID	CustomerID	OrderDate
1	2	2017-02-02
2	2	2017-03-23
3	4	2017-06-14
4	5	2017-06-17
5	6	2017-07-28
6	6	2017-07-31
7	7	2017-09-10
8	6	2017-09-12

The following MySQL statement selects all customers who have made order:

SELECT * FROM customers
WHERE CustomerID IN (SELECT CustomerID FROM orders);