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.


Most Read