Oracle NOT EXIST Operator
The NOT EXISTS operator in Oracle allows you to remove the data from a given set of values. The operator syntax is shown in the following:
WHERE NOT EXISTS (subquery);
When used with a WHERE clause, the NOT EXISTS operator returns true if the specified subquery returns no rows. If the defined subquery returns any rows, the operator retruns false and excludes the returned rows from the result set.
The operator also returns false if the returned rows contain NULL values.
Example Demonstration
Although the NOT EXISTS operator in Oracle may sound daunting, it is relatively simple and intuitive, as discussed in the following example.
Sample Table:
Suppose we have tables that contain the employee information and the department in which that customer reserves.
We can use the NOT EXISTS operator to remove all the employees who do not belong to any particular department, as shown in the following query:
In this case, the where clause creates a subquery that returns the rows where the value of the employee_id column in the employees does not exist in the employee_id column of the department table.
Using the NOT EXISTS operator negates the results of the subquery.
We can also use the NOT EXITS operator with other DML statements such as DELETE and UPDATE.
For example, the following query demonstrates how to use the UPDATE operator to update the value of a given table column:
SET commission = 10
WHERE NOT EXISTS(SELECT NULL FROM employees WHERE commission_pct = NULL);
In this case, the update statement should set the value of the commission column to 10 for all the rows where the value of the commission_pct column is not NULL.
This query demonstrates a simple and efficient way of updating a table with a given value without manually filtering the results.
Conclusion
This short tutorial introduces you to the NOT EXISTS operator in the Oracle database to negate the result of a given subquery. In most cases, we use this operator in conjunction with the conditional statements such as where to filter out the results.
