PostgreSQL NOT EXISTS
In PostgreSQL NOT EXISTS operator is negation of EXISTS Operator. If the subquery does not returns any records than NOT EXISTS operator returns true else it returns FALSE.. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.NOT Exists operators are also used with correlated subquery.
NOT EXISTS Syntax :
The syntax of the NOT EXISTS statement is −
SELECT col_name... FROM table_name1 t1 WHERE NOT EXISTS (SELECT 'x' FROM table_name2 t2 WHERE t2.col_name = t1.col_name)
NOT EXISTS Operator Example :
Consider the Customer table with the following records -
Consider the Order table with the following records -
TEST CASE 1:- The following code is an example, which would fetch the Name, Address from Customer table where Customer.ID = Order.CustomerID is does not exist.
SELECT Name, Address FROM Customer WHERE NOT EXISTS (SELECT * FROM Order WHERE Customer.ID = Order.CustomerID);
The result for the respective PostgreSQL query is as follows −