SQL IN Operator

The IN operator is a logical operator that checks whether a specified value matches any value in a list of values.

The IN operator help reduce the need of using multiple OR operator.

The IN operator will return TRUE when the search value is present within the list otherwise it will return FALSE.

The IN operator can be used with any data type in SQL.

Syntax

The basic syntax of IN operator can be given with:

SELECT column1_name, column2_name, ...columnN_name
FROM table_name
WHERE column_name IN (value1, value2, ...valueN);

OR

SELECT column1_name, column2_name, ...columnN_name
FROM table_name
WHERE column_name IN (SELECT statement);

Demo table

Let us suppose that we have a table called "employee" in our database with the following records:

Example Using the IN operator with Text Values

Let us see how to use the IN operator with text (string) values.

The following SQL statement will return only employees that work in either "Finance", "Customer Service", or "Marketing".

SELECT * 
FROM employee
WHERE dept_name IN ('Finance', 'Customer Service', 'Marketing');

After running the above SQL statement, we will get the following result set:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
+--------+-------------+-----------+------------+------------+-----------+------------------+

It is equivalent to run the following SQL statement:

SELECT *
FROM employee 
WHERE dept_name = 'Finance'
OR dept_name = 'Customer Service'
OR dept_name = 'Marketing';

As you can see, the use of the IN operator makes it easy to read and write an efficient SQL statement than using multiple OR operators.

Example Using the IN operator with Numeric Values

In this example, we will see how to use the IN operator with numeric Values.

The following SQL statement will return employees whose 'emp_no' are IN the following list (1002, 1003, 1007).

SELECT * 
FROM employee 
WHERE emp_no IN (1002, 1003, 1007);

After executing the above SQL statement, the result set will be as the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

As you can see, the SQL statement returned all the records from the "employee" table where the "emp_no" is either 1001, 1003, or 1007.

It is equivalent to the following SQL statement:

SELECT * 
FROM employee 
WHERE emp_no = 1002
OR emp_no = 1003
OR emp_no = 1007;

Example Using the IN Operator with the NOT operator

Let us see how to use the IN operator with the NOT operator.

When we use the NOT operator with the IN operator, it creates a NOT IN operator that will check if the specified column not in the list.

The following SQL statement will return only employees that are not working in "Customer Service", "Development", and "Human Resources":

SELECT *
FROM employee
WHERE dept_name NOT IN ('Customer Service', 'Development', 'Human Resources');

After running the SQL statement, the result set will look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Example Using the IN Operator with a Subquery

Let us see how to use the IN operator with a subquery.

For this example, in addition to the "employee" table, we will need a "department" table that will look like the following:

The following SQL statement will return employees that "depat_name" exists in the table "department".

SELECT * 
FROM employee
WHERE dept_name IN (SELECT dept_name FROM department);

After the execution of the above SQL statement, the result set will look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
+--------+-------------+-----------+------------+------------+-----------+------------------+

As we can see above, the query returned all the records of the "employee" table, which is normal because all the department name of the table "employee" exist in the "department" table.