(mysql.info.gz) ALL subqueries

(mysql.info.gz) ANY IN SOME subqueries

 
 13.1.8.4 Subqueries with `ALL'
 ..............................
 
 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' if table `t2' contains `(0,NULL,1)'.
 
 Finally, if table `t2' is empty, the result is `TRUE'.  You might think
 the result should be `UNKNOWN', but sorry, it's `TRUE'. So, rather
 oddly, the following statement is `TRUE' when table `t2' is empty:
 
      SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
 
 But this statement is `UNKNOWN' when table `t2' is empty:
 
      SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
 
 In addition, the following statement is `UNKNOWN' when table `t2' is
 empty:
 
      SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
 
 In general, _tables with `NULL' values_ and _empty tables_ are _edge
 cases_. When writing subquery code, always consider whether you have
 taken those two possibilities into account.
 
 `NOT IN' is an alias for `<> ALL'. Thus these two statements are the
 same:
 
      SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
      SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

Info Catalog

(mysql.info.gz) ANY IN SOME subqueries

(mysql.info.gz) Subqueries

(mysql.info.gz) Row subqueries

automatically generated byinfo2html