MySQL 8.0-13.2.11.3 Subqueries with ANY, IN, or SOME

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparison_operator is one of these operators:

=  >  <  >=  <=  <>  !=

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.” For example:

ANY关键字必须跟在比较操作符后面，意思是“如果子查询返回的列中任何值的比较为TRUE，则返回TRUE。”例如:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).

假设表t1中有一行包含(10)。如果表t2包含(21,14,7)，则表达式为TRUE，因为在t2中有一个值7小于10。如果表t2包含(20,10)，或者表t2为空，则表达式为FALSE。如果表t2包含(NULL,NULL,NULL)，则表达式是未知的(即NULL)。

When used with a subquery, the word IN is an alias for = ANY. Thus, these two statements are the same:

当与子查询一起使用时，单词IN是= ANY的别名。因此，这两种说法是相同的:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

IN and = ANY are not synonyms when used with an expression list. IN can take an expression list, but = ANY cannot. See Section 12.4.2, “Comparison Functions and Operators”.

IN和= ANY在与表达式列表一起使用时不是同义词。IN可以接受一个表达式列表，但是= ANY不能。参见第12.4.2节“比较函数和运算符”。

NOT IN is not an alias for <> ANY, but for <> ALL. See Section 13.2.11.4, “Subqueries with ALL”.

NOT IN不是<> ANY的别名，而是<> ALL的别名。参见13.2.11.4节“带ALL的子查询”。

The word SOME is an alias for ANY. Thus, these two statements are the same:

SOME这个词是ANY的别名。因此，这两种说法是相同的:

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

Use of the word SOME is rare, but this example shows why it might be useful. To most people, the English phrase “a is not equal to any b” means “there is no b which is equal to a,” but that is not what is meant by the SQL syntax. The syntax means “there is some b to which a is not equal.” Using <> SOME instead helps ensure that everyone understands the true meaning of the query.

很少使用单词SOME，但这个示例说明了为什么它可能有用。对大多数人来说，英语短语“a不等于任何b”意味着“没有等于a的b”，但这不是SQL语法的意思。这个语法的意思是“有一个b和a不相等。”相反，使用<> SOME有助于确保每个人都理解查询的真正含义。

Beginning with MySQL 8.0.19, you can use TABLE in a scalar IN, ANY, or SOME subquery provided the table contains only a single column. If t2 has only one column, the statements shown previously in this section can be written as shown here, in each case substituting TABLE t2 for SELECT s1 FROM t2:

从MySQL 8.0.19开始，您可以在标量in、ANY或SOME子查询中使用TABLE，只要表只包含一列。如果t2只有一列，本节中前面所示的语句可以写成如下所示，在每种情况下都用TABLE t2替换SELECT s1 FROM t2:

SELECT s1 FROM t1 WHERE s1 > ANY (TABLE t2);

SELECT s1 FROM t1 WHERE s1 = ANY (TABLE t2);

SELECT s1 FROM t1 WHERE s1 IN (TABLE t2);

SELECT s1 FROM t1 WHERE s1 <> ANY  (TABLE t2);

SELECT s1 FROM t1 WHERE s1 <> SOME (TABLE t2);