SQL IN Operator

In this tutorial, you will learn how to use the SQL IN operator to check if a value is in a set of values.

Introduction to SQL IN Operator

The IN is a logical operator in SQL. The IN operator returns true if a value is in a set of values or false otherwise.

The following illustrates the syntax of the IN operator:

expression IN (value1,value2,...)

Technically, you can substitute the IN operator with the = and OR operators The condition that uses the IN operator can be rewritten using one or more OR operators as follows:

expression = value1 OR expression = value2 OR ...

To negate the IN operator, you use the NOT operator:

expression NOT IN (value1, value2,...)

The NOT IN operator returns true if the expression does not equal any values in the list or false otherwise.

To substitute the IN operator, you can use the != and AND operators as follows:

expression != value1 AND expression != value2 AND...

Notice that if any value in the list (value1,value2,...) is null, the IN operator returns no rows.

In practice, you often use the IN and NOT IN operators in the  WHERE clause of the SELECT statement to select rows with a value in a set of values. Also, you’ll use the IN operator in subqueries.

SQL IN examples

We will use the  employees table in the sample database to demonstrate the functionality of the IN operator.

The following example uses the IN operator to find employees with the job id is 8, 9, or 10:

SELECT
    employee_id,
    first_name,
    last_name,
    job_id
FROM
    employees
WHERE
    job_id IN (8, 9, 10)
ORDER BY
    job_id;

+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | job_id |
+-------------+------------+-----------+--------+
|         203 | Susan      | Mavris    |      8 |
|         103 | Alexander  | Hunold    |      9 |
|         104 | Bruce      | Ernst     |      9 |
|         105 | David      | Austin    |      9 |
|         106 | Valli      | Pataballa |      9 |
|         107 | Diana      | Lorentz   |      9 |
|         201 | Michael    | Hartstein |     10 |
+-------------+------------+-----------+--------+

The following example uses the NOT IN operator to find employees whose job’s id is neither 7, 8, nor 9:

SELECT
    employee_id,
    first_name,
    last_name,
    job_id
FROM
    employees
WHERE
    job_id NOT IN (7, 8, 9)
ORDER BY
    job_id;

+-------------+-------------+-------------+--------+
| employee_id | first_name  | last_name   | job_id |
+-------------+-------------+-------------+--------+
|         206 | William     | Gietz       |      1 |
|         205 | Shelley     | Higgins     |      2 |
|         200 | Jennifer    | Whalen      |      3 |
|         100 | Steven      | King        |      4 |
|         102 | Lex         | De Haan     |      5 |
|         101 | Neena       | Kochhar     |      5 |
|         109 | Daniel      | Faviet      |      6 |
|         113 | Luis        | Popp        |      6 |
|         110 | John        | Chen        |      6 |
|         111 | Ismael      | Sciarra     |      6 |
|         112 | Jose Manuel | Urman       |      6 |
|         201 | Michael     | Hartstein   |     10 |
|         202 | Pat         | Fay         |     11 |
|         204 | Hermann     | Baer        |     12 |
|         118 | Guy         | Himuro      |     13 |
...

2) Using SQL IN opeator with a subquery example

A subquery is a query nested inside another query. Let’s take a look at an example:

The following query returns the department id of the Marketing and Sales departments:

SELECT 
    department_id
FROM
    departments
WHERE
    department_name = 'Marketing'
        OR department_name = 'Sales'

+---------------+
| department_id |
+---------------+
|             2 |
|             8 |
+---------------+

The query returns a list of two department ids.

And you can pass the id list to the IN operator to find employees who work in the Marketing and Sales departments like this:

SELECT
    employee_id,
    first_name,
    last_name,
    department_id
FROM
    employees
WHERE
    department_id IN (2, 8);

+-------------+------------+------------+---------------+
| employee_id | first_name | last_name  | department_id |
+-------------+------------+------------+---------------+
|         145 | John       | Russell    |             8 |
|         146 | Karen      | Partners   |             8 |
|         176 | Jonathon   | Taylor     |             8 |
|         177 | Jack       | Livingston |             8 |
|         178 | Kimberely  | Grant      |             8 |
|         179 | Charles    | Johnson    |             8 |
|         201 | Michael    | Hartstein  |             2 |
|         202 | Pat        | Fay        |             2 |
+-------------+------------+------------+---------------+

To combine two above queries into a single query, you can use the first query in place of the list inside parentheses followed the IN operator:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    department_id IN (SELECT 
            department_id
        FROM
            departments
        WHERE
            department_name = 'Marketing'
                OR department_name = 'Sales')