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
andALL
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.