SQL | EXISTS AND NOT EXISTS OPERATORS

In this article, we'll learn about the EXISTS and NOT EXISTS operators used in Correlated Subqueries. If you want to get hold of Correlated Subqueries you can check out this link.

For ALL and ANY operators, you can check this link.

---

1. EXISTS: It is used to check the existence of any record in a subquery. It returns true if one

or more than one tuples(records) are present in the table.

Syntax:

 SELECT column_name_1, column_name_2, .., column_name_n
 FROM table_name
 WHERE EXISTS(SELECT column_name FROM table_name WHERE condition);

Example: To fetch the product name of the orders with a single quantity.

Products Orders

SELECT product_name
FROM Products  
WHERE EXISTS(SELECT * FROM Orders WHERE Products.product_id = 
             Orders.product_id AND quantity=1);)

---

2. NOT EXISTS: It negates the logic of EXISTS Operator. The NOT EXISTS operator returns

true if the subquery returns no record.

Syntax:

SELECT column_name_1, column_name_2, .., column_name_n
FROM table_name  
WHERE NOT EXISTS(SELECT column_name FROM table_name WHERE condition);

Example: To match all products records that have no associated orders quantity less than 2.

SELECT product_name
FROM Products  
WHERE NOT EXISTS(SELECT * FROM Orders WHERE Products.product_id =         
                 Orders.product_id AND quantity>=2);

---

Happy Learning!

Follow us on Instagram @programmersdoor

Join us on Telegram @programmersdoor

Do comment if you find any error in the above article, or if you have an easier way to explain the topic. Follow Programmers Door for more. #blog #interview #placement #learn #computer #science #CorrelatedSubquery #Subquery #DBMS #ALL #ANY