Course contentsShow
← Computer Science
Lesson 1607 of 2,87236. Database FundamentalsFree lesson

ANY, ALL, and SOME Operators

Using quantified comparison operators to compare a value against all or any results from a subquery.

ANY, ALL, and SOME Operators

You'll learn: How to use quantified comparison operators to check if a value matches any, all, or some rows returned by a subquery.

What Are Quantified Comparisons?

When you compare a single value against a subquery that returns multiple rows, you need special operators to define the comparison logic. That's where ANY, ALL, and SOME come in—they're quantifiers that tell the database whether your condition must be true for all rows, any row, or some row (same as ANY).

Think of it like asking friends for restaurant recommendations. With ANY, you're happy if at least one person suggests a place under 20.With‘ALL‘,youwant∗everysingleperson∗torecommendaplaceunder20. With `ALL`, you want *every single person* to recommend a place under 20.

The Three Operators

ANY (and SOME): The condition is true if it's true for at least one value in the subquery result.

SELECT name FROM products
WHERE price < ANY (SELECT price FROM premium_products);

This finds products cheaper than at least one premium product.

ALL: The condition must be true for every value in the subquery result.

SELECT name FROM products
WHERE price < ALL (SELECT price FROM premium_products);

This finds products cheaper than all premium products (i.e., cheaper than the cheapest premium product).

Comparison Operators Work Too

You can combine these with =, <, >, <=, >=, or <>:

WHERE salary > ALL (SELECT salary FROM interns)
WHERE rating >= ANY (SELECT min_rating FROM requirements)

Key Takeaway: Use ANY/SOME when your condition needs to match at least one subquery result, and ALL when it must match every result—they let you quantify comparisons against multi-row subqueries.