display | more...

A keyword in Structured Query Language, ANY returns a true value if any value in the subquery matches the value in the current row. In this way, ANY is similar to the IN keyword, except that IN only accepts the = operator and ANY accepts any relational operator. Let's look at an example.

SELECT * FROM
     books WHERE pub_year = ANY
     (SELECT birth_year from employees
      WHERE lname = "smith");

In this example, the query engine starts looking in the books table for books that have a publication year identical the same as the birth year of any person on the employees table whose last name is "smith." Say, there are two Smiths (Joe and Jane, born in 1971 and 1960 respectively). In this case, the query will return a record (or result) set of all books with publication dates of either 1971 or 1960. The same result set would have been returned had you replaced "= ANY" with "IN."

The real value of ANY is shown in the following example.

SELECT * FROM
     books WHERE pub_year < ANY
     (SELECT birth_year from employees
      WHERE lname = "smith");

In this case, the query will return all books with a publication year less than the birth year of any employee named "smith." The subquery grabs the birth years of Joe and Jane, which results in the main query returning a set of all books with publication years earlier than 1971 or 1960. Since 1971 is the 'larger' year, the 1960 value is sort of redundant.

Source:
Understanding SQL by Martin Gruber, ISBN 0895886448