Oracle / PLSQL: IN Condition

This Oracle tutorial explains how to use the Oracle IN condition with syntax and examples.

Description

The Oracle IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IN condition in Oracle/PLSQL is:

expression IN (value1, value2, ... value_n);

OR

expression IN (subquery);

Parameters or Arguments

expression

The value to test.

value1, value2, ... value_n

The values to test against expression.

subquery

This is a SELECT statement whose result set will be tested against expression. If any of these values matches expression, then the IN condition will evaluate to true.

Note

• The Oracle IN condition will return the records where expression is value1, value2..., or value_n.
• The Oracle IN condition is also called the Oracle IN operator.

Example - With Character

Let's look at an Oracle IN condition example using character values.

The following is an Oracle SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM customers
WHERE customer_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

This Oracle IN condition example would return all rows where the customer_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the SELECT, all fields from the customers table would appear in the result set.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_name = 'IBM'
OR customer_name = 'Hewlett Packard'
OR customer_name = 'Microsoft';

As you can see, using the Oracle IN condition makes the statement easier to read and more efficient.

Example - With Numeric

Next, let's look at an Oracle IN condition example using numeric values.

For example:

SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);

This Oracle IN condition example would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example - Using NOT operator

Finally, let's look at an IN condition example using the Oracle NOT operator.

For example:

SELECT *
FROM customers
WHERE customer_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');

This Oracle IN condition example would return all rows where the customer_name is not IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.