MySQL IN

Summary: In this chapter, we will learn how to use MySQL IN operator to determine if a specified value matches any value in a list or a subquery.

Querying Data with MySQL IN Operator

Introduction to the MySQL IN Operator

The MySQL IN operator allows you to specify multiple values in a WHERE clause means its allows you to determine if a specified value matches any one of a list.

The following illustrates the syntax of the IN operator.

1 2 3 4 5 6

SELECT    column1,column2,... FROM    table_name WHERE    (column) IN ('value1','value2',...);

Let’s examine the query in more detail:

• We can use a column with the IN operator in the WHERE clause.
• The values in the list must be separated by a comma (,).
• The IN operator can also be used in the WHERE clause of other statements such as INSERT, UPDATE, DELETE, etc.

MySQL IN examples

We will continue with the branches table data in the sample database as shown in the picture below.

Let’s practice with some examples of using the IN operator.

If we want to find out the offices that locate in the Australia and Japan, simply we can use the IN operator as the following query:

1 2 3 4 5 6

SELECT   branchid, city, state, country FROM   branches WHERE   country IN ('Australia' , 'Japan');

RESULT:

We can achieve the same result with the MySQL OR operator as the following query:

1 2 3 4 5 6

SELECT   branchid, city, state, country FROM   branches WHERE   country='Australia' OR country ='Japan';

Note: In case the list has many values, we have to construct a very long statement with multiple OR operators. Hence, the IN operator allows you to shorten the query and make the query more readable.

In this chapter, we have learnt that how to use MySQL IN operator to determine if a value matches any value in a list.

---

MySQL LIMIT MySQL BETWEEN

---