MySQL IN Operator

The MySQL IN Operator is used to restrict the total number of rows returned by the SELECT Statement. The IN Operator checks the given expression against the Values inside it. If there is at least one match, then the SELECT Statement returns the records.

Please use MySQL IN Operator to check an expression or condition against Multiple values and the basic syntax of this can be written as:

SELECT [Column Names] 
FROM [Source] 
WHERE [Column_Name] IN (Value1,...., ValueN) 

-- Use OR to write the above statement 
SELECT [Column Names] 
FROM [Source] 
WHERE [Column_Name] = Value1 OR [Column_Name] = ValueN

For this MySQL IN Operator example, We are using the below-shown data.

Customer Table

MySQL IN Operator Example

In this example, we are checking for numeric values using this one.

SELECT 3 IN (1, 2, 3, 4, 5);

SELECT 6 IN (1, 2, 3, 4, 5);

SELECT 3.5 IN (1, 2, 3, 4, 5);
Simple Example 1

Let me check for string data using in operator. Within the third statement, we used mixed values inside it. In real-time, this might give strange results, so always use a single data type inside this.

SELECT 'abc' IN ('xyz', 'hi', 'abc', 'ab');

SELECT 'a' IN ('xyz', 'hi', 'abc', 'ab');

SELECT 4 IN (1, 3, 4, '5');
String or Text in Example 2

Instead of using Mixed values, we have to use any single MySQL type. So, the SELECT Statement gives an accurate result.

SELECT 4 IN ('1',' 3', '4', '5');

SELECT (10, 20) IN ((1,2), (10, 20), (100, 200));

SELECT 3 IN (1, 3, NULL, 4);
Null Example 3

MySQL IN Operator Practical Examples

The following query finds the Customers whose Income is either 80000 or 90000

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Income IN (80000, 90000);
MySQL IN Numeric Values 5

For better understanding, Let me show you one more example. The following query returns the Customers whose Income is either 50000, 80000, or 90000

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Income IN (50000, 80000, 90000);
Numeric Values 6

IN Operator on String Column Example

This time, we are using the String data. The following query finds the Customers whose Qualifications is either Bachelor’s or Masters’s Degrees.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Qualification IN ('Bachelors', 'Masters Degree');
String Example 7

IN Operator and Order By Clause

You can also use the Order By clause along with this one. This query returns Customers whose Qualifications in either Bachelors, Masters Degree, or Education. Next, the Order By clause sorts the result based on the Income in ascending order.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Qualification IN ('Bachelors', 'Masters Degree', 'Education')
 ORDER BY Income;
MySQL IN Order By 8