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 -

ID Name Age Address Salary
1 Aarav 36 Udaipur 35000
2 Vivaan 33 Mumbai 30000
3 Reyansh 28 Chennai 40000
4 Muhammad 29 Udaipur 50000
5 Sai 27 Mumbai 27000


Consider the Order table with the following records -

OID Date CustomerID Amount
101 2019-11-20 00:00:00 2 1800
102 2019-10-08 00:00:00 3 3000
103 2018-05-20 00:00:00 4 2500


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 −

Name Address
Aarav Udaipur
Sai Mumbai





Visit :


Discussion


* You must be logged in to add comment.