postgreSQL EXISTS Operator

The postgreSQL EXISTS operator is used to check the presence of any record in a subquery. If the subquery returns one or more records than EXISTS operator returns true. In case the subquery returns no row, the result is of EXISTS is false. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.EXISTS is often used with the correlated subquery.

EXISTS Operator Syntax :

The syntax of the EXISTS  Operator is −

SELECT column_name(s) 
FROM table_name
WHERE EXISTS 
  (SELECT column_name(s) 
   FROM table_name
   WHERE condition);

The syntax of the NOT EXISTS  Operator is −

SELECT column_name(s) 
FROM table_name
WHERE NOT EXISTS 
  (SELECT column_name(s) 
   FROM table_name
   WHERE condition);
 



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.

lfcdb=# SELECT Name, Address 
FROM Customer
WHERE EXISTS (SELECT * 
              FROM Order 
              WHERE Customer.ID = Order.CustomerID);
   


The result for the respective postgreSQL query is as follows −

Name Address
Vivaan Mumbai
Reyansh Chennai
Muhammad Udaipur





Visit :


Discussion


* You must be logged in to add comment.