MySQL SQL ANY & ALL 运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5980474/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL ANY & ALL Operators
提问by user559142
I have started using sql and have heard much about the ANYand ALLoperators. Can somebody explain to me the kind of queries they are used in and how they work?
我已经开始使用 sql 并且听说过很多关于ANYandALL运算符的信息。有人可以向我解释它们使用的查询类型以及它们是如何工作的吗？
回答by Adam Robinson
The ANYand ALLoperators allow you to perform a comparison between a single column value and a range of other values. For instance:
的ANY和ALL运营商允许执行单柱值和范围内的其他值之间的比较。例如：
select * from Table1 t1 where t1.Col1 < ANY(select value from Table2)
ANYmeans that the condition will be satisfied if the operation is true for any of the values in the range. ALLmeans that the condition will be satisfied only if the operation is true for allvalues in the range.
ANY意味着如果该操作对范围内的任何值都为真，则条件将得到满足。ALL意味着只有当该操作对范围内的所有值都为真时，才会满足条件。
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)
回答by onedaywhen
I have heard much about the
ANYandALLoperators
我听说过很多关于
ANY和ALL运营商
I'm mildly surprised: I rarely see them used myself. Far more commonly seen are WHERE val IN (subquery)and WHERE EXISTS (subquery).
我有点惊讶：我很少看到自己使用它们。更常见的是WHERE val IN (subquery)和WHERE EXISTS (subquery)。
To borrow @Adam Robinson's example:
借用@Adam Robinson 的例子：
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.Col1and t2.valuerespectively) are closer together.
我发现这个结构更容易阅读，因为谓词（t1.Col1和t2.value）的参数更接近。
回答by Denis de Bernardy
Adding to Adam's reply, be wary that the syntax can be ambiguous:
添加到 Adam 的回复中，请注意语法可能不明确：
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)
这里的 ANY 可以被视为引入子查询，或者被视为聚合函数，如果子查询返回具有布尔值的一行。（通过 postgresql.org）
回答by coder
Answers above addressed some aspects of "ANY" and did not address "ALL".
上面的答案解决了“ANY”的某些方面，并没有解决“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".
对于 < ANY 和 > ANY 尤其如此，因为对于静态参数，您可以分别取 MAX/MIN，并删除“ANY”。
For example, this query -
例如，这个查询 -
SELECT ProductName, ProductID FROM Products WHERE ProductID > ANY (100, 200, 300);
SELECT ProductName, ProductID FROM Products WHERE ProductID > ANY (100, 200, 300);
can be simplified to -
可以简化为——
SELECT ProductName, ProductID FROM Products WHERE ProductID > 100;
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.
请注意，“ALL”查询最终会将一列值与 ALL (...) 进行比较，除非“ALL”参数相同，否则它将始终为 false。
For ex -
对于前 -
SELECT ProductName, ProductID FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails);
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);
SELECT ProductName, ProductID FROM Products WHERE ProductID = ALL (10, 20, 30);
