What is the use of ‘ALL’, ‘ANY’, ’SOME’, ’IN’ operators with MySQL subquery?

The ‘ALL’, ‘ANY’, ’SOME’, ’IN’ operator compares value to every value returned by the subquery. All of these operators must follow a comparison operator. The syntax of using these operators with MySQL subquery is as follows −

Syntax for using ‘ALL’

Operand comparison_operator ALL (subquery)

Syntax for using ‘ANY’

Operand comparison_operator ANY (subquery)

Syntax for using ‘SOME’

Operand comparison_operator SOME (subquery)

Syntax of using ‘IN’

Operand comparison_operator IN (subquery)

Examples

We are using the data from the following tables to illustrate the use of ‘ALL’, ‘ANY’, ’SOME’, ’IN’ operators with a subquery.

mysql> Select * from Customers;
+-------------+----------+
| Customer_Id | Name     |
+-------------+----------+
|           1 | Rahul    |
|           2 | Yashpal  |
|           3 | Gaurav   |
|           4 | Virender |
+-------------+----------+
4 rows in set (0.00 sec)

mysql> Select * from Reservations;
+------+-------------+------------+
| ID   | Customer_id | Day        |
+------+-------------+------------+
|    1 |           1 | 2017-12-30 |
|    2 |           2 | 2017-12-28 |
|    3 |           2 | 2017-12-29 |
|    4 |           1 | 2017-12-25 |
|    5 |           3 | 2017-12-26 |
+------+-------------+------------+
5 rows in set (0.00 sec)

The query below uses ‘ALL’ operator with a subquery and returns the result after comparing all the values returned by the subquery.

mysql> SELECT customer_id from Customers WHERE customer_id <> ALL(Select customer_id from reservations);
+-------------+
| customer_id |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

The query below uses ‘ANY’ operator with a subquery and returns the result after comparing all the values returned by the subquery.

mysql> SELECT customer_id from customers WHERE customer_id = ANY(Select customer_id from reservations);
+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+
3 rows in set (0.00 sec)

The query below uses ‘SOME’ operator with a subquery and returns the result after comparing all the values returned by the subquery.

mysql> SELECT customer_id from customers WHERE customer_id = Some(Select customer_id from reservations);
+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+
3 rows in set (0.00 sec)

The query below uses ‘IN’ operator with a subquery and returns the result after comparing all the values returned by the subquery.

mysql> SELECT customer_id from customers WHERE customer_id IN (Select customer_id from reservations);
+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+
3 rows in set (0.00 sec)