Oracle IN condition

The Oracle IN condition (also called the IN operator) determines whether a value or a list of values corresponds to an expression in the specified resulting SELECT, INSERT, UPDATE or DELETE set.

Syntax for the IN condition in Oracle/PLSQL

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

Parameters and arguments of the condition

• expression – An expression to check.
• value1, value2, … value_n – Values to check the expression.

Note:
The Oracle IN condition will return records where the value of expression coincides with value1, value2, … or value_n.
No more than 1000 constants are allowed in the IN list.

Example with symbols

Let’s consider an example of the Oracle IN condition, using character values.

Below is the Oracle proposal SELECT, which uses the IN condition to compare character values:

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

This example of the Oracle IN condition will return all strings where customer_name or ‘IBM’, ‘Hewlett Packard’, or ‘Microsoft’. Since SELECT uses *, all fields from the customer_name table will appear in the resulting set.

The example above is equivalent to the following SELECT query:

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

As you can see, using the IN condition, the SELECT query becomes more readable and more efficient.

Example with numbers

Let’s consider the example of Oracle’s IN condition, using numeric values.
For example:

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

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

The example above is equivalent to the following SELECT request:

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

Example of NOT operator usage

And finally, let us consider an example of the IN condition, using the Oracle NOT operator.
For example:

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

This example of the Oracle IN condition will return all lines where customer_name is not ‘IBM’, ‘Hewlett Packard’, or ‘Microsoft’. Sometimes it is much more efficient to list the values that you do not want to get as a result, as opposed to the values that you want to get.

SQL tutorials: How To CREATE TABLE using enterprise manager 11g