SQL EXISTS Operator

The SQL 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. It can be used in a SELECT, UPDATE, INSERT or DELETE statement.

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 (Using Exist):- The following code is an example, which would fetch the Name, Address from Customer table where Customer.ID = Order.CustomerID.

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


The result for the respective sql query is as follows −

Name Address
Vivaan Mumbai
Reyansh Chennai
Muhammad Udaipur



TEST CASE 2 (Using NOT EXISTS):- 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 sql query is as follows −

Name Address
Aarav Udaipur
Sai Mumbai





Visit :


Discussion


* You must be logged in to add comment.