This article is written about how to use the Oracle NOT circumstance with syntax and examples.
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.
The syntax for the NOT circumstance in Oracle/PLSQL is:
Parameters or Arguments
The condition to negate.
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.
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.
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.
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.
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.
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.