(mysql.info) any-in-some-subqueries

(mysql.info) comparisons-using-subqueries

 
 13.2.8.3 Subqueries with `ANY', `IN', and `SOME'
 ................................................
 
 Syntax:
 
      OPERAND COMPARISON_OPERATOR ANY (SUBQUERY)
      OPERAND IN (SUBQUERY)
      OPERAND COMPARISON_OPERATOR SOME (SUBQUERY)
 
 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:
 
      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' if table `t2' contains `(NULL,NULL,NULL)'.
 
 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);
 
 However, `NOT IN' is not an alias for `<> ANY', but for `<> ALL'. See
  all-subqueries.
 
 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's ears, 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.

Info Catalog

(mysql.info) comparisons-using-subqueries

(mysql.info) subqueries

(mysql.info) all-subqueries

automatically generated byinfo2html