(mysql.info) all-subqueries

Info Catalog

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

(mysql.info) subqueries

(mysql.info) row-subqueries

 
 13.2.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_ (that is, `NULL') if table `t2' contains `(0,NULL,1)'.
 
 Finally, if table `t2' is empty, the result is `TRUE'. So, the
 following statement is `TRUE' when table `t2' is empty:
 
      SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
 
 But this statement is `NULL' when table `t2' is empty:
 
      SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
 
 In addition, the following statement is `NULL' when table `t2' is empty:
 
      SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
 
 In general, _tables containing `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) any-in-some-subqueries

(mysql.info) subqueries

(mysql.info) row-subqueries