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

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

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

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

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. 

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 : 

## 13.2.11.4 Subqueries with ALL 

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); 

Syntax: 

operand comparison_operator  ALL ( subquery ) 

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

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

Suppose that there is a row in table t1  containing (10) . The expression is TRUE  if table t2  contains (-5,0,+5)  because 10  is greater than all three values in t2 . The expression is FALSE  if table t2 contains (12,6,NULL,-100)  because there is a single value 12  in table t2  that is greater than 10 . The expression is unknown  (that is, NULL ) if table t2  contains (0,NULL,1) . 

Finally, the expression is TRUE  if table t2  is empty. So, the following expression is TRUE  when table t2  is empty: 

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2); 

But this expression is NULL  when table t2  is empty: 

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2); 

