Oracle / PLSQL: NOT Condition

This Oracle tutorial explains how to use the Oracle NOT condition with syntax and examples.

Description

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

Syntax

The syntax for the NOT condition in Oracle/PLSQL is:

NOT condition

Parameters or Arguments

condition

The condition to negate.

Note

• The Oracle NOT condition requires that the opposite of the condition be must be met for the record to be included in the result set.

Example - Combine With IN condition

The Oracle NOT condition can be combined with the IN condition.

For example:

SELECT *
FROM customers
WHERE customer_name NOT IN ( 'CISCO', 'Hewlett Packard', 'GCP' );

This Oracle NOT example would return all rows from the customers table where the customer_name is not CISCO, Hewlett Packard, or GCP. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Example - Combine With IS NULL condition

The Oracle NOT condition can also be combined with the IS NULL condition.

For example,

SELECT *
FROM customers
WHERE last_name IS NOT NULL;

This Oracle NOT example would return all records from the customers table where the last_name does not contain a NULL value.

Example - Combine With LIKE condition

The Oracle NOT condition can also 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 in front of the LIKE condition, you are able to retrieve all customers whose customer_name does not start with 'S'.

Example - Combine With BETWEEN condition

The Oracle NOT condition can also be combined with the BETWEEN condition. Here is an example of how you would 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 would return all rows where the customer_id was NOT between 4000 and 4100, inclusive. It would be equivalent to the following Oracle SELECT statement:

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

Example - Combine With EXISTS condition

The Oracle NOT condition can also be combined with the EXISTS condition.

For example,

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

This Oracle NOT example would return all records from the suppliers table where there are no records in the orders table for the given supplier_id.