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 -

Consider the Order table with the following records -

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 −

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 −

---

Visit :

---

Discussion