MySQL : ANY and ALL Operators

In MySQL, the ANY and ALL operators are used in conjunction with subqueries to compare a value with a set of values returned by the subquery.

1.  ANY: The ANY operator returns TRUE if the comparison is true for at least one of the values returned by the subquery.

2.  ALL: The ALL operator returns TRUE if the comparison is true for all values returned by the subquery.

Here's the basic syntax:


SELECT columns
FROM table1
WHERE column1 operator ANY (subquery);

SELECT columns
FROM table1
WHERE column1 operator ALL (subquery);
 

  • columns are the columns you want to retrieve from table1.
  • table1 is the main table you're querying.
  • column1 is the column you want to compare with the results of the subquery.
  • operator is a comparison operator like =, >, <, etc.
  • subquery is a subquery that returns a set of values.

For example, suppose you have a table named products with columns product_id and price, and you want to find products that are more expensive than at least one product with product_id equal to 100:


SELECT *
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE product_id = 100
);
 

This query will return all products with a price higher than at least one product with product_id equal to 100.

Conversely, if you wanted to find products that are more expensive than all products with product_id equal to 100, you could use the ALL operator:


SELECT *
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE product_id = 100
);
 

This query would return products that are more expensive than every product with product_id equal to 100.