SQL ANY & ALL Operators

I have started using sql and h开发者_开发技巧ave 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?

---

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.

---

Answers above addressed some aspects of "ANY" and did not address "ALL".

Both of these are more useful when comparing against another table and its entries are changing dynamically.

Especially true for < ANY and > ANY, since for static arguments, you could just take MAX/MIN respectively, and drop the "ANY".

For example, this query -

SELECT ProductName, ProductID FROM Products WHERE ProductID > ANY (100, 200, 300);

can be simplified to -

SELECT ProductName, ProductID FROM Products WHERE ProductID > 100;

Note that the "ALL" query will end up comparing one column value with ALL (...) which will always be false unless "ALL" arguments are identical.

For ex -

SELECT ProductName, ProductID FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails);

which is always empty/ false when subquery is multi-valued like -

SELECT ProductName, ProductID FROM Products WHERE ProductID = ALL (10, 20, 30);

---

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)

---

Sample query that may put some context into this. Let's say we have a database of major league baseball players and we have a database of common Puerto Rican last names. Let's say somebody wanted to see how common Puerto Rican players are on the MLB. They could run the following query:

SELECT mlb_roster.last_name FROM mlb_roster WHERE mlb_roster.last_name = ANY (SELECT common_pr_names.last_name FROM common_pr_names)

What the query is doing here is comparing the last names on the MLB roster and displaying only the ones that are also found on the list of common Puerto Rican names.