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)

Sharon Christine

Published on 21-Feb-2018 13:24:17

• Related Questions & Answers
• Validate input: replace all ‘a’ with ‘@’ and ‘i’ with ‘!’JavaScript
• Why in MySQL, we cannot use arithmetic operators like ‘=’, ‘<’ or ‘<>’ with NULL?
• ‘AND’ vs ‘&&’ operators in PHP
• Rearrange an array such that ‘arr[j]’ becomes ‘i’ if ‘arr[i]’ is ‘j’ in C++
• What is the benefit of MySQL ‘IS NULL’ and ‘IS NOT NULL’?
• Are ‘this’ and ‘super’ keywords in Java?
• How to Use ‘cat’ and ‘tac’ Commands with Examples in Linux
• Replacing ‘public’ with ‘private’ in “main” in Java
• Python program to replace first ‘K’ elements by ‘N’
• How can we create MySQL stored procedures without ‘BEGIN’ and ‘END’?
• Convert all substrings of length ‘k’ from base ‘b’ to decimal in C++
• Construct DFA beginning with ‘a’ but does not have substring ‘aab’
• What is the significance of ‘^’ in PHP?
• Comparing ‘cubic’ and ‘linear’ 1-D interpolation using SciPy library
• MySQL query to search a string ‘demo’ if someone mistakenly types ‘deom’?