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
IN operator determines whether a value matches any values in a list or a subquery.
The syntax of Oracle
IN operator that determines whether an expression matches a list of value is as follows:
expression [NOT] IN (v1,v2,...)
and syntax of an expression matches a subquery:
expression [NOT] IN (subquery)
In this syntax:
expression is any valid expression, which can be a column of a table that you want to match.
v1, v2, v3..
IN operator is a list of comma-separated values to test for a match. All the values must have the same data type as
The subquery returns a result set of one column to test for the match. The column must also have the same data type as
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.
NOT operator negates the result of the
Oracle IN operator examples
We will use the
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
SELECT order_id, customer_id, status, salesman_id FROM orders WHERE status IN( 'Pending', 'Canceled' ) ORDER BY order_id;
The query returned all orders whose statuses are
B) Oracle NOT IN example
The example shows how to find orders whose statuses are not
SELECT order_id, customer_id, status, salesman_id FROM orders WHERE status NOT IN( 'Shipped', 'Canceled' ) ORDER BY order_id;
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;
In this example, the subquery executes first and returns a list of salesman ids:
SELECT DISTINCT salesman_id FROM orders WHERE status = 'Canceled'
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
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 salesman 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);
has the same effect as:
salesman_id != 60 AND salesman_id != 61 AND salesman_id != 62;
In this tutorial, you have learned how to use the Oracle
IN operator to query data that matches a list of value or a subquery.