SQL IN

Summary: 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,...)Code language: SQL (Structured Query Language) (sql)

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 ...Code language: SQL (Structured Query Language) (sql)

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

expression NOT IN (value1, value2,...)Code language: SQL (Structured Query Language) (sql)

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...Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+--------+
| 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;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+--------+
| 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'Code language: SQL (Structured Query Language) (sql)

Try It

+---------------+
| 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);Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+------------+---------------+
| 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')Code language: SQL (Structured Query Language) (sql)

Try It

Summary

• Use the SQL IN operator to check if a value is in a set of values.
• Use the NOT opeator to negate the IN opeator, NOT IN.
• Use the `IN` with a subquery to combine two queries into a single query.