SQL ANY & ALL Operators

Question

I have started using sql and have heard much about the ANY and ALL operators. Can somebody explain to me the kind of queries they are used in and how they work?

Answer 1

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) 

Answer 2

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.