Oracle in clause

Oracle in clausePrimary Perplexed Oracle Basic review: 0 Oracle Technology QQ Group: 175248146

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 subquery.

Introduction to the Oracle in operator

The Oracle IN operator determines whether a value matches any value in a list or subquery.

Note: A subquery is a query that is nested in another query, and you can understand the subquery usage in the subquery tutorial.

The syntax of an Oracle operator that determines whether an expression matches a list of values is IN as follows:

[NOT] IN (v1,v2,...)

Sql

And the syntax of the expression matches the subquery:

[NOT] IN (subquery)

Sql

Parameters

In the above syntax, the meaning of each parameter is described here:

• expression-This is any validexpression that can be a column of a table to match.
• v1, v2, v3. -The IN operator is followed by a comma-separated list of values for testing matches. All values must have the same data type as the expression.
• subquery -The subquery returns a column of result sets to test the match. The column must also have the same data type as the expression.

return value

The operator returns ifthe value of an expression is equal to any value in the list of values or a result set returned by a subquery IN true . Otherwise, it returns false .

NOTINThe result of the operator negation operator.

examples of Oracle in operators

We'll use the and tables in the sample database to orders employees demonstrate:

1. Oracle in Example

The following statement finds all salesperson IDs 54 , 55 and the 56 orders that are responsible:

SELECT    order_id,customer_id,status,salesman_idFROM ordersWHERE salesman_id IN (54,55,56)ORDER BY order_id;

Sql

Execute the above query statement to get the following results-

As shown in, the query result set returns all orders in the salesman_id column with a value of + + , on , or + . Similarly, the following example retrieves the value of the status ( status ) column as a sales order information for "Pending" or "Canceled" :

SELECT    order_id, customer_id, status, salesman_idFROM ordersWHERE status IN(‘Pending‘,‘Canceled‘)ORDER BY order_id;

Sql

Execute the above query statement to return the value of the status ( status ) column "Pending" or "Canceled" the sales order information as follows-

2. Oracle Not in example

This example shows how to find status "Pending" "Canceled" an order with a status () that is not OR:

SELECT    order_id, customer_id, status, salesman_idFROM ordersWHERE status NOT IN( ‘Shipped‘, ‘Canceled‘)ORDER BY order_id;

Sql

Execute the above query statement return status The value of the status () column is not "Pending" or "Canceled" the sales order information, as shown below-

3. Oracle in subquery sample

The following example returns the ID, first name, and last name of the salesperson who owns the order status cancel ( Canceled ), referring to the following query statement-

SELECT employee_id, first_name, last_nameFROM employeesWHERE employee_id IN( SELECT DISTINCT salesman_id FROM orders WHERE status = ‘Canceled‘ )ORDER BY first_Name;

Sql

Execute the above query statement to get the following results-

In this example, the subquery first executes and returns a list of salesperson IDs:

SELECT    DISTINCT salesman_idFROM    ordersWHERE    status = ‘Canceled‘

Sql

Execute the above query statement to get the following results-

These salespeople are id used for external queries to find all employees in the ID ID same list as the salesperson id .

4. Oracle Not in subquery sample

See customers orders The ER diagram below and table:

The following example uses NOT IN to find customers who have not yet placed any orders:

SELECT    customer_id, nameFROM customersWHERE    customer_id NOT IN( SELECT customer_id FROM orders );

Sql

Execute the above query statement to get the following results-

5. Oracle in and OR operators

The following example shows how to obtain a sales 60 order with salesperson ID, 61 and 62 :

SELECT    customer_id, status, salesman_idFROM ordersWHERE salesman_id IN(60, 61, 62)ORDER BY customer_id;

Sql

Execute the above query statement to get the following results-

The above statement is equivalent to:

SELECT    customer_id,    status,    salesman_idFROM ordersWHERE salesman_id = 60 OR salesman_id = 61 OR salesman_id = 62ORDER BY customer_id;

Sql

Notice that the expression:

NOT IN (60,61,62);

Sql

Has the same effect as the following statement:

= 60AND salesman_id = 61AND salesman_id = 62;

Sql

In this tutorial, you learned how to use the Oracle IN operator to query data that matches a list of values or subqueries.

Oracle in clause