SQL IN Operator in Where Clause

<< Previous - SQL LIKE Operator

Next - SQL UNION Operator >>

• SQL IN operator is used to select the list of records from the table based on a set of target values.
• This operator is also known as the set membership test.

Syntax

SELECT Column-name1,Column-name2....Column-nameN FROM Table-name
WHERE Column-name IN(value1,value2...);

Example

Below is the demo Staff_table from the Collegedb database.

+-------+-----------+-------+--------+
| ID_No | Name      | Dept  | Salary |
+-------+-----------+-------+--------+
|   101 | Madhan    | IT    |  20000 |
|   102 | Kamini    | IT    |  25000 |
|   103 | Senthil   | CSE   |  30000 |
|   104 | Vani      | CSE   |  30000 |
|   105 | Aadhi     | MECH  |  35000 |
|   106 | Arun      | CIVIL |  35000 |
|   107 | Vasanthi  | CIVIL |  28000 |
|   108 | Balan     | CIVIL |  27500 |
|   109 | Devid     | MECH  |  23500 |
|   110 | Aruna     | ADMIN |  15000 |
|   111 | Boomi     | ADMIN |  17000 |
|   112 | Kathirvel | ADMIN |  17550 |
|   113 | Kathir    | ADMIN |  22000 |
+-------+-----------+-------+--------+

Following is the query statement to select the list of staff members who are working in the department of MECH, IT and CSE.

mysql> SELECT * FROM staff_table WHERE Dept IN ('MECH','IT','CSE');
+-------+---------+------+--------+
| ID_No | Name    | Dept | Salary |
+-------+---------+------+--------+
|   101 | Madhan  | IT   |  20000 |
|   102 | Kamini  | IT   |  25000 |
|   103 | Senthil | CSE  |  30000 |
|   104 | Vani    | CSE  |  30000 |
|   105 | Aadhi   | MECH |  35000 |
|   109 | Devid   | MECH |  23500 |
+-------+---------+------+--------+
6 rows in set (0.10 sec)

<< Previous - SQL LIKE Operator

Next - SQL UNION Operator >>