Oracle NOT operator

The Oracle NOT condition (also called NOT operator) is used to negate the condition in SELECT, INSERT, UPDATE or DELETE sentences.

Syntax of the NOT condition in Oracle/PLSQL

NOT condition

Parameters and arguments of the condition

• Condition – Condition of denial.

Note:
The Oracle NOT condition requires that the opposite conditions must be met for the entries to be included in the resulting set.

Example of a combination with the IN condition

The Oracle NOT condition can be combined with the IN condition.
For example:

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

In this example, Oracle NOT will return all rows from the customer_name table where customer_name is not IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you want.

Example of a combination with the IS NULL condition

The Oracle NOT condition can be combined with the IS NULL condition.
For example:

SELECT *
FROM customers
WHERE last_name IS NOT NULL;

In this example, Oracle NOT will return all entries from the customer table where last_name does not contain a value of NULL.

Example of a combination with the LIKE condition

The Oracle NOT condition can be combined with the LIKE condition.
For example:

SELECT customer_name
FROM customers
WHERE customer_name NOT LIKE 'S%';

By placing the Oracle NOT operator before the LIKE condition, you can get all records of the customers table whose customer_name values do not start with ‘S’.

Example of combination with the BETWEEN condition

The Oracle NOT condition can also be used with the BETWEEN condition. Below is an example of how to combine the NOT operator with the BETWEEN condition.
For example:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 4000 AND 4100;

This Oracle NOT example will return all lines where the customer_id value was not between 4000 and 4100 (inclusive). This would be equivalent to the following Oracle SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 4000
OR customer_id > 4100;

Example of combination of NOT with EXISTS condition

The Oracle NOT condition can also be used with the EXISTS condition.
For example:

SELECT *
FROM suppliers
WHERE DOES NOT EXIST (SELECT *
FROM orders
WHERE suppliers.supplier_id = orders.supplier_id);

This Oracle NOT example will return all rows in the suppliers table where there are no entries in the orders table for the given supplier_id.

Oracle Tutorial: Logical Operator – AND OR NOT Operators