- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- What is the use of comparison operators with MySQL subquery?
- What is the concept of a derived table concerned with MySQL subquery?
- How can we use a MySQL subquery with INSERT statement?
- How can we use a MySQL subquery with FROM clause?
- Selecting all the users with maximum age values using a MySQL subquery?
- How can we test for the existence of any record in MySQL subquery?
- How MySQL evaluates if we use EXISTS operator with the subquery that returns NULL?
- What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
- What is the use of WITH ROLLUP modifier in MySQL?
- How can I use the arithmetic operators (+,-,*,/) with unit values of INTERVAL keyword in MySQL?
- How can we filter data with the help of MySQL subquery?
- What is Practical Use of Reversed Set Operators in Python?
- How MySQL evaluates if we use EXISTS operator with a subquery that returns no rows?
- How can I use MySQL subquery as a table in FROM clause?
- What is MySQL? Discuss some characteristics of MySQL
