ANY and ALL
Compare a value to every row a subquery returns. ALL means every row; ANY means at least one.
Syntax
WHERE column operator ALL (SELECT ...)WHERE column operator ANY (SELECT ...)
- The operator is a comparison such as >, < or =.
- = ANY is the same as IN. > ALL and > ANY have no IN equivalent.
Example
SELECT name FROM products WHERE unit_price > ALL (SELECT unit_price FROM products WHERE category = 'drinks');
Products that cost more than every drink: the two cakes and the sourdough loaf.
Tables used: products. Try it in a lesson below.
Common mistakes
- ALL of an empty subquery is true for every row, which is rarely what you meant. ANY of an empty subquery is never true.