MySQL ANY and ALL Operators
Consider the demo table customers and orders with the following data.
Customers Table
| cust_id | name | description | age |
|---|---|---|---|
| 101 | Peter | Engineer | 32 |
| 102 | Joseph | Developer | 30 |
| 103 | John | Leader | 28 |
| 104 | Stephen | Scientist | 45 |
| 105 | Suzi | Carpenter | 26 |
| 106 | Bob | Actor | 25 |
Orders Table
| order_id | cust_id | prod_name | order_date | amount |
|---|---|---|---|---|
| 1 | 101 | Laptop | 2022-01-10 | 45000 |
| 2 | 103 | Desktop | 2022-02-12 | 35000 |
| 3 | 106 | Iphone | 2022-02-15 | 65000 |
| 4 | 104 | Mobile | 2022-03-05 | 12000 |
ANY Operator
The ANY operator compares the values of the first table with the values of the second table and return the matching records with any value.
Syntax for ANY Operator
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
Example
SELECT *
FROM customers
WHERE cust_id = ANY
(SELECT cust_id
FROM orders
WHERE order_date>'2022-02-01');
Output
| cust_id | name | description | age |
|---|---|---|---|
| 103 | John | Leader | 28 |
| 104 | Stephen | Scientist | 45 |
| 106 | Bob | Actor | 25 |
ALL Operator
The ALL operator compares the values of the first table with the values of the second table and return the matching records.
Syntax for ALL Operator
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
Example
SELECT *
FROM customers
WHERE cust_id = ALL
(SELECT cust_id
FROM orders
WHERE order_date>'2022-02-01');
Output
| cust_id | name | description | age |
|---|
This will not return any values.