The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Example

 Return all employee from 'Germany', 'Spain', or 'Italy':
 SELECT *
 FROM  employee 
              
 WHERE  Country  IN (  'Germany' ,
 'Spain' , 'Italy' );

You can click on above box to edit the code and run again.

Output

Syntax

 SELECT  column_name(s)
 FROM  table_name
              
 WHERE  column_name IN (value1, value2, ...);

Demo Employee table

This employee table is used for examples:

NOT IN

By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the list.

Syntax

Return all employee that are NOT from 'Germany', 'Spain', or 'Italy':


SELECT*
 FROM  employee

WHERE Country  NOT IN ( 'Germany',
'Spain','Italy');

You can click on above box to edit the code and run again.

Output

IN (SELECT)

You can also use IN with a subquery in the WHERE clause.

With a subquery you can return all records from the main query that are present in the result of the subquery.

Example

Return all employee that have an order in the products table:


SELECT*
 FROM  employee

WHERE Employee_id  IN ( SELECT
 Employee_id
 FROM products);

You can click on above box to edit the code and run again.

Output

Demo Products table

This product table is used in examples:

NOT IN (SELECT)

The result in the example above returned 9 records, that means that there are 6 employee that haven't placed any orders.

Let us check if that is correct, by using the NOT IN operator.

Example

Return all employee that have NOT placed any order in the products table:


SELECT*
FROM  employee

WHERE Employee_id  NOT IN ( SELECT
Employee_id
 FROM products);

You can click on above box to edit the code and run again.

Output