MySQL IN Operator

In this MySQL tutorial, we are going to learn all about MySQL IN Operator with the help of the example. In MySQL, IN operator is used to match the specified values in a list of values. In the previous tutorial, we have seen MySQL AND Operator and MySQL OR operator with the help of examples.

MySQL IN Operator Introduction

Let’s see what is means of MySQL IN Operator is.IN operator in MySQL allow us to match the specified value from any value in a list of values.IN operator accepts a list of values that you want to match from.

Syntax

This is the basic syntax of MySQL IN Operator.

SELECT select_list FROM table_name WHERE column IN (value1, value2, value3,....):

Break down the above syntax:

• select_list represent the column which you want to select.
• table_name represnt the table name.
• column represent the column name which you want to match.
• (value1, value2, value3,….) are the values which you want to match from.

MySQL IN Operator Examples

We have a students table that contains the following records.

Example

SELECT * FROM students;

Output

+-------+------------+-----------+--------+---------------------+
| st_id | first_name | last_name | course | created_at          |
+-------+------------+-----------+--------+---------------------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |
+-------+------------+-----------+--------+---------------------+

a). We want to select all the students where last_name is Rao, Kumar, and Singh.

SELECT * FROM students WHERE last_name IN ('Rao', 'Kumar', 'Singh');

Output

+-------+------------+-----------+--------+---------------------+
| st_id | first_name | last_name | course | created_at          |
+-------+------------+-----------+--------+---------------------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |
|     4 | Pankaj     | Singh     | Btech  | 2021-11-13 14:54:28 |
|     7 | Abhi       | Kumar     | MCA    | 2021-11-28 11:43:40 |
|     8 | Kartike    | Singh     | Btech  | 2021-11-28 11:44:22 |
+-------+------------+-----------+--------+---------------------+

b). We want to select all the students where last_name is not Rao, Kumar, and Singh.

SELECT * FROM students WHERE last_name NOT IN ('Rao', 'Kumar', 'Singh');

Output

+-------+------------+-----------+--------+---------------------+
| st_id | first_name | last_name | course | created_at          |
+-------+------------+-----------+--------+---------------------+
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |
|     5 | Hayati     | Kaur      | LLB    | 2021-11-13 14:54:28 |
|     6 | Aysha      | Garima    | BCA    | 2021-11-13 14:54:28 |
+-------+------------+-----------+--------+---------------------+

c). Select all the students whose st_id is 1, 2, and 3.

SELECT * FROM students WHERE st_id IN (1, 2, 3);

Output

+-------+------------+-----------+--------+---------------------+
| st_id | first_name | last_name | course | created_at          |
+-------+------------+-----------+--------+---------------------+
|     1 | Vishvajit  | Rao       | MCA    | 2021-11-13 14:26:39 |
|     2 | John       | Doe       | Mtech  | 2021-11-13 14:26:39 |
|     3 | Shivam     | Kumar     | B.A.   | 2021-11-13 14:26:39 |
+-------+------------+-----------+--------+---------------------+

Note:- You can verify all the output from the above main table.

Conclusion:

So in this article, we have seen everything about MySQL IN Operator with the help of examples.IN Operator in MySQL is very useful when you want to match all the values from any column value.
I hope after this article, you don’t have any confusion regarding MySQL IN Operator.

If you like this article, please share and keep visiting for further MySQL interesting tutorials.

Ref:- Click Here

---

Frequently Asked Questions

Thanks for your valuable time ….