Using IN Condition in Oracle

This article is written about how to use the Oracle IN condition with syntax and examples.

Description

The Oracle IN condition is used to assist limit the need to use a couple of OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

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

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

OR

expression IN (subquery);

Parameters or Arguments

expression The cost to test. value1, value2, … value_n The values to take a look at towards expression. subquery This is a SELECT assertion whose result set will be tested towards expression. If any of these values fits expression, then the IN situation will evaluate to true.

Note

The Oracle IN circumstance will return the records the place expression is value1, value2…, or value_n. The Oracle IN situation is also known as the Oracle IN operator.

Example – With Character

Let’s seem at an Oracle IN circumstance example the usage of character values.

The following is an Oracle SELECT assertion that uses the IN condition to examine personality values:

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

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

The above IN instance is equal 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 declaration easier to study and extra efficient.

Example – With Numeric

Next, let’s seem 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 instance would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

The above IN instance is equal 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 seem at an IN situation instance using the Oracle NOT operator.

For example:

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

This Oracle IN circumstance example would return all rows where the customer_name is not IBM, Hewlett Packard, or Microsoft. Sometimes, it is greater environment friendly to list the values that you do no longer want, as hostile to the values that you do want.