Oracle IN

Summary: in this tutorial, you will learn how to use the Oracle IN operator to determine whether a value matches any value in a list or a subquery.

Introduction to Oracle IN operator

The Oracle IN operator determines whether a value matches any values in a list or a subquery.

A subquery is a query nested within another query, you will learn about the subquery in the subquery tutorial.

The syntax of the Oracle IN operator that determines whether an expression matches a list of values is as follows:

expression [NOT] IN (v1,v2,...)Code language: SQL (Structured Query Language) (sql)

and syntax of an expression matches a subquery:

expression [NOT] IN (subquery)Code language: SQL (Structured Query Language) (sql)

Arguments

In this syntax:

1) expression

The expression is any valid expression, which can be a column of a table that you want to match.

2) v1, v2, v3..

Followed the IN operator is a list of comma-separated values to test for a match. All the values must have the same data type as expression.

3) subquery

The subquery returns a result set of one column to test for the match. The column must also have the same data type as expression.

Return value

The IN operator returns true if the value of the expression equals any value in the list of values or the result set returned by the subquery. Otherwise, it returns false.

The NOT operator negates the result of the IN operator.

Oracle IN operator examples

We will use the orders and employees tables in the sample database for the demonstration:

A) Oracle IN examples

The following statement finds all orders which are in charge of the salesman id 54, 55, and 56:

SELECT
    order_id,
    customer_id,
    status,
    salesman_id
FROM
    orders
WHERE
    salesman_id IN (
        54,
        55,
        56
    )
ORDER BY
    order_id;

The query returned all orders whose values in the salesman_id column are 54, 55 or 56:

Similarly, the following example retrieves sales orders whose statuses are Pending or Canceled:

SELECT
    order_id,
    customer_id,
    status,
    salesman_id
FROM
    orders
WHERE
    status IN(
        'Pending',
        'Canceled'
    )
ORDER BY
    order_id;Code language: JavaScript (javascript)

The query returned all orders whose statuses are Pending or Canceled:

B) Oracle NOT IN example

The example shows how to find orders whose statuses are not Shipped and Canceled:

SELECT
    order_id,
    customer_id,
    status,
    salesman_id
FROM
    orders
WHERE
    status NOT IN(
        'Shipped',
        'Canceled'
    )
ORDER BY
    order_id;Code language: JavaScript (javascript)

The result is:

C) Oracle IN subquery example

The following example returns the id, first name, and last name of salesmen who are in charge of orders that were canceled

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    employee_id IN(
        SELECT
            DISTINCT salesman_id
        FROM
            orders
        WHERE
            status = 'Canceled'
    );
ORDER BY
    first_Name;Code language: JavaScript (javascript)

In this example, the subquery executes first and returns a list of salesman ids:

SELECT
	DISTINCT salesman_id
FROM
	orders
WHERE
	status = 'Canceled'Code language: SQL (Structured Query Language) (sql)

These salesman ids are used for the outer query which finds all employees whose ids are equal to any id in the salesman id list

D) Oracle NOT IN subquery example

See the following customers and orders tables:

The following example uses the NOT IN to find customers who have not placed any orders:

SELECT
    customer_id,
    name
FROM
    customers
WHERE
    customer_id NOT IN(
        SELECT
            customer_id
        FROM
            orders
    );  

E) Oracle IN vs. OR

The following example shows how to get the sales orders of the salesman with id 60, 61, and 62:

SELECT
    customer_id,
    status,
    salesman_id
FROM
    orders
WHERE
    salesman_id IN(
        60,
        61,
        62
    )
ORDER BY
    customer_id;

It is equivalent to:

SELECT
    customer_id,
    status,
    salesman_id
FROM
    orders
WHERE
    salesman_id = 60
    OR salesman_id = 61
    OR salesman_id = 62
ORDER BY
    customer_id;

Note that the expression:

salesman_id NOT IN (60,61,62);Code language: SQL (Structured Query Language) (sql)

has the same effect as:

salesman_id != 60
AND salesman_id != 61
AND salesman_id != 62;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle IN operator to query data that matches a list of values or a subquery.