MYSQL IN and NOT IN Operators
In this tutorial, we shall see how to use IN and NOT IN operators in mysql.
We shall use the following employee table and branches table to which we will query using the above operators.
| empno | name | age | role | location | salary |
|---|---|---|---|---|---|
| 001 | Andrew | 30 | Manager | India | 100000 |
| 002 | Beslin | 28 | Business Analyst | India | 50000 |
| 003 | Joanna | 23 | Senior Developer | USA | 500000 |
| 004 | Rayan | 26 | Technical Lead | Canada | 500000 |
| branchid | country |
|---|---|
| 1 | India |
| 2 | USA |
IN Operator
The IN operator in mysql is used to check a list of values with where clause.
Syntax for IN operator
select * from table_name
where column_name IN (value1, value2, ...);
Example
select * from employee
where location IN ('India','USA');
This query will select the records of the employee whose location is India or USA.
Ouptut
| empno | name | age | role | location | salary |
|---|---|---|---|---|---|
| 001 | Andrew | 30 | Manager | India | 100000 |
| 002 | Beslin | 28 | Business Analyst | India | 50000 |
It is also possible to query with IN operator within a set of records.
Syntax
select column_name(s)
from table_name
WHERE column_name IN (SELECT STATEMENT);
Example
select * from employee
where location IN (select country from branches);
The above query will retrieve the records of the employee's whose location is India and USA.
Output
| empno | name | age | role | location | salary |
|---|---|---|---|---|---|
| 001 | Andrew | 30 | Manager | India | 100000 |
| 002 | Beslin | 28 | Business Analyst | India | 50000 |
NOT IN Operator
The NOT IN operator is used to retrieve the records that are not in the list specified.
Syntax for NOT IN Operator
select * from table_name
where column_name NOT IN (value1, value2, ...);
Example
select * from employee
where location NOT IN ('India');
This will return the records of the employee's whose location is not India.