SQL | Different operators used with Correlated Subqueries

In this article, we'll learn about different operators used with Correlated Subqueries.

If you want to get hold of correlated subqueries you can check out this link.

The basic operators used with it are ALL, ANY, EXISTS, and NON-EXISTS. In this article, we'll understand the use of ALL and ANY operator.

---

1. ALL: It returns true if all of the subquery values meet the condition. It works similar to AND

operator.

Syntax:

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

It (ALL) operator must be preceded by a comparison operator such as <, >, <=, >=, etc and followed by a subquery.

Products Orders

Example: Find the name of the product if all the records in the Orders have quantity either

equal to 5 or 1.

Query:

SELECT product_name 
FROM Products
WHERE product_id = ALL (SELECT product_id
                       FROM Orders
                       WHERE quantity = 5 OR quantity = 1);

---

2. ANY: It returns true if any of the subquery values meet the condition. It works similar to

AND operator.

Syntax:

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

Example: The following SQL statement returns true and lists the product names if it finds

ANY records in the Orders table that quantity = 10.

Query:

SELECT product_name  
FROM Products 
WHERE product_id = ANY (SELECT product_id FROM Orders                     
                        WHERE quantity = 10);

In the next article, we'll understand the use of EXISTS and NON-EXISTS operators.

---

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