MySQL - Any, All Operators

In MySQL, the ANY and ALL operators are used to compare a value with a set of values returned by a subquery. The ANY operator returns true if the value being compared is equal to any value in the set of values returned by the subquery. The ALL operator returns true if the value being compared is equal to all values in the set of values returned by the subquery.

Suppose you have a table named "employees" that contains information about employees, including their ID, name, and salary. If you want to find all employees who earn a salary that is greater than the maximum salary of employees in department 'Sales', you can use the ALL operator in the following way:

SELECT *

FROM employees

WHERE salary > ALL (

  SELECT MAX(salary)

  FROM employees

  WHERE department = 'Sales'

);

In this example, the subquery returns the maximum salary of employees in the 'Sales' department. The ALL operator compares the salary of each employee with the maximum salary returned by the subquery. If the salary of an employee is greater than all the maximum salaries returned by the subquery, that employee's row will be included in the result set.

Another example of using the ANY operator:

Suppose you have a table named "products" that contains information about products, including their ID, name, and price. If you want to find all products that are cheaper than the most expensive product with name starting with 'A', you can use the ANY operator in the following way:

SELECT *

FROM products

WHERE price < ANY (

  SELECT MAX(price)

  FROM products

  WHERE name LIKE 'A%'

);

In this example, the subquery returns the maximum price of products whose name starts with 'A'. The ANY operator compares the price of each product with the maximum price returned by the subquery. If the price of a product is less than any of the maximum prices returned by the subquery, that product's row will be included in the result set.