(mysql.info.gz) Comparisons using subqueries

Info Catalog

(mysql.info.gz) Scalar subqueries

(mysql.info.gz) Subqueries

(mysql.info.gz) ANY IN SOME subqueries

 
 13.1.8.2 Comparisons Using Subqueries
 .....................................
 
 The most common use of a subquery is in the form:
 
      NON_SUBQUERY_OPERAND COMPARISON_OPERATOR (SUBQUERY)
 
 Where COMPARISON_OPERATOR is one of these operators:
      =  >  <  >=  <=  <>
 
 For example:
      ... 'a' = (SELECT column1 FROM t1)
 
 At one time the only legal place for a subquery was on the right side
 of a comparison, and you might still find some old DBMSs that insist on
 this.
 
 Here is an example of a common-form subquery comparison that you cannot
 do with a join. It finds all the values in table `t1' that are equal to
 a maximum value in table `t2':
 
      SELECT column1 FROM t1
             WHERE column1 = (SELECT MAX(column2) FROM t2);
 
 Here is another example, which again is impossible with a join because
 it involves aggregating for one of the tables. It finds all rows in
 table `t1' containing a value that occurs twice in a given column:
 
      SELECT * FROM t1 AS t
          WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
 
 For a comparison performed with one of these comparison operators, the
 subquery must return a scalar, with the exception that `=' can be used
 with row subqueries.   Row subqueries.
 

Info Catalog

(mysql.info.gz) Scalar subqueries

(mysql.info.gz) Subqueries

(mysql.info.gz) ANY IN SOME subqueries