Oracle NOT EXISTS Operator

In Oracle, NOT EXISTS operator allows you to check non existence of any record in a subquery. The NOT EXISTS operator can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Oracle NOT EXISTS OPERATOR Syntax

To check non existence of any record in a subquery, use the following syntax:

Syntax

SELECT col1, col2,....
FROM tablename
WHERE NOT EXISTS (SELECT col1 FROM tablename WHERE condition);

Parameters:

tablename: The table name from which you want to perform NOT EXISTS operator.

subquery: Usually a SELECT statement starts with SELECT * or column name. Oracle ignores the SELECT list from subquery.

Oracle NOT EXISTS OPERATOR example with SELECT statement

The following Oracle, NOT EXISTS operator is uses the Oracle NOT EXISTS operator:

Example

SELECT A.accountName 
FROM tblbankdemataccountcharges A 
WHERE NOT EXISTS (SELECT B.banks FROM tblbankheadquartersandaddress B WHERE A.bankId = B.pk);

Note:

In the above example, the NOT EXISTS operator will return all records from the "tblbankdemataccountcharges" table where there is no record in the tblbankheadquartersandaddress table with the matching "bankId".

« Previous