SQL ANY & ALL Operators

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values. For instance:

select * from Table1 t1 where t1.Col1 < ANY(select value from Table2)

ANY means that the condition will be satisfied if the operation is true for any of the values in the range. ALL means that the condition will be satisfied only if the operation is true for all values in the range.

To use an example that might hit closer to home, doing this:

select * from Table1 t1 where t1.Col1 = ANY(select value from Table2)

Is the same as doing this:

select * from Table1 t1 where t1.Col1 in (select value from Table2)

I have heard much about the ANY and ALL operators

I'm mildly surprised: I rarely see them used myself. Far more commonly seen are WHERE val IN (subquery) and WHERE EXISTS (subquery).

To borrow @Adam Robinson's example:

SELECT *   FROM Table1 AS t1  WHERE t1.Col1 < ANY (                      SELECT value                         FROM Table2                     );

I more usually see this written like this:

SELECT *   FROM Table1 AS t1  WHERE EXISTS (               SELECT *                 FROM Table2 AS t2                WHERE t1.Col1 < t2.value              );

I find this construct easier to read because the parameters of the predicate (t1.Col1 and t2.value respectively) are closer together.

Adding to Adam's reply, be wary that the syntax can be ambiguous:

SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;

Here ANY can be considered either as introducing a subquery, or as being an aggregate function, if the subquery returns one row with a Boolean value. (via postgresql.org)