MySQL NOT EXISTS Operator

In MySQL, NOT EXISTS operator allows you to check non existence of any record in a subquery. The NOT EXISTS operator return true if the subquery returns zero row. The NOT EXISTS operator can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Note: MySQL statements that use the NOT EXISTS Condition are not efficient because the sub-query is RE-RUN for EVERY row in the outer query's table.

MySQL 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. MySQL ignores the SELECT list from subquery.

MySQL NOT EXISTS OPERATOR example with SELECT statement

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

Example

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

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".

You can use MySQL Command Line Client to use NOT EXISTS operator in subquery. It will look like this: