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 Oracle IN operator that determines whether an expression matches a list of value is as follows:

expression [NOT] IN (v1,v2,...)
Code language: CSS (css)

and syntax of an expression matches a subquery:

expression [NOT] IN (subquery)
Code language: CSS (css)

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 expression equals to 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;
Oracle IN example

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:

Oracle IN example 2

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:

Oracle NOT IN example

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)
Oracle IN Subquery Example

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)
Oracle IN subquery result

And 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:

Customers 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 );
Oracle NOT IN Subquery example

E) Oracle IN vs. OR

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

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

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.

Was this tutorial helpful?