Using NOT Condition in Oracle

This article is written about how to use the Oracle NOT circumstance with syntax and examples.

Description

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

Syntax

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

NOT condition

Parameters or Arguments

condition

The condition to negate.

Note

The Oracle NOT condition requires that the contrary of the condition should be met for the report to be blanketed in the end result set.

Example – Combine With IN condition

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

For example:

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

This Oracle NOT instance would return all rows from the clients desk the place the customer_name is now not IBM, Hewlett Packard, or Microsoft. Sometimes, it is greater efficient to list the values that you do now not want, as antagonistic to the values that you do want.

Example – Combine With IS NULL condition

The Oracle NOT circumstance can additionally be blended with the IS NULL condition.

For example,

SELECT *
FROM customers
WHERE last_name IS NOT NULL;

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

Example – Combine With LIKE condition

The Oracle NOT condition can also be mixed with the LIKE condition.

For example:

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

By setting the Oracle NOT Operator in front of the LIKE condition, you are in a position to retrieve all customers whose customer_name does now not begin with ‘S’.

Example – Combine With BETWEEN condition

The Oracle NOT condition can additionally be mixed 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 instance would return all rows the place the customer_id used to be 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 situation can also be blended 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 instance would return all information from the suppliers table where there are no files in the orders table for the given supplier_id.