Preamble
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
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…