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.


Most Read