SQL IN

Summary: in this tutorial, you will learn how to use the SQL IN operator to compare values in a column against a set of values.

Introduction to SQL IN Operator

The IN the operator is a logical operator that allows you to compare a value against a set of values. The IN operator returns true if the value is within the set of values. Otherwise, it returns false or unknown.

The following illustrates the syntax of the IN operator:

expression IN (value1,value2,...)

The IN operator is often used in the  WHERE clause of the SELECT, UPDATE, and DELETE statements. It is also used extensively in subqueries.

The condition that uses the IN the operator can be rewritten using one or more OR operators as follows:

expression = value1 OR expression = value2 OR ...

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

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

The NOT IN the operator returns true if the expression does not match any value in the list, otherwise, it returns false.

Similarly, you can rewrite the NOT IN operator by using the AND operators as shown below:

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

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

SQL IN examples

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

To find all employees whose job’s id is either 8, 9, or 10, you use the IN operator as follows:

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

To query an employee whose job’s id is neither 7, 8, nor 9, you use the NOT IN operator as the following statement:

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

SQL IN with subquery examples

A subquery is a query nested inside another query. The following query finds department id of the Marketing and Sales departments:

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

The query returns a list of two departments. You can supply this list to the IN operator to find all employees who work in the Marketing and Sales departments.

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

To make it more simple, you can use the  subquery technique that nests the first query inside the second query:

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')

In this tutorial, you have learned how to use the SQL  IN operator to test whether a value is within a list of values or subquery.