MySQL IN Clause

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;
CustomerIDCustomerNameAgeCityCountry
1Christina40LondonUnited Kingdom
2Maria Anders56BerlinGermany
3Matthew34LuleåSweden
4Alen51BerlinGermany
5Jacob28México D.FMexico
6Aastha26ChandigarhIndia
7Joshua21México D.FMexico

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');
CustomerIDCustomerNameAgeCityCountry
1Christina40LondonUnited Kingdom
2Maria Anders56BerlinGermany
4Alen51BerlinGermany
5Jacob28México D.FMexico
7Joshua21México D.FMexico

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');
CustomerIDCustomerNameAgeCityCountry
3Matthew34LuleåSweden
6Aastha26ChandigarhIndia

Examples 3:

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

SELECT * FROM orders;
OrderIDCustomerIDOrderDate
122017-02-02
222017-03-23
342017-06-14
452017-06-17
562017-07-28
662017-07-31
772017-09-10
862017-09-12

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

SELECT * FROM customers
WHERE CustomerID IN (SELECT CustomerID FROM orders);
CustomerIDCustomerNameAgeCityCountry
2Maria Anders56BerlinGermany
4Alen51BerlinGermany
5Jacob28México D.FMexico
6Aastha26ChandigarhIndia
7Joshua21México D.FMexico

Leave A Reply

Your email address will not be published.

Comment moderation is enabled. Your comment may take some time to appear.