usage of not in in oracle

The NOT IN operator is used to check if a value is not in the result set of another group or subquery. It is used in the following way: Specify the value to check. Compare the value to a subquery or list of values. Returns all rows where the value is not in the comparison group.

NOT IN usage in Oracle

NOT IN operator is used in Oracle to check whether a value is not in another A group or subquery returns a result set.

Syntax:

<code>SELECT column_name
FROM table_name
WHERE column_name NOT IN (subquery | value_list);</code>

Where:

• column_name is the value to be checked.
• subquery is a nested query that returns a value.
• value_list is a comma-separated list of multiple values.

Example:

Check if a value is not in the result set returned by a subquery:

<code>SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);</code>

This Will select all customer names from the customers table that are not in the value of the customer_id column in the orders table (i.e. customers who have not ordered).

Check if a value is not in a value list:

<code>SELECT product_id
FROM products
WHERE product_id NOT IN (101, 102, 103);</code>

This will select all from the products table that are not in the value list( Product ID in 101, 102, 103).

Note:

• NOT IN operators are case-sensitive.
• If subquery or value_list is empty, NOT IN will return all rows.
• Oracle also provides a NOT IN ANY operator to check whether a value is not in the union of the result sets returned by multiple subqueries.

The above is the detailed content of usage of not in in oracle. For more information, please follow other related articles on the PHP Chinese website!